All Forums Tools
Kusuma@DB 5 posts Joined 11/09
09 Mar 2012
Fastload error :when trying to load data using fastload from BTEQ exported data file

I am trying to load AREA table using fastload (data file is exported using BTEQ and mode DATA).But I am getting warning message saying EOF on INPUT stream.

below are warning message and scripts:

*** 13:22:41 Number of recs/msg: 338
*** 13:22:41 Starting to send to RDBMS with record 1
*** 13:22:41 Sending row 5
*** 13:22:41 Finished sending rows to the RDBMS

*** 13:22:41 Warning: EOF on INPUT stream.

Fast load script:

logon 127.0.0.1/sample,pwd;

set record formatted;
define
 N_NATIONKEY (integer)
 ,N_NAME    (char(25))
 ,N_REGIONKEY (integer)
 ,N_COMMENT  (varchar(152))

file=bteq_outfile_data.txt;

begin loading sample.area
errorfiles sample.err1,sample.err2;

insert into sample.area values(:N_NATIONKEY,:N_NAME,:N_REGIONKEY,:N_COMMENT);

end loading

logoff

and 

BTEQ Script:

.logon 127.0.0.1/sample,sample;

.export data file=bteq_outfile_data.txt

select * from sample.area1 ;

.export reset

.logoff
 

Can any one have any idea or some info related to it....please share with me....thanks

Stefans 38 posts Joined 02/12
09 Mar 2012

Can you post your input file.......

Stalin

ulrich 816 posts Joined 09/09
09 Mar 2012

This was a warining - not an error.

So had all rows been loaded correctly into the table?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Kusuma@DB 5 posts Joined 11/09
09 Mar 2012

@Stefans

Here is my input file:

+    kusuma                      comment1
+                                comment3
+    ramana                      comment5
+    suma                        comment2
+    suji                        comment4
 

 

@ulrich

I tried to query the table but though it's a warning, it's not allowing me to do that.

It's saying table being loaded.

 

Stefans 38 posts Joined 02/12
09 Mar 2012

What is the highest return code encountered?

Stalin

Kusuma@DB 5 posts Joined 11/09
09 Mar 2012

@Stefans

I got return code:4

ulrich 816 posts Joined 09/09
09 Mar 2012

can you share the whole log of the fastload run?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Stefans 38 posts Joined 02/12
12 Mar 2012

It would be good if you can share the whole log of the run and also the DDL of the table..

I could see a blank for N_NAME in the second row of the i/p file - Try removing this record and run it..

Also there are too many symbols in the i/p file that you had provided...If its a copy paste error,can you share the i/p file again..

Stalin

Kusuma@DB 5 posts Joined 11/09
12 Mar 2012

 

Thanks for your replies but still I am not able to laod data.

Hi,

@ulrich

Below is the complete log of my Fastload run

C:\Documents and Settings\kusuma\Desktop\Practice\Fastload>fastload < Fa
stload_from_bteq_export.txt
     ===================================================================
     =                                                                 =
     =          FASTLOAD UTILITY     VERSION 13.00.00.004              =
     =          PLATFORM WIN32                                         =
     =                                                                 =
     ===================================================================

     ===================================================================
     =                                                                 =
     =          Copyright 1984-2009, Teradata Corporation.             =
     =          ALL RIGHTS RESERVED.                                   =
     =                                                                 =
     ===================================================================

**** 15:37:42 Processing starting at: Mon Mar 12 15:37:42 2012

     ===================================================================
     =                                                                 =
     =          Logon/Connection                                       =
     =                                                                 =
     ===================================================================

0001 logon 127.0.0.1/kusumanew,

**** 15:37:42 Teradata Database Release: 13.00.00.12
**** 15:37:42 Teradata Database Version: 13.00.00.12
**** 15:37:42 Current CLI or RDBMS allows maximum row size: 64K
**** 15:37:42 Character set for this job: ASCII
**** 15:37:43 Number of FastLoad sessions connected = 2
**** 15:37:43 FDL4808 LOGON successful

0002 set record formatted;

**** 15:37:43 Now set to read 'FORMATTED' records
**** 15:37:43 Command completed successfully

0003 define
      N_NATIONKEY (integer)
      ,N_NAME   (char(25))
      ,N_REGIONKEY (integer)
      ,N_COMMENT  (varchar(152))

     file=C:\Documents and Settings\kusuma\Desktop\Practice\BTEQ\bteq_o
     utfile_data.txt;

**** 15:37:43 FDL4803 DEFINE statement processed

0004 begin loading kusumanew.area
     errorfiles kusumanew.err1,kusumanew.err2;

**** 15:37:43 FastLoad is continuing a multifile job
**** 15:37:43 Number of AMPs available: 2
**** 15:37:43 BEGIN LOADING COMPLETE

     ===================================================================
     =                                                                 =
     =          Insert Phase                                           =
     =                                                                 =
     ===================================================================

0005 insert into kusumanew.area values(:N_NATIONKEY,:N_NAME,:N_REGIONKEY,:N_COMM

     ENT);

**** 15:37:43 Number of recs/msg: 338
**** 15:37:43 Starting to send to RDBMS with record 1
**** 15:37:43 Sending row 5
**** 15:37:43 Finished sending rows to the RDBMS

**** 15:37:43 Warning: EOF on INPUT stream.
     ===================================================================
     =                                                                 =
     =          Logoff/Disconnect                                      =
     =                                                                 =
     ===================================================================

**** 15:37:43 Logging off all sessions
**** 15:37:44 Total processor time used = '0.328125 Seconds'
     .        Start : Mon Mar 12 15:37:42 2012
     .        End   : Mon Mar 12 15:37:44 2012
     .        Highest return code encountered = '4'.
**** 15:37:44 FastLoad Paused

 

and DDL of AREA Table:

CREATE SET  TABLE kusumanew.AREA ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      N_NATIONKEY INTEGER NOT NULL,
      N_NAME CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      N_REGIONKEY INTEGER NOT NULL,
      N_COMMENT VARCHAR(152) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX ( N_NAME );

@Stefans

I have removed that row which have null for one column but no use and BTEQ exported data file had some spaces ,I removed and tried to load I received the following o/p

 insert into kusumanew.area values(:N_NATIONKEY,:N_NAME,:N_REGIONKEY,:N_COMM

 ENT);

 15:48:59 Number of recs/msg: 338
 15:48:59 Starting to send to RDBMS with record 1
 15:48:59 I/O Error on File Read: 16, Text: Unexpected data format
 ===================================================================
 =                                                                 =
 =          Logoff/Disconnect                                      =
 =                                                                 =
 ===================================================================

 15:48:59 Logging off all sessions
 15:49:00 Total processor time used = '0.34375 Seconds'
 .        Start : Mon Mar 12 15:48:58 2012
 .        End   : Mon Mar 12 15:49:00 2012
 .        Highest return code encountered = '12'.
 15:49:00 FastLoad Paused

Did you anytime try to load data using fastload which is exported using BTEQ DATA mode?

Am I doing anything wrong here?

 

 

 

ulrich 816 posts Joined 09/09
12 Mar 2012

you missed two ; at the end of the script...

it should be

 

end loading;

logoff;

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Kusuma@DB 5 posts Joined 11/09
12 Mar 2012

Yah , Now it's working fine.

Thanks for  your time.....

super25 19 posts Joined 07/11
15 Jan 2013

Is there a way to diasble the fall back option for fastload error tables? It seems like by default error tables will have fallback option enbaled in 13.10.

You must sign in to leave a comment.