«

»

Oct 13

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

 

6 comments

1 ping

Skip to comment form

  1. Manikanta

    Hi Admin,

    Thank you very much Fastload four parts are awesome
    I have follow only those four parts my total doubts on fastload has been clarified.
    Once again thank you for your lovely posts.keep it up admin.

  2. satish

    Hi ,

    Can any one send sample script to load multiple file to load single empty table using fast load.
    please test the script first and send reply.

  3. priyabrat

    Hi Admin,
    Why do we need to use RECORD and RECORD THRU , why do we need to skip the first and last rows of the data files by using these two commands.

    Regards,
    Priyabrat

    1. Prasanna

      Hi

      since the first record will come along with column name, we will skip the first record

  4. Shari Vigil

    We use FASTLOAD in JCL with NOSPIE option, what does this do? Why is it needed?

    Thanks!

    1. vg

      Hi Shari,

      NOSPIE parm in JCL used with FASTLOAD in order to write SYSDUMP messages in case of job failures. This parm was mandate till TD 13.0 version. TD 13.10 version on wards we do not need NOSPIE parm in our JCL.

  1. Teradata Utilities – FASTLOAD part 3 » TeraData Tech » BlinkMoth Software Industries | BlinkMoth Software Industries

    […] Post From Teradata – Google Blog Search: A sample Fastload script. Below is the sample Fastload script which is designed to work in […]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>