Clearing Multiple Tables

cosec · Post by **cosec** » Thu Jul 26, 2007 11:02 pm

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

ArndW · Post by **ArndW** » Thu Jul 26, 2007 11:09 pm

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

cosec · Post by **cosec** » Thu Jul 26, 2007 11:42 pm

I am using a DB2 stage.

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

ArndW · Post by **ArndW** » Fri Jul 27, 2007 12:16 am

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.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 27, 2007 1:31 am

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.

reypotxo · Post by **reypotxo** » Fri Jul 27, 2007 2:57 am

[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.

cosec · Post by **cosec** » Fri Jul 27, 2007 3:19 am

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.

ArndW · Post by **ArndW** » Fri Jul 27, 2007 3:51 am

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

reypotxo · Post by **reypotxo** » Fri Jul 27, 2007 3:53 am

[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