Job to trucate tables
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 33
- Joined: Sun Jun 24, 2007 9:46 pm
Job to trucate tables
Hi,
I pretty new to datastage. So the question I am posting might be very silly.
I have a scenario where in I have to clear all the tables before inserting. I know there is a option like truncate and insert. But what I want to know is, if there is some other solution to this problem. Can I have a job in the sequencer which will I can call first in the sequence so that I can clear all the tables.
Thank you in advance!!!
I pretty new to datastage. So the question I am posting might be very silly.
I have a scenario where in I have to clear all the tables before inserting. I know there is a option like truncate and insert. But what I want to know is, if there is some other solution to this problem. Can I have a job in the sequencer which will I can call first in the sequence so that I can clear all the tables.
Thank you in advance!!!
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 33
- Joined: Sun Jun 24, 2007 9:46 pm
-
- Participant
- Posts: 33
- Joined: Sun Jun 24, 2007 9:46 pm
Sure, any script you can write can be run from the Execute Command stage... but are you sure that's the right approach? With the truncate built into the load job, it can be easily restarted. However, what if your load fails and the solution is to truncate again? Now you've got to find a way to rerun the truncate step, which will probably mean a manual step.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 33
- Joined: Sun Jun 24, 2007 9:46 pm
There's multiple ways to accomplish this. A job could source from a transformer, pass no rows to a DB stage and simply perform the 'truncate'. I have a multi-instance job that 'truncates any table' with the InvocationID being the table name. Food for thought.
And a separate truncate step may very well be ok in an 'all or nothing' load strategy - one where the Transaction Size is zero. Make sure you think your design through, not just the running but the restart / recovery as well.
And a separate truncate step may very well be ok in an 'all or nothing' load strategy - one where the Transaction Size is zero. Make sure you think your design through, not just the running but the restart / recovery as well.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 33
- Joined: Sun Jun 24, 2007 9:46 pm
-
- Participant
- Posts: 33
- Joined: Sun Jun 24, 2007 9:46 pm
-
- Participant
- Posts: 33
- Joined: Sun Jun 24, 2007 9:46 pm
-
- Participant
- Posts: 33
- Joined: Sun Jun 24, 2007 9:46 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can do the following steps to avoid truncate when you restart job sequence:
1) Create a sequence "One" with one job that contains Truncate (in any way discussed above).
2) Create another sequence "Two" with rest of the jobs.
3) Run sequence "One" as a separate process.
4) Run sequence "Two" as a separate process after running sequence "One".
If sequence "Two" fails, you need to restart sequence "Two" only.
Lakshmi Srinivas
1) Create a sequence "One" with one job that contains Truncate (in any way discussed above).
2) Create another sequence "Two" with rest of the jobs.
3) Run sequence "One" as a separate process.
4) Run sequence "Two" as a separate process after running sequence "One".
If sequence "Two" fails, you need to restart sequence "Two" only.
Lakshmi Srinivas
![Confused :?](./images/smilies/icon_confused.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers