Conditional Delete with commit issue

mdx34 · #1 (**permalink**) 07-10-12, 09:18

DB2 V9.5 WSE FP8 - RHEL6.2

I have researched but could not find a delete statement that looks like mine to create a delete process with commit every N rows.

I have ran the below statement but it did not delete any records.

Here is the original statement that deletes the rows but blows up the logs even I set the logs to max (250). I also increased the log size to 4096. It deletes about 7 million rows and leaves the table with about 150,000 rows

db2 "DELETE FROM schema1.TABLE1 where (id NOT in(select id from schema1.TABLE2 ))"

Here is the one that does not delete anything and gives a message that ".....result of a query is an empty table."

db2 "DELETE FROM schema1.TABLE1 where (id NOT in(select id from schema1.TABLE2 FETCH FIRST 250000 ROWS ONLY))" ;

commit;

What is the best way to delete the table and how can I put this into a while statament (or any other method but not a stored procedure as this is part of many other deletes) ?

Thanks.

sathyaram_s · #2 (**permalink**) 07-10-12, 11:17

You can use a unix shell script to execute your "first 250000 rows only" delete in an infinite loop. The exit criteria from the loop will be a unix return code of 1. When you exit the loop, recheck the data in the table to confirm the records are all deleted ..

select 1 from schema1.table1 where exists (select * from schema1.table1 where id not in (select id from schema1.table2)

or similar...

tonkuma · #3 (**permalink**) 07-10-12, 11:33

I have two ideas now.

(1) EXPORT ---> TRUNCATE ---> INPORT
This way might be better, because the number of rows to be kept looks relatively much smaller(10% or less) than the number of rows in the table.
(1-1) EXPORT rows to be kept.
(1-2) Tuncate table. For example Truncate Table or TRUNCATE statement on DB2 9.7.
(1-3) INPORT the rows kept in (1-1).

(2) delete with commit every N rows.
See my posts(#8 - #11) in this thread
developerWorks : Information Management : IBM DB2 for Linux, Unix, and Windows Forum : Delete Script 250 rows at time ...

mdx34 · #4 (**permalink**) 07-10-12, 11:41

Thank you for your replies. Please ignore this post. I found out that the logs are blown up before it comes to this statement. Previous statements are also conditional delete statements with no commit in between and some of the delete statements like "Delete from schema.table" are the casue of the log problem instead of using truncate.

These are multiple scripts given to me by our developers to clean up the data to initialize the database. They have a very small development databases where this is not an issue but when I run it in client test/production it becomes a problem because the volume of the data.

Thanks again

papadi · #5 (**permalink**) 07-10-12, 16:05

Before promoting the offering to "client testing", some full-volume testing would be advisable. . .

fengsun2 · #6 (**permalink**) 07-10-12, 22:11

Quote:

Originally Posted by tonkuma

I have two ideas now.

(1) EXPORT ---> TRUNCATE ---> INPORT
This way might be better, because the number of rows to be kept looks relatively smaller than the number of rows in the table.
(1-1) EXPORT rows to be kept.
(1-2) Tuncate table. For example Truncate Table or TRUNCATE statement on DB2 9.7.
(1-3) INPORT the rows kept in (1-1).

(2) delete with commit every N rows.
See my posts(#8 - #11) in this thread
developerWorks : Information Management : IBM DB2 for Linux, Unix, and Windows Forum : Delete Script 250 rows at time ...

Maybe we can change a little for performance consideration.

Code:

BEGIN
DECLARE delete_count INT DEFAULT 0;

delete_loop:
REPEAT
   SELECT COUNT(*)
    INTO  delete_count
    FROM  OLD TABLE
         (DELETE FROM (SELECT *
                        FROM  reporter_status
                        WHERE lastoccurence < current date - 6 MONTHs
                        FETCH FIRST 250 ROWS ONLY
                      )
         );
 
   INSERT INTO log_reporter_status(text)
   VALUES 'delete_count = ' || delete_count || '.' ;
 
   COMMIT;
   if delete_count < 250 then
      leave delete_loop;
   end if;
UNTIL
   delete_count <= 0
END REPEAT;

tonkuma · #7 (**permalink**) 07-10-12, 22:36

If additional code is to avoid a final(extra) loop,
this may be enough.

Note 1: Reformatted(adjusted leading blanks and new-line)
Note 2: Adding a meaningful name to a loop(if not necessary) may be a good habit.

Code:

BEGIN
DECLARE delete_count INT DEFAULT 0;

delete_loop:
REPEAT
   SELECT COUNT(*)
    INTO  delete_count
    FROM  OLD TABLE
         (DELETE
           FROM (SELECT *
                  FROM  reporter_status
                  WHERE lastoccurence < current date - 6 MONTHs
                  FETCH FIRST 250 ROWS ONLY
                )
         );
 
   INSERT INTO log_reporter_status(text)
   VALUES 'delete_count = ' || delete_count || '.' ;
 
   COMMIT;
UNTIL
   delete_count < 250
END REPEAT;

fengsun2 · #8 (**permalink**) 07-10-12, 23:17

yeah， “delete_count < 250” is better。

Internet Services

Web Development

Digital Marketing

Consumer Tech