Job to trucate tables

dsdeveloper123 · Post by **dsdeveloper123** » Sun Jun 24, 2007 9:54 pm

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!!!

keshav0307 · Post by **keshav0307** » Sun Jun 24, 2007 10:04 pm

oh yes, you have many ways to do it. you can choose best of that.
you can have a routine, to truncate the table, and call it before the insert job.
you can also have a job to delete records from table. before this insert job.

there may be some other silly methods

dsdeveloper123 · Post by **dsdeveloper123** » Sun Jun 24, 2007 10:18 pm

Thank you...
I m calling a user defined sql file which will delete all the tables I use.

dsdeveloper123 · Post by **dsdeveloper123** » Sun Jun 24, 2007 10:25 pm

Ooppss...
want to know something...
can i use ExecCommand in the job sequencer to execute a sql file which can truncate the tables

chulett · Post by **chulett** » Sun Jun 24, 2007 10:30 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.

dsdeveloper123 · Post by **dsdeveloper123** » Mon Jun 25, 2007 12:25 am

Got ur point...
I am going with truncate and insert option..
Thank u

rameshrr3 · Post by **rameshrr3** » Mon Jun 25, 2007 4:55 am

Alternatively : create a stored procedure that clears data in your tables. Create a job that has a stored proc stage, and and output link to capture the return code and proc message to a sequential file. Call this job first in your sequencer.

chulett · Post by **chulett** » Mon Jun 25, 2007 6:10 am

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.

dsdeveloper123 · Post by **dsdeveloper123** » Wed Jun 27, 2007 4:48 am

Ok..

Can I call sub-routine from the job properties. So that it calls a sql file to delete the tables. I have a series of insertions. So I want to truncate the tables before my jobs runs in a sequencer.

dsdeveloper123 · Post by **dsdeveloper123** » Wed Jun 27, 2007 4:48 am

Ok..

Can I call sub-routine from the job properties. So that it calls a sql file to delete the tables. I have a series of insertions. So I want to truncate the tables before my jobs runs in a sequencer.

dsdeveloper123 · Post by **dsdeveloper123** » Wed Jun 27, 2007 4:51 am

Ok..

Can I call sub-routine from the job properties. So that it calls a sql file to delete the tables. I have a series of insertions. So I want to truncate the tables before my jobs runs in a sequencer.

dsdeveloper123 · Post by **dsdeveloper123** » Wed Jun 27, 2007 4:58 am

Ok..

Can I call sub-routine from the job properties. So that it calls a sql file to delete the tables. I have a series of insertions. So I want to truncate the tables before my jobs runs in a sequencer.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 27, 2007 5:01 am

No.
No.
No.
No.

klsrao · Post by **klsrao** » Wed Jun 27, 2007 5:23 am

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

chulett · Post by **chulett** » Wed Jun 27, 2007 6:56 am

Still curious what's so horrible about the 'Truncate then insert' action most stages support? Unless you are using ODBC, from what I recall it doesn't have that as a selectable 'update action', sticking you with the transactional delete of 'Clear' as your only option there.