How to write a sample Fastload script?

How to write a sample Fastload script?

A fastload script will load data into a single table from one/more files.

Multifile loading will be covered in a future post.

A single file Fastload example is given below:

---------------------------

SESSIONS 5;

TENACITY 5;

SLEEP 5;
ERRLIMIT 50;

.logon server1/user1,passwd123;

DROP TABLE TL_62917_DLY1_QRMDB.FLD1;

CREATE MULTISET TABLE TL_62917_DLY1_QRMDB.FLD1

(

NAME VARCHAR(20)

,DOB DATE format 'yyyy-mm-dd'

)

PRIMARY INDEX(NAME)

;

DROP TABLE tl_62917_dly1_qrmdb.fld1_WT;

DROP TABLE tl_62917_dly1_qrmdb.fld1_ET;

DROP TABLE tl_62917_dly1_qrmdb.fld1_UV;

　

.SET RECORD VARTEXT "|";

DEFINE

NAME (VARCHAR(20)),

DOB (varchar(20))

　

　

FILE=/wload/wzed/app/subwload/ALL_DWH/sql/NON_INCOME/fld1.csv;

SHOW;

BEGIN LOADING tl_62917_dly1_qrmdb.fld1 ERRORFILES tl_62917_dly1_qrmdb.fld1_ET , tl_62917_dly1_qrmdb.fld1_UV

CHECKPOINT 1000;

INSERT INTO tl_62917_dly1_qrmdb.fld1

VALUES

(

:NAME,

:DOB(date, format 'ddmmyyyy')

);

　

END LOADING;

.LOGOFF;

.QUIT;

FASTLOAD:

---------------------------


How to improve the Fastload performance:

1. Check the total sessions assigned for the FASTLOAD. Higher the sessions, lesser the time.

2. If you are loading a very large file, increase the .CHECKPOINT.
    A checkpoint regularly makes an entry about the rows successfully loaded. So, its an overhead.

    Rule of thumb: Set a checkpoint at 10% of your total row-count in the file.

Share with us your problems, and we will help you solve them.
Donate if you are helped.
 For any training inquires please call us or fill the inquiry form on the right.