All Forums Tools
Aruna N 1 post Joined 05/09
05 May 2009
Fastload utility

I have file with large number of fields. And i want it to load only certain fields into an empty table using fast load.While defining the input file do i need to give the layout of the entire file or is there any option to define only the required fields to load the table as like in MLOAD.

Adeel Chaudhry 773 posts Joined 04/08
05 May 2009

Hello,You need to use the concept of 'filler' .... which means define all the columns in the define block with proper data-types .... but only use those required in the INSERT clause and simply ignore those which are not required.HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

r123 5 posts Joined 05/09
11 May 2009

Hi,How this filler concept should be used with fastload? Can someone give working examples? The previous suggestion gives error to me.Table ddl:CREATE SET TABLE EDW.T01000_PARTY_DDD ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( Party_Id INTEGER NOT NULL)PRIMARY INDEX XPKT01000_PARTY_DD ( Party_Id );Fastload file:.SET SESSION CHARSET "ASCII";LOGON IP/DBC,dbc ;DATABASE EDW;SET RECORD VARTEXT " " NOSTOP;DEFINE Party_Id (VARCHAR(30)), FILLER_IOC (VARCHAR(30))FILE=EDW.T01000_PARTY_DD.TXT;SHOW;BEGIN LOADING "T01000_PARTY_DDD"ERRORFILES EDW.T01000_PARTY_DDD1, EDW.T01000_PARTY_DDD2;RECORD 2;INSERT INTO "T01000_PARTY_DDD" (:Party_Id) values (:Party_Id);END LOADING ;LOGOFF;Data:Party_Id FILLER_IOC200231197 2200864306 2 Error:0009 INSERT INTO "T01000_PARTY_DDD" ( :Party_Id ) values ( :Party_Id );**** 11:41:17 RDBMS error 3706: Syntax error: Column name list is invalid.Regards,Roberts

Adeel Chaudhry 773 posts Joined 04/08
11 May 2009

Hello,Following example may be of some help:.SESSIONS 8;.LOGON dbc,dbc;DATABASE MyDB;ERRLIMIT 50000;DROP TABLE MyTable;DROP TABLE MyTable_Err1;DROP TABLE MyTable_Err2;CREATE TABLE MyTable(Col1 DATE NOT NULL,Col2 TIME NOT NULL,Col3 TIME NOT NULL,Col4 FLOAT NOT NULL)UNIQUE PRIMARY INDEX (Col1);BEGIN LOADING MyTableERRORFILES MyTable_ERR1, MyTable_ERR2CHECKPOINT 10000;.SET RECORD VARTEXT ",";DEFINE Col1 (VARCHAR(255)) ,Col2 (VARCHAR(255)) ,Filler1 (VARCHAR(255)) ,Col3 (VARCHAR(255)) ,Filler2 (VARCHAR(255)) ,Col4 (VARCHAR(255)) FILE = MyTable.csv;SHOW;INSERT INTO MyTable( Col1 = :Col1 ,Col2 = :Col2 ,Col3 = :Col3 ,Col4 = :Col4);END LOADING;LOGOFF;Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

r123 5 posts Joined 05/09
11 May 2009

Thank you! It realy works I just noticed error in my generated fastload script. There should be "," as separator for column list in first part of insert script.Regards,Roberts

You must sign in to leave a comment.