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.....
Clearing Multiple Tables
Moderators: chulett, rschirm, roy
Which stage are you using to do this? Most DB stages will support a "before" command.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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: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 wrote:I am using a DB2 stage.
Should I use like a DELETE or is there anything else to clear multiple tables ?
<sigh> As mentioned earlier, don't use DELETE in DB2, do an import from /dev/null.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
[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
[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