Teradata Fastload – Part 2

A Sample Fastload script

/*Section 1*/
.SESSIONS 50;

/*Section 2*/
.LOGON IP_Adress/username,password;

/*Section 3*/
drop table  TERADATAPOINT.STUDENT;
drop table  TERADATAPOINT.STUDENT_E1;
drop table  TERADATAPOINT.STUDENT_E2;

CREATE MULTISET TABLE TERADATAPOINT.STUDENT ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      Roll_No CHAR(10)
      ,First_Name VARCHAR(20)
      ,Last_Name VARCHAR(20)
      ,DOB DATE format 'YYYY-MM-DD'
     )
PRIMARY INDEX (Roll_No);

/*Section 4*/
.Record 2;
.SET RECORD VARTEXT '|'; 

/*Section 5*/
define
 Roll_No (VARCHAR(100))
 ,First_Name (VARCHAR(100))
 ,Last_Name (VARCHAR(100))
 ,DOB VARCHAR(100)

 /*Section 6*/
file=C:\Student_data\Student_info;
 
/*Section 7*/
begin loading TERADATAPOINT.STUDENT
errorfiles TERADATAPOINT.STUDENT_E1,TERADATAPOINT.STUDENT_E2;

/*Section 8*/
Insert into TERADATAPOINT.STUDENT
 values
 (
:Roll_No
,:First_Name
,:Last_Name
,:DOB (format 'YYYY-MM-DD')
);
 
 /*Section 9*/
 end loading;
 .logoff;
 .quit;

Section 1-

In section 1, we can specify the numbers of sessions Fastload will make to connect to Teradata. By default, one session per AMP.

Section 2-

In this section, we need to provide logon information like tdpid or ip_address of Teradata system, username, password.

Section 3-

We can mention Teradata target table name here. Optionally we can drop statement for the target tables and two error tables. This will ensure if these tables are already exist, Fastload will drop those.

Section 4-

 Record command is used to skip the starting rows from the data file. Suppose flat file contains header information at the first line and obviously you don’t want to load the header info to the Teradata table. In this case you can specify using Record command.

Section 5-

Define command is use to define the structure of the data file. This should accordance to your target table. Fastload define statement only allows VARCHAR format. That means irrespective of the data type in target column, you need to specify the data type as VARCHAR only.

Section 6-

Define the name of the source file with the fully specified path. Data will be loaded to Teradata target table from this file.

Section 7-

In this section we give the BEGIN LOADDING command. As soon as Teradata receives this statement, it start Phase 1.

Section 8-

Insert statement will be specified here. Teradata will starts to send the data block to the AMP. You can specify the conversion of DATE format here as per your requirement.  Suppose from source file DATE is coming as ‘DD-MM-YYYY’ but you want date in target table  as ‘YYYY-MM-DD’. This date conversion can be specified here.

Section 9 –

End loading finishes the phase 1 i.e. data insert into target table and initiates the phase 2. And lastly logoff is required to close all the sessions created by Fastload.