Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2017
    Posts
    1

    Unanswered: How to work "by slices" on a large table

    We need to clean up a large table: basically, we need to execute some SELECT to find out unwanted rows and then some DELETE to suppress them. But this should not be done in one single pass because the table is too large and that would take too much time. We consider doing it "by slices", meaning the SELECT used to find the rows to be deleted and the DELETE used to suppress them would not have the full table as scope, but only a part of it. We would then iterate over the next slices of the table until the whole table has been cleaned up.
    To be more specific, with Oracle we would use something like:
    SELECT * FROM (
    SELECT *, ROWNUM RNUM FROM table WHERE ROWNUM < slice_end
    ) WHERE RNUM >= slice_start
    and we would apply the cleanup statements to the result of these two SELECT.
    Is it something possible with DB2 and how could it be achieved?

    Patrick

  2. #2
    Join Date
    Jan 2003
    Posts
    4,310
    Provided Answers: 5
    I would iterate through something like this:

    Code:
    declare global temporary table myTemp like My table on commit preserve rows not logged with replace;
    
    insert into session.myTemp select * from myTable Where something = Something  fetch first 1000 rows only;
    
    delete from myTable where myPK in (select myPK from session.myTemp);
    Andy

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    2 ways for this:

    1. Run the same statement below until you get SQLSTATE '02000' (no rows deleted):
    delete from (select 1 from mytable where .... fetch first 1000 rows only)
    This means that you delete not more than 1000 rows with each such a statement. You can commit after a number of such statements to avoid the 'log full' situation.

    2. Open a 'with hold' cursor for 'select 1 from mytable where ....' statement and use 'delete from mytable where current of cursor_name' (so called positioned delete) after each 'fetch cursor_name' statement. You can commit after a number of such deletes to avoid the 'log full' situation. Cursor position of 'with hold' cursor isn't lost after the commit statement.

    Both methods are restartable meaning, that you can even stop/force the application running the code, and restart it again later without any modification if you want.
    Regards,
    Mark.

  4. #4
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    There are, also, a few examples here on the board of a procedure to do the same. Other methods would include unload the data you wish to keep and then load replace the table with that data.

Posting Permissions

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