fastload errors with define delimiters.

Tools
Enthusiast

fastload errors with define delimiters.

clear;

sessions 10;

tenacity 4;

sleep 20;

DATEFORM ANSIDATE;\

show version;

.LOGON 127.0.0.1/TDUSER, TDUSER

DROP TABLE tduser.M1;

 DROP TABLE TDUSER.ERR1;

 DROP TABLE TDUSER.ERR2;

CREATE SET TABLE tduser.M1 

 (

  MANUFACTURER_ID INTEGER,

  MANUFACTURER_NAME VARCHAR(10),  

  MANFACTURE_CODE VARCHAR(3)

)

UNIQUE PRIMARY INDEX ( MANUFACTURER_ID );

SET RECORD VARTEXT "|";

DEFINE

 delim0 (varchar(1)),

 MANUFACTURER_ID (varchar(2)),

 delim1 (varchar(1)),

 MANUFACTURER_NAME (varchar(10)),

 delim2 (varchar(1)),

 MANFACTURE_CODE (varchar(3)),

 delim3 (varchar(1)),

 newlinechar (varchar(1))

FILE = C:\Documents and Settings\code.txt;

SHOW;

BEGIN LOADING TDUSER.M1 ERRORFILES TDUSER.ERR1, TDUSER.ERR2;

.CHECKPOINT 1000;

.INDICATORS;

INSERT INTO TDUSER.M1 VALUES (:MANUFACTURER_ID,:MANUFACTURER_NAME,:MANFACTURE_CODE) ;

END LOADING;

logoff;

LOG+++++++++++++++++++++++++++

     ===================================================================

     =                                                                 =

     =          FASTLOAD UTILITY     VERSION 13.00.00.004              =

     =          PLATFORM WIN32                                         =

     =                                                                 =

     ===================================================================

     ===================================================================

     =                                                                 =

     =          Copyright 1984-2009, Teradata Corporation.             =

     =          ALL RIGHTS RESERVED.                                   =

     =                                                                 =

     ===================================================================

**** 00:51:07 Processing starting at: Sun Jun 16 00:51:07 2013

0001 clear;

**** 00:51:07 Warning: All previous column and file definitions cleared

0002 sessions 10;

**** 00:51:07 FDL4866 SESSIONS command accepted

0003 tenacity 4;

**** 00:51:07 Tenacity Enabled:  4 hour(s)

0004 sleep 20;

**** 00:51:07 Sleep Minutes Set: 20 minute(s)

0005 DATEFORM ANSIDATE;\

**** 00:51:07 Date/Time Format set to ANSIDATE

**** 00:51:07 Command completed successfully

0006 show version;

     FastLoad Version 13.00.00.004 for Win 32 running Windows Sockets

     FastLoad  : 13.00.00.08

     FastCmds  : 13.00.00.11

     FastIO    : 13.00.00.02

     FastMBCS  : 13.00.00.02

     FastNtfy  : 13.00.00.00

     FastPars  : 13.00.00.06

     FastSQL   : 13.00.00.16

     FastUtil  : 13.00.00.04

     Standalone Data Connector  : 13.00.00.001

     PMPROCS   : 13.00.00.01

     PMRWFMT   : 13.00.00.03

     PMTRCE    : 13.00.00.01

     PMMM      : 13.00.00.01

     PMHEXDMP  : 13.00.00.01

     PMUNXDSK  : 13.00.00.01

     ICUVER    : TDICU, 13.00.00.01

     CLIV2     : 13.00.00.25

     MTDP      : 13.00.00.15

     MOSIos    : 13.00.00.05

     MOSIDEP   : 13.00.00.02

     OSENCRYPT : N/A

     OSERR     : 13.00.00.00

     FastLoad linking date: Mar 16 2009

     ===================================================================

     =                                                                 =

     =          Logon/Connection                                       =

     =                                                                 =

     ===================================================================

0007 .LOGON 127.0.0.1/TDUSER,

**** 00:51:07 Teradata Database Release: 13.00.00.12

**** 00:51:07 Teradata Database Version: 13.00.00.12

**** 00:51:07 Current CLI or RDBMS allows maximum row size: 64K

**** 00:51:07 Character set for this job: ASCII

**** 00:51:08 Number of FastLoad sessions requested = 10

**** 00:51:08 Number of FastLoad sessions connected = 2

**** 00:51:08 FDL4808 LOGON successful

0008 DROP TABLE tduser.M1;

**** 00:51:08 Command completed successfully

0009  DROP TABLE TDUSER.ERR1;

**** 00:51:08 RDBMS error 3807: Object 'TDUSER.ERR1' does not exist.

0010  DROP TABLE TDUSER.ERR2;

**** 00:51:08 RDBMS error 3807: Object 'TDUSER.ERR2' does not exist.

0011 CREATE SET TABLE tduser.M1

      (

       MANUFACTURER_ID INTEGER,

       MANUFACTURER_NAME VARCHAR(10),

       MANFACTURE_CODE VARCHAR(3)

     )

     UNIQUE PRIMARY INDEX ( MANUFACTURER_ID );

**** 00:51:08 Command completed successfully

0012 SET RECORD VARTEXT "|";

**** 00:51:08 Now set to read 'Variable-Length Text' records

**** 00:51:08 Delimiter character(s) is set to '|'

**** 00:51:08 Command completed successfully

0013 DEFINE

      delim0 (varchar(1)),

      MANUFACTURER_ID (varchar(2)),

      delim1 (varchar(1)),

      MANUFACTURER_NAME (varchar(10)),

      delim2 (varchar(1)),

      MANFACTURE_CODE (varchar(3)),

      delim3 (varchar(1)),

      newlinechar (varchar(1))

     FILE = C:\Documents and Settings\code.txt;

**** 00:51:08 FDL4803 DEFINE statement processed

0014 SHOW;

     FILE = C:\Documents and Settings\code.txt

     DELIM0                           OFFSET =      0 LEN =     1 VARCHAR

     MANUFACTURER_ID                  OFFSET =      3 LEN =     2 VARCHAR

     DELIM1                           OFFSET =      7 LEN =     1 VARCHAR

     MANUFACTURER_NAME                OFFSET =     10 LEN =    10 VARCHAR

     DELIM2                           OFFSET =     22 LEN =     1 VARCHAR

     MANFACTURE_CODE                  OFFSET =     25 LEN =     3 VARCHAR

     DELIM3                           OFFSET =     30 LEN =     1 VARCHAR

     NEWLINECHAR                      OFFSET =     33 LEN =     1 VARCHAR

     TOTAL RECORD LENGTH = 36

0015 BEGIN LOADING TDUSER.M1 ERRORFILES TDUSER.ERR1, TDUSER.ERR2;

**** 00:51:08 Number of AMPs available: 2

**** 00:51:08 BEGIN LOADING COMPLETE

0016 .CHECKPOINT 1000;

**** 00:51:08 FDL4800 Invalid FastLoad statement

0017 .INDICATORS;

**** 00:51:08 FDL4800 Invalid FastLoad statement

     ===================================================================

     =                                                                 =

     =          Insert Phase                                           =

     =                                                                 =

     ===================================================================

0018 INSERT INTO TDUSER.M1 VALUES (:MANUFACTURER_ID,:MANUFACTURER_NAME,:MANFACTU

     RE_CODE) ;

**** 00:51:08 Number of recs/msg: 2570

**** 00:51:08 Starting to send to RDBMS with record 1

**** 00:51:08 Field too large in vartext data record: 1, field:

              MANUFACTURER_ID

     ===================================================================

     =                                                                 =

     =          Logoff/Disconnect                                      =

     =                                                                 =

     ===================================================================

**** 00:51:08 Logging off all sessions

**** 00:51:11 Total processor time used = '0.296875 Seconds'

     .        Start : Sun Jun 16 00:51:07 2013

     .        End   : Sun Jun 16 00:51:11 2013

     .        Highest return code encountered = '12'.

**** 00:51:11 FastLoad Paused

DATA file++++++++++

1|mayank|ab

22|deepak|xy

10|father|jj

also advise please when to use default option of unformatted and formatted if using normal text file in personal computer.

what is format when we use xcel to keep data for loading.

regards,

Mayank


Tags (1)
3 REPLIES
Teradata Employee

Re: fastload errors with define delimiters.

Don't include the delimiter characters or newline in the DEFINE.

UNFORMATTED is used for "fixed format" records with positional fields and binary data.

FORMATTED (also known as FastLoad format) is used mainly when the input was created by a Teradata export utility.

Enthusiast

Re: fastload errors with define delimiters.

Hi ,

Just use below define statement as the data file doesn't have any starting and trailing delimiters and you just need to press enter while creating the sample data i.e your cursor should be in next line.

 DEFINE

      MANUFACTURER_ID (varchar(2)),

      MANUFACTURER_NAME (varchar(10)),

      MANFACTURE_CODE (varchar(3)),

your sample data:

1|mayank|ab

22|deepak|xy

10|father|jj

Try with the above define and sample data, It should work fine.BTW,  Add checkpoint and other commands in begin loading command (as in our earlier reply)

Teradata Employee

Re: fastload errors with define delimiters.

Hi,

You use:

SET RECORD VARTEXT "|";

Your DEFINE command looks like you want to use UNFORMATTED :-(.

If you DEFINE command as kusumayella provided, it should work!

Please read the FastLoad manual for record format usage. CHECKPOINT 1000 and INDICATORS are part of the BEGIN LOADING command. Please refer to the manual for the BEGIN LOADING syntax. The manual also contains examples that can be useful for you.

I see you starts several threads for the same issue!

If you don't have the FastLoad manual handy to write FastLoad script, let me know, I will provide you the link.

Good luck!

Thomas