Table action as truncate Vs truncate statement in before SQL

dspxlearn · Post by **dspxlearn** » Thu May 28, 2015 2:04 pm

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.

priyadarshikunal · Post by **priyadarshikunal** » Thu May 28, 2015 2:07 pm

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

chulett · Post by **chulett** » Thu May 28, 2015 2:11 pm

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.

ShaneMuir · Post by **ShaneMuir** » Fri May 29, 2015 2:57 am

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?

chulett · Post by **chulett** » Fri May 29, 2015 6:02 am

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

qt_ky · Post by **qt_ky** » Fri May 29, 2015 7:29 am

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.

ShaneMuir · Post by **ShaneMuir** » Fri May 29, 2015 9:04 am

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

chulett · Post by **chulett** » Fri May 29, 2015 11:19 am

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.

qt_ky · Post by **qt_ky** » Fri May 29, 2015 11:31 am

"Working as designed."

ray.wurlod · Post by **ray.wurlod** » Fri May 29, 2015 4:03 pm

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