Tuesday, 10 July 2012

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.

No comments:

Post a Comment

Please share your thoughts and let us know the topics you want covered