Table action as truncate Vs truncate statement in before SQL
Moderators: chulett, rschirm, roy
Table action as truncate Vs truncate statement in before SQL
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.
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.
Thanks and Regards!!
dspxlearn
dspxlearn
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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?
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.
Last edited by chulett on Fri May 29, 2015 8:45 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
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
http://www-01.ibm.com/support/docview.w ... wg21445833
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: