delete rows on db table

theone1987 · New User Joined: 14 Sep 2009 Posts: 60 Location: Milan

Hello everybody,
I need help.
I need to clear a very large number of rows in the DB. The records that I have to delete are contained in a PS file.

What is the quickest way to do it?
load? delete?

I hope that is clear ..

thanks

Kjeld · Posted: Tue Mar 16, 2010 5:48 pm

You probably don't want to do a query and delete on each individual row from your PS, due to the number of rows involved.

I will suggest 2 approaches for total unload with load of retained rows:

do not

You could write a program to read the key values from the PS and update one or more columns in the selected rows that distinctively marks them as obsolete. Subsequently make the total unload, and selectively load the table with the rows that do not have the obsolete criteriae set.

theone1987 · New User Joined: 14 Sep 2009 Posts: 60 Location: Milan

I did not understand the first approach.
while the second approach: it is convenient to make a program that reads each row from the file and made an update on the table?

I would spend less time possible.

Kjeld · Posted: Tue Mar 16, 2010 6:34 pm

If you know what criteriae the records (rows) in your input file is selected from, you could use that knowledge to select what rows to keep = re-load in the table, and avoid using the dataset.

The second approach: You decide... If you want to use the information in your PS file I don't think you can avoid some coding.

A third approach could be processing the unload file to discard all rows found in the PS input file, that could even be done with some batch utility, with applicable control statements.

dick scherrer · Posted: Tue Mar 16, 2010 9:02 pm

Hello,

theone1987 · New User Joined: 14 Sep 2009 Posts: 60 Location: Milan

dick scherrer · Posted: Tue Mar 16, 2010 9:25 pm

Hello,

And how many rows will be deleted each month?

How long does it take to unload the table? How long to reload?

Knowing these may help decide which way to proceed. . .

theone1987 · New User Joined: 14 Sep 2009 Posts: 60 Location: Milan

dick scherrer · Posted: Tue Mar 16, 2010 9:41 pm

Sigh. . . One bit at a time. . .

theone1987 · New User Joined: 14 Sep 2009 Posts: 60 Location: Milan

what was your idea?

dick scherrer · Posted: Tue Mar 16, 2010 9:45 pm

To find out how many rows will be deleted each month. . .

theone1987 · New User Joined: 14 Sep 2009 Posts: 60 Location: Milan

dick scherrer · Posted: Tue Mar 16, 2010 9:59 pm

Hello,

Kjeld · Posted: Wed Mar 17, 2010 1:24 pm

You should confirm with the DBA at your installation if direct delete of the rows is feasible. Delete statements take quite a heave load on the DB2 system, as every row to be deleted has to be written to the database log, so it can be restored to the table in case of a rollback situation.

You should consider designing your delete code to be able to commit frequently. Deleting 1.000.000 rows in one LUW (logical unit of work) can take a considerable amount of time, and in case of an abend the rollback process can take longer time than the delete process took, reading off the DB2 log and restoring into the tablespace and indexes.