As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at email@example.com and provide your corporate email address, company, and name.
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>,
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.
Posts: 397 | Location: New York City | Registered: May 03, 2007
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
-* Build empty table for bulk load.
SELECT * FROM &1 WHERE 1 = 2;
TABLE ON TABLE HOLD AS SQLINLD FORMAT TAB
-* 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
-* Call bulk load (Replace) with empty table.
BULKLOAD &1 FOR SQLINLD
-* Send message to job log
-TYPE (ICM-Trunc) Truncate for table &1 finished.
-* Error - No table name requested
-TYPE (ICM-Trunc) No table requested!
-TYPE (ICM-Trunc) Truncate not done.
-SET &&CM__RETURN = 100;
"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 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster