Teradata Utilities – FASTLOAD part 3

A sample Fastload script

Below is the sample Fastload script which is designed to work in WINDOWS OS. To run the Fastload in your system you need to install Fastload utility and then go to command prompt and type –

>fastload < c:\fload_1.txt

where c:\fload_1.txt is the name and path of the Fastload which you want to execute.

A sample Fastload script

/***** Section 1 *****/

/* In this section we give the LOGIN credentials which is required to connect to TD system. Sessions command is used to restrict the number of sessions Fastload will make to connect to TD. Default is one session per AMP. */

.SESSIONS 4;

.LOGON 127.0.0.1/tduser,tduser;

/***** Section 2 *****/

/* In this section we are defining the table which we want to load from Fastload. DROP commands are optional. There is no need to define the structure of ERROR tables they’ll be created itself by Fastload. */

drop table retail.emp_test;
drop table retail.emp_test_er1;
drop table retail.emp_test_er2;

create table retail.emp_test
(
emp_id integer not null,
emp_name varchar(50),
dept_id integer,
salary integer,
dob date format’yyyy-mm-dd’

)
unique primary index(emp_id);
/***** Section 3 *****/


/* In this section we give the BEGIN loading statement. As soon as Fastload receives this statement it starts PHASE 1. */

BEGIN LOADING
retail.emp_test
ERRORFILES
retail.emp_test_er1, retail.emp_test_er2;
/***** Section 4 *****/

/*RECORD command is used to skip the starting rows from the data file. RECORD THRU command is used to skip the last rows of data file. SET RECORD command is used to define the records layout and the “,” is the delimiter which we are using in our data file to separate columns. */ 

.RECORD 1;

.RECORD THRU 3;

SET RECORD VARTEXT “,”;
/***** Section 5 *****/

/* DEFINE statement is used to define the structure of the data file. This should be in accordance with the actual target table structure. Fastload DEFINE statement allows only VARCHAR format. */

DEFINE
emp_id        (VARCHAR(9))
emp_name (VARCHAR(50))
dept_id        (VARCHAR(9))
salary           (VARCHAR(9))
dob               (VARHAR(50))

/***** Section 6 *****/

/* FILE command defines the data file path and name. */

FILE = C:\fload_data.txt;

/***** Section 7 *****/

/* INSERT command is used to load the data file into actual target table. NOTE – For DATE columns we can use the data conversion by the syntax given below. */

INSERT INTO retail.emp_test

(
:emp_id ,
:emp_name,
:dept_id ,
:salary,
:dob (format ‘yyyy-mm-dd’)

);

/***** Section 8 *****/

/* END LOADING ends PHASE 1 and starts the execution of PHASE 2. LOGOFF is required to close all the sessions created by Fastload. */

END LOADING;

.LOGOFF;


This is the simplest Fastload script which can be used to understand the concepts of Fastload.  Besides the commands given here there are also some other commands as well, which you can see in next post.

Based on the requirements we can always tweak the Fastload.

To know more about Fastload check below mentioned links –

Fastload Part 1

Fastload Part 2

Fastload Part 4