Db2

 View Only
Expand all | Collapse all

How DB2 old/final table works while delete and updating data in chunks

  • 1.  How DB2 old/final table works while delete and updating data in chunks

    Posted Tue April 27, 2021 07:07 AM

    I have one sql which will delete the data in chunks from table to avoid tempspace issue. can anyone help me to understand how below statement works

    select count(1) from old table (delete from (SELECT * FROM "table name" WHERE RPT_DT = 'date' fetch first $DELETE_INCR rows only))"





    #Db2
    #Db2forLUW
    #Support
    #SupportMigration


  • 2.  RE: How DB2 old/final table works while delete and updating data in chunks

    Posted Fri May 07, 2021 01:59 PM

    Hi,

    The statement deletes the number of $DELETE_INCR rows from the table "table name" where RPT_DT = 'date', and counts the number of deleted rows that was actually deleted. The "old table" represents an intermediate result table containing the deleted rows.

    Ref. Retrieval of result sets from an SQL data change statementHere is an example to help you understand how the statement works.

    1.Create a table.

    $ db2 "create table t1(c1 int,c2 int)" DB20000I The SQL command completed successfully.

    2.Insert 5 records.

    $ db2 "insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5)" DB20000I The SQL command completed successfully. $ db2 "select * from t1" C1 C2 ----------- ----------- 1 1 2 2 3 3 4 4 5 5 5 record(s) selected. $ db2 "select count(1) from t1" 1 ----------- 5 1 record(s) selected.

    3.Select the Intermediate result table(deleted records) each time we delete 2 records from the table.

    $ db2 "select * from old table (delete from (select * from t1 fetch first 2 rows only))" C1 C2 ----------- ----------- 1 1 2 2 2 record(s) selected. $ db2 "select * from old table (delete from (select * from t1 fetch first 2 rows only))" C1 C2 ----------- ----------- 3 3 4 4 2 record(s) selected. $ db2 "select * from old table (delete from (select * from t1 fetch first 2 rows only))" C1 C2 ----------- ----------- 5 5 1 record(s) selected.

    4.Populate 5 rows again.

    $ db2 "insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5)" DB20000I The SQL command completed successfully.

    5.Now count the number of the Intermediate result table each time we delete 2 records from the table.

    $ db2 "select count(1) from old table (delete from (select * from t1 fetch first 2 rows only))" 1 ----------- 2 1 record(s) selected. $ db2 "select count(1) from old table (delete from (select * from t1 fetch first 2 rows only))" 1 ----------- 2 1 record(s) selected. $ db2 "select count(1) from old table (delete from (select * from t1 fetch first 2 rows only))" 1 ----------- 1 1 record(s) selected. $ db2 "select * from t1" C1 C2 ----------- ----------- 0 record(s) selected.



    #Db2
    #Db2forLUW
    #Support
    #SupportMigration