DSXchange

Hello,

I need to clear three tables before I execute my job. My Job currently does a clear and insert. Due to the dependancy on other tables i need to clear all three tables before I begin the job. Any suggestions on how I could accomplish this..? Should I do it by way of a script or is there any short way.....

Which stage are you using to do this? Most DB stages will support a "before" command.

I am using a DB2 stage.

Should I use like a DELETE or is there anything else to clear multiple tables ?

DB2 is a bit tricky when it comes to efficiently deleting data, usually the approach is to use a "IMPORT FROM /dev/null OF DEL REPLACE INTO TableName" command and you can put that into the DB2 stage OPEN command.

Be more modular. Create a separate job to clear each file. Each job sets "clear before writing" then sends 0 rows. Use job sequences to tie it all together with your downstream requirements.

[quote="cosec"]I am using a DB2 stage.

Should I use like a DELETE or is there anything else to clear multiple tables ?[/quote]

I recommend you to write all your delete sentences in the first DB2 stage's before label which has the DB2 component; using the operator ';' (without quotes) as separator.

What if there are lots of records in the target table....will it affect performance ? Wouldn't it better to have a separate job to clear the table like ray says....or before routine script ?? Anyone know of a script to clear the tables ?

reypotxo wrote:

cosec wrote:I am using a DB2 stage.

Should I use like a DELETE or is there anything else to clear multiple tables ?

I recommend you to write all your delete sentences in the first DB2 stage's before label which has the DB2 component; using the operator ';' (without quotes) as separator.

<sigh> As mentioned earlier, don't use DELETE in DB2, do an import from /dev/null.

[quote="cosec"]What if there are lots of records in the target table....will it affect performance ? Wouldn't it better to have a separate job to clear the table like ray says....or before routine script ?? Anyone know of a script to clear the tables ?

[quote="reypotxo"][quote="cosec"]I am using a DB2 stage.

Should I use like a DELETE or is there anything else to clear multiple tables ?[/quote]

I recommend you to write all your delete sentences in the first DB2 stage's before label which has the DB2 component; using the operator ';' (without quotes) as separator.[/quote][/quote]

It depends on database manager configuration. If it runs queries one by one or if it could parallelize several ones. If the first options is yours, i would try the first because runs queries sequentially, is cleaner and easier to implement, otherwise i'd try the second one.
The ultimate possibility would be use own system instructions like clrpfm from AS400 that is the fastest way to clean files but it is more complex to make and not portable to another systems