Hi,
DB2 9.7 fp 4 / Win 2008 / 6 CPU / 8GB RAM

DBSIZE around 800GB.

This is the first time we are deleting data using a java apps which deletes older than YY-MM-DD.
The java apps was develop by our developers and uses correct indexes (no table scan).

Out of 400 million rows from 8 tables, we need to delete around 100 million rows.
The deletion ran around 12 hours and deleted only 10 million rows. So, I had to kill the job in the middle.
I setup infinitive logs each 100 MB size. I can see now around 150 LOGS were archived.
How can i investigate its performance ? what are the areas i may need to tune / check ?
Please advise.
Thanks,

That's about 4.5 ms per delete, which is probably as good as you can get if the application deletes rows one by one.

Look at the delete statement explain plan and at the monreport.dbsummary() output -- that might give you an idea where you can find improvement.

I guessed similar thing as n_i(if the application deletes rows one by one), too.

Please try to use SQL's set oriented capabilities,
i.e. delete multiple rows by one SQL delete statement which includes predicates(to judge the rows to be deleted) in it,
not to include the logic(to judge the rows to be deleted) in Java code.

All,
Thanks for your contribution, very helpfull.
Let see hou much that i can dig.

Regards,

In the old days (my mainframe years) we used the 80/20 rule: when you have to delete more than 20% it is more efficient to dump the table to a sequential file(only the 80% of the rows which have to stay) and use that file to reload the table.

I have no reason to believe that the above rule is not valid anymore....

system does not allow me to delete the doublepost
sorry guys

deleted now

Hi,

delete completed ?
Have you did any changes to anything to improve performance ?

My previous post on delete was about deleting a post in the thread.

Hi,
I had to backout the deletion and I restored the DB now.
Our developers are now reviewing the code.

This was not a stright forward delete. There is a business rules too, to find the correct data. Also couple of tables were joined.
I was not sure that the issue was related to the DB or Code.

While deletion was running, I switched ON all the monitor switches but I couldn't see much information to find why its running slow.

This type of situation, which monitoring tool is better ? eventmonitor ? OR DB2Trace OR FFODC ?

Thanks.

I want to repeat again
Many people don't know capabiliies of SQL language enough, and sometimes don't know their ignorance for SQL too.

Please try to integrate the rules/logics("business rules to find the correct data" , "couple of tables were joined") into
one final(sometimes, more than one) SQL statement(not by using intermediate/temporary tables, nor built-in to host language),
by compensation of efforts to improve your SQL programming skills,
if you want to get better execution performance.

Few time back I used this kind of delete :
create a a dublicate table ... insert the data into that duplicate table through NLI or cursor loading. when it finished then replace table names... and if everything went smooth you can load original table from NULL with replace option and then delete table.

Like that idea

That is a good idea.

But, I think it depends on the practical circumstances ... You can do this if you can afford downtime till you rebuild the new table, indexes and collect stats.
You can take this approach even when the db is online if you are dealing with a insert-only table (say, a business logging table). Rename of the table can be done with almost-no downtime.

Renames get tricky when you have dependent objects - Child tables, views, routines etc.

Agree excluding collect stats. That's no extra time. Gotta be done in every senario.