DB2_UDB_API stage delete data in four tables

[suresh_dsx]

Hi,
I have a job which contains the following stages.

Code: Select all

                            |----------->delete cust_table (Based on 4 columns)
                            |------------>delete prod_table (Based on 4 columns)
DB2_source---------->cpystage--------> del cust_table (Based on 4 columns)
                           |------------>del sales_table(Based on 4 columns

we are using db2 stage is DB2_UDB_API stage.
DB2_source contains the select query from a table which contains only 15000 rows.

When i try to delete data in the four tables. the job is running past 3 hours. we are unable to figure the issue beacuse very less number of records.

Based on fourms we have added index on the tables. but still there is no improvement.

I dont know the nature of DB2_UDB_API stage take some steps to delete data in the tables.

Any help greatly appriciated.

Thanks -Suresh

[chulett]

Have your DBA trace the sessions and see what is going on and if the indexes are being utilized. If this was Oracle I'd tell you to check the "explain plan" but I have no idea what the equivalent is for DB2.

Also, I'm guessing each input row deletes a single record from each target? If so, can you "widen" that a bit? Anything in common amongst the records where you could delete groups or perhaps all of them with one operation?

[MT]

Hi suresh_dsx,

what you do is extremely expensive - as Chulett mentioned as well - you will delete let us say 15000 x 4 rows = 60000 rows if every row in the source is selected. The real problem behind that is that you will issue 120000 statements for that
60000 Deletes (of a single rows each)
60000 Commits

Talk to your DBA maybe you could definie referential integrity between the tables because they all depend n the same key as it seems. This would reduce the number of necessary delete statements because DB2 could do the remaining deletes internally through the RI definition.
It would even be better if you could have only a single delete statement per table with some kind of range predicate - you would see a incredible performance increase then.

@Chulett: In DB2 your Oracle explain plan is called explain plan

[chulett]

@Chulett: In DB2 your Oracle explain plan is called explain plan

Good to know! :D