Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    268

    Unanswered: Conditional Delete with commit issue

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 http://www.dbforums.com/db2/642857-truncate-table.html 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 ...
    Last edited by tonkuma; 07-10-12 at 23:40. Reason: Added "much" ... "(10% or less)"

  4. #4
    Join Date
    Oct 2004
    Posts
    268
    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

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Before promoting the offering to "client testing", some full-volume testing would be advisable. . .

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by tonkuma View Post
    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 http://www.dbforums.com/db2/642857-truncate-table.html 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;

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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;

  8. #8
    Join Date
    Nov 2011
    Posts
    334
    yeah, “delete_count < 250” is better。

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •