DSXchange

Hi,

We have to truncate around 30 tables before loading them individually in differnt DataStage load jobs. So, wondering which one is the best among the below options in terms of faster truncation and loading (time together).

1. Write a stored procedure to truncate all the tables before we trigger the load jobs.
2. Within the ODBC stage, use Table action as "Truncate" | Write mode as "Insert".
3. Within the ODBC stage, use Table action as "Append" | Write mode as "Insert" | Before SQL statement use "Truncate table statement"


Can someone suggest please.

truncate is a ddl operation hence doesn't require much time to execute. why are you even worried about performance of truncate.

Truncate is truncate.

Why complicate things or make them more obtuse? If you always need to truncate each table before it is loaded, go with #2. Never mind the fact that #1 will complicate a restart, meaning without manual intervention of some sort you'd have reload ALL should one fail. And #3 essential hides the truncate.

Just as a note regarding truncate with the ODBC stage, I have noticed in previous versions that the Truncate table action on the ODBC stage when calling Oracle DB actually performs a delete from statement not a truncate, and as a result has an overhead due to the UNDO space requriements.

In these instances I have found it is better to use a before SQL statement to truncate instead.

Not sure if this was ever 'fixed' in later versions of the stage?

Yikes. If that's true (easily testable) then #2 is living up to its number and should be avoided.

I would confirm what ShaneMuir reported. Go with option 3. And if you are talking about loading a lot of data, then also look for a bulk load option. These are faster than insert statements but only if your database support them.

So just to confirm this apparently is by design in the ODBC stage. More information can be found here:
http://www-01.ibm.com/support/docview.w ... wg21445833

Interesting, thanks for posting that. Note that the resolution is not to go all #3 on it but rather use the custom truncate option. That or use a native stage if at all possible.

"Working as designed."

Designed that way because not every database supports a TRUNCATE statement.