Fastload Problem: Invalid Fastload Statement

Tools & Utilities
Enthusiast

Fastload Problem: Invalid Fastload Statement

Hi Guys,

Need your help to figure out where the problem is.

Thanks in advance!

**** 18:33:38 Teradata Database Release: 14.10.02.08

**** 18:33:38 Teradata Database Version: 14.10.02.08

**** 18:33:38 Number of AMPs available: 180

**** 18:33:38 Current CLI or RDBMS allows maximum row size: 64K

**** 18:33:38 Character set for this job: ASCII

0003 DROP TABLE dl_xaas_new.DW_CSM_AGENTASSIGNMENT_ACCOUNT;

**** 18:33:42 Command completed successfully

0004     CREATE TABLE dl_xaas_new.DW_CSM_AGENTASSIGNMENT_ACCOUNT

         (

         AGENTASSIGNMENTID      DECIMAL(16,0),

         ASSIGNMENTTYPEID       DECIMAL(16,0),

         AGENTID        DECIMAL(16,0),

         ENTITYID       DECIMAL(16,0),

         KEYID  DECIMAL(16,0),

         ASSIGNMENTVALUE1       varchar(64),

         ASSIGNMENTVALUE2       varchar(64),

         ASSIGNMENTVALUE3       varchar(64),

         TEAMNAME       varchar(64),

         REPORTTONAME   varchar(64),

         AGENTORGANIZATION      varchar(64),

         STATUS varchar(64),

         CREATEDBY      DECIMAL(16,0),

         CREATIONDATE   TIMESTAMP(6),

         LASTMODIFIEDBY DECIMAL(16,0),

         LASTMODIFIEDDATE       TIMESTAMP(6)

         )

         UNIQUE PRIMARY INDEX (AGENTASSIGNMENTID);

**** 18:33:43 Command completed successfully

0005 .RECORD 2;

**** 18:33:43 Starting record number set to  : 2

0006 .set record VARTEXT "      ";

**** 18:33:43 Now set to read 'Variable-Length Text' records

**** 18:33:43 Delimiter character(s) is set to '        '

**** 18:33:43 Command completed successfully

0007 begin loading dl_xaas_new.DW_CSM_AGENTASSIGNMENT_ACCOUNT ERRORFILES dl_xaas

     _new.ASIGN_ERR1, dl_xaas_new.ASIGN_ERR2;

**** 18:33:43 Session count 29 returned by the DBS overrides

              user-requested session count

**** 18:34:23 Number of FastLoad sessions requested = 29

**** 18:34:23 Number of FastLoad sessions connected = 29

**** 18:34:23 FDL4808 LOGON successful

**** 18:34:24 Number of AMPs available: 180

**** 18:34:24 BEGIN LOADING COMPLETE

0008 define

        AGENTASSIGNMENTID       (varchar(64))   ,

         ASSIGNMENTTYPEID       (varchar(64))   ,

         AGENTID        (varchar(64))   ,

         ENTITYID       (varchar(64))   ,

         KEYID  (varchar(64))   ,

         ASSIGNMENTVALUE1       (varchar(64))   ,

         ASSIGNMENTVALUE2       (varchar(64))   ,

         ASSIGNMENTVALUE3       (varchar(64))   ,

         TEAMNAME       (varchar(64))   ,

         REPORTTONAME   (varchar(64))   ,

         AGENTORGANIZATION      (varchar(64))   ,

         STATUS (varchar(64))   ,

         CREATEDBY      (varchar(64))   ,

         CREATIONDATE   (varchar(64))   ,

         LASTMODIFIEDBY (varchar(64))   ,

         LASTMODIFIEDDATE       (varchar(64))

     file=C:\Users\auxu\Desktop\assign.txt;

**** 18:34:24 FDL4803 DEFINE statement processed

0009 show;

     FILE = C:\Users\auxu\Desktop\assign.txt

     AGENTASSIGNMENTID                OFFSET =      0 LEN =    64 VARCHAR

     ASSIGNMENTTYPEID                 OFFSET =     66 LEN =    64 VARCHAR

     AGENTID                          OFFSET =    132 LEN =    64 VARCHAR

     ENTITYID                         OFFSET =    198 LEN =    64 VARCHAR

     KEYID                            OFFSET =    264 LEN =    64 VARCHAR

     ASSIGNMENTVALUE1                 OFFSET =    330 LEN =    64 VARCHAR

     ASSIGNMENTVALUE2                 OFFSET =    396 LEN =    64 VARCHAR

     ASSIGNMENTVALUE3                 OFFSET =    462 LEN =    64 VARCHAR

     TEAMNAME                         OFFSET =    528 LEN =    64 VARCHAR

     REPORTTONAME                     OFFSET =    594 LEN =    64 VARCHAR

     AGENTORGANIZATION                OFFSET =    660 LEN =    64 VARCHAR

     STATUS                           OFFSET =    726 LEN =    64 VARCHAR

     CREATEDBY                        OFFSET =    792 LEN =    64 VARCHAR

     CREATIONDATE                     OFFSET =    858 LEN =    64 VARCHAR

     LASTMODIFIEDBY                   OFFSET =    924 LEN =    64 VARCHAR

     LASTMODIFIEDDATE                 OFFSET =    990 LEN =    64 VARCHAR

     TOTAL RECORD LENGTH = 1056

0010 checkpoint 1000;

**** 18:34:24 FDL4800 Invalid FastLoad statement

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

     =                                                                 =

     =          Insert Phase                                           =

     =                                                                 =

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

0011 insert into dl_xaas_new.DW_CSM_AGENTASSIGNMENT_ACCOUNT (

         :AGENTASSIGNMENTID,

         :ASSIGNMENTTYPEID,

         :AGENTID,

         :ENTITYID,

         :KEYID,

         :ASSIGNMENTVALUE1,

         :ASSIGNMENTVALUE2,

         :ASSIGNMENTVALUE3,

         :TEAMNAME,

         :REPORTTONAME,

         :AGENTORGANIZATION,

         :STATUS,

         :CREATEDBY,

         :CREATIONDATE,

         :LASTMODIFIEDBY,

         :LASTMODIFIEDDATE

         );

**** 18:34:26 Number of recs/msg: 62

**** 18:34:26 Starting to send to RDBMS with record 2

**** 18:34:32 Starting row 100000

**** 18:34:38 Starting row 200000

**** 18:34:58 Starting row 300000

**** 18:35:10 Sending row 380728

**** 18:35:13 Finished sending rows to the RDBMS

**** 18:35:13 Acquisition Phase statistics:

              Elapsed time: 00:00:47 (in hh:mm:ss)

              CPU time:     1.76281 Seconds

              MB/sec:       0.79

              MB/cpusec:    20.98

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

     =                                                                 =

     =          End Loading Phase                                      =

     =                                                                 =

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

0012 end loading;

**** 18:35:18 END LOADING COMPLETE

     Total Records Read              =  380728

      - skipped by RECORD command    =  1

      - sent to the RDBMS            =  380727

     Total Error Table 1             =  380727

     Total Error Table 2             =  0  ---- Table has been dropped

     Total Inserts Applied           =  0

     Total Duplicate Rows            =  0

     Start:   Tue Jul 15 18:35:14 2014

     End  :   Tue Jul 15 18:35:18 2014

**** 18:35:18 Application Phase statistics:

              Elapsed time: 00:00:04 (in hh:mm:ss)

0013 .LOGOFF;

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

     =                                                                 =

     =          Logoff/Disconnect                                      =

     =                                                                 =

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

**** 18:35:21 Logging off all sessions

**** 18:35:29 Total processor time used = '3.27602 Seconds'

     .        Start : Tue Jul 15 18:33:29 2014

     .        End   : Tue Jul 15 18:35:29 2014

     .        Highest return code encountered = '8'.

**** 18:35:29 FDL4818 FastLoad Terminated

6 REPLIES
Enthusiast

Re: Fastload Problem: Invalid Fastload Statement

Can you check for what is the error code in the ET table(asign_err1) that was created.

Teradata Employee

Re: Fastload Problem: Invalid Fastload Statement

This error is not related to an error tables.

The problem is with your "checkpoint 1000" statement.

That is not valid.

You specify the checkpoint interval on the BEGIN LOADING command.

Please refer to the FastLoad reference manual for the correct syntax.

-- SteveF
Enthusiast

Re: Fastload Problem: Invalid Fastload Statement

Thanks for all the response.

I am stuck in loading a timestamp column. Following is my script, can you pls help?

sessions 100;

.LOGON TDPROD2/auxu,Ninth89xu;

DROP TABLE dl_xaas_new.tmtest;

    CREATE TABLE dl_xaas_new.tmtest

    (dtime timestamp(6));

.RECORD 2;

.set record VARTEXT " ";

begin loading dl_xaas_new.tmtest ERRORFILES dl_xaas_new.dtime_ERR1, dl_xaas_new.dtime_ERR2;

define

dtime (varchar(100))

file=C:\Users\auxu\Desktop\d.txt;

show;

checkpoint 50000;

insert into dl_xaas_new.DW_CSM_AGENTASSIGNMENT_ACCOUNT (

    cast(:dtime as TIMESTAMP(0) format 'yyyy-mm-ddbhh:mi:ss')

    );

end loading;

.LOGOFF;

Teradata Employee

Re: Fastload Problem: Invalid Fastload Statement

You say you are stuck, but do not give the problem.

I do see 2 things:

1. you still have not removed the checkpoint command; that is not valid

2. I do not believe you can provide CAST or FORMAT specifications in an INSERT statement for the FastLoad protocol (the DBS places a lot of restrictions on the syntax of the INSERT statement for the FastLoad protocol).

If you define the column properly in the target table (with the appropriate FORMAT string), I would think you can provide the data as either CHAR or VARCHAR and the DBS will convert.

-- SteveF
Junior Contributor

Re: Fastload Problem: Invalid Fastload Statement

You can use a typecast in FastLoad, but it must be Teradata style, not CAST :-)

(:dtime (TIMESTAMP(0), format 'yyyy-mm-ddbhh:mi:ss'))

Enthusiast

Re: Fastload Problem: Invalid Fastload Statement

Thanks everyone, especially feinholz.