I wrote A sgtored procedure in Data Migrator that looks as the below:

ENGINE DB2 SET DEFAULT_CONNECTION ClientDB
SQL DB2
TRUNCATE TABLE DB2ADMIN.TMP_UPC_MASTER IMMEDIATE;
CONNECT RESET;
TERMINATE;
END
-EXIT

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>,

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

  

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?

regards, Martin.

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.