I wrote A sgtored procedure in Data Migrator that looks as the below:
ENGINE DB2 SET DEFAULT_CONNECTION ClientDB
TRUNCATE TABLE DB2ADMIN.TMP_UPC_MASTER IMMEDIATE;
The stored procedure seems to truncate the table correctly; however, it stays active (status 'in use') on the DM WebConsole. The log shows that stored procedure completed with error code 0; however the next step, which is a bulk load into the tmp table, does not finish. Both processes, truncate table and load into tmp table show 'in use' status on web console. I have to manually kill the agent in order for the process to stop.
What is wrong with the stored procedures? Is there a better way to truncate DB2 table before loading (note that 'delete data before load' results in transaction log full error. That is why I decided to use truncate. Plus, this is temp data and I do not need logs created by delete process).
thanks!This message has been edited. Last edited by: <Kathryn Henning>,
WebFOCUS 7.7, iWay Data Migrator, Windows
Excel, PDF, HTML, XML
In the current production Release 7.7.05 the "prior to load" of TRUNCATE was added for DB2 tables. There is no longer any need to write a stored procedure using direct pass through to perform this action.
Clif is correct, but if you are using an old version of DB2, this is what we use to do to truncate a DB2 table:
-* Purpose: Invokes the DB2 bulk load process for a single table. -* Process is called with a single (unnamed) argument. -* Args: 1 - Table to be truncated. -* send message to job log. -TYPE (ICM-Trunc) Bulk Truncate Process Invoked. -* Arg 1 - should be passed in from the framework. -DEFAULTH &1 = 'NONE' -* Ensure table name passed -IF &1 EQ 'NONE' THEN GOTO :NOTABLE; -* Send message to log -TYPE (ICM-Trunc) Truncate requested for table &1 -* Uncomment and fix if connection is actually needed. -*ENGINE DB2 SET DEFAULT_CONNECTION &&CONNECTION_NM -*-RUN SET DBLOAD=DB2 SET HNODATA=,$ -RUN -* Build empty table for bulk load. SQL SELECT * FROM &1 WHERE 1 = 2; TABLE ON TABLE HOLD AS SQLINLD FORMAT TAB END -RUN -* get column list of empty table. FILEDEF TFLLST DISK tfllst.fex TABLE FILE SYSCOLUM PRINT COMPUTE C_NAME/A100 = NAME || ';'; WHERE TBNAME EQ 'SQLINLD'; ON TABLE SET ALL ON ON TABLE SET HOLDLIST PRINTONLY ON TABLE HOLD AS TFLLST FORMAT ALPHA END -RUN -* Call bulk load (Replace) with empty table. BULKLOAD &1 FOR SQLINLD -INCLUDE TFLLST BULKPARM BLK_MODE=REPLACE END -RUN -* Send message to job log -TYPE (ICM-Trunc) Truncate for table &1 finished. -EXIT -* Error - No table name requested -:NOTABLE -TYPE (ICM-Trunc) No table requested! -TYPE (ICM-Trunc) Truncate not done. -SET &&CM__RETURN = 100; -EXIT
"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
I like and will use the stored procedure for truncating tables in DB2. I was just wondering about one element.
You use TABLE FILE SYSCOLUM. This seems like a synonym to read other synonyms.
Do you have the definition (synonym) of this SYSCOLUM?
WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
There is a directory of internal synonyms in d:\ibi\srvnn\home\catalog. This folder is added into the server path automatically and will always be available.
|Powered by Social Strata|