Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSED] TRUNCATE TABLE in Data Migrator stored procedure
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] TRUNCATE TABLE in Data Migrator stored procedure
 Login/Join
 
Member
posted
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
 
Posts: 19 | Registered: March 19, 2012Reply With QuoteReport This Post
Guru
posted Hide Post
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: 379 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1096 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFocus 7.7.06M/8.2.03M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 149 | Registered: March 29, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSED] TRUNCATE TABLE in Data Migrator stored procedure

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.