How to delete the millions of records from a db2 table

Question

I am using DB2 9.7 for Z/oS. I have a table with 10+ million rows and I want to delete about 9 million rows. I have tried with below stored procedure, but without success.

CREATE PROCEDURE DELETE_DATA 
(IN V_TABLE_NAME VARCHAR(24), 
IN V_COMMIT_COUNT INTEGER, 
IN V_WHERE_CONDITION VARCHAR(1024))

DYNAMIC RESULT SETS 01
COMMIT ON RETURN NO
LANGUAGE    SQL
NOT DETERMINISTIC

L3 : BEGIN

-- DECLARE STATEMENTS
DECLARE SQLCODE INTEGER;
DECLARE V_DELETE_QUERY VARCHAR(1024);
DECLARE V_DELETE_STATEMENT STATEMENT;

SET V_DELETE_QUERY = 'DELETE FROM ' || V_TABLE_NAME || 
' WHERE COLUMN IN (SELECT COLUMN FROM  ' 
|| V_TABLE_NAME ||  'WHERE COLUMN_ID =' 
|| V_WHERE_CONDITION  || ' FETCH FIRST ' 
|| RTRIM(CHAR(V_COMMIT_COUNT)) || ' ROWS ONLY) ';

PREPARE V_DELETE_STATEMENT FROM V_DELETE_QUERY;
DEL_LOOP:
    LOOP
        EXECUTE V_DELETE_STATEMENT;
        IF SQLCODE = 100 THEN
            LEAVE DEL_LOOP; 
        END IF;
        COMMIT;
    END LOOP;

COMMIT;
END L3

Suggest me any changes in the above stored procedure.

If I would have been at your place then I would have inserted 1 million records which I dont want to delete in a seperate table and dropped the original table and rename the table having 1 million record to the original table name.(*Assuming it as a one time activity*) — Rahul Tripathi, Sep 22 '15 at 13:06
I have to do this activity daily, before loading new data into table — Bobby, Sep 22 '15 at 13:11
That can sometimes be a bit too tricky, dependencies you know. (triggers, views, foreign keys etc.) (Answer to Rahul Tripathi's comment.) — jarlh, Sep 22 '15 at 13:11
@jarlh:- Agreed, that's why I added at the last if its a *one time activity*. — Rahul Tripathi, Sep 22 '15 at 13:13
Creating a new table in production platform is not that easy. — Bobby, Sep 22 '15 at 13:26
Instead of concatenating your parameters you should be using the `?` marker and `EXECUTE USING`... but the bigger issue I see is that you should be coding the delete explicitly for each table to purge. — Stavr00, Sep 22 '15 at 13:27
There's no such thing as DB2 9.7 for z/OS. And what do you mean by "without success"? — mustaccio, Sep 22 '15 at 13:33
See this question http://stackoverflow.com/questions/5205981/db2-stored-procedure-controlled-large-record-deletion/20470577#20470577 — Stavr00, Sep 22 '15 at 15:12
Have you checked the answer in that question. And do u think it works? — Bobby, Sep 22 '15 at 15:16
`FETCH FIRST ... ROWS` returns non-deterministic results in the case where you have no `ORDER BY`. What exactly are you wanting to delete, yesterday's records? — Clockwork-Muse, Sep 25 '15 at 02:36

score 2 · Answer 1 · answered Sep 28 '15 at 14:12

Extract the data that you wish to preserve, into a file suitable for input to sql loader utility. Generate you new data, also to a file suitable for input to sql loader. Truncate the destination table. Run Sql loader using the two input files. Clean, simple, repeatable, and it doesn't load up the log file.

How to delete the millions of records from a db2 table

1 Answers1