-3

I want to delete 80 million rows of data from a DB2 table.

This table is used in number of CICS-DB2 programs. We cannot drop the table or unload the required amount of data into a file and then delete the unwanted records. Later load the table from file. As this will require CICS outage. CISCS outage is not possible.

I have tried different methods like DB2 delete cursor, Adhoc job but everything has a limit from 5,000 to 5,00,000 rows.

Please can you suggest a good and optimize way of deleting the rows from table.

Garima
  • 1
  • 2
  • 2
    What is 'Crore'? What is 'lakh'? The question does not appear to relate to JCL, CICS or VSAM and possibly not COBOL. Certainly from the tags used the tag that definitely should have been used is 'mainframe'. – NicC Aug 12 '19 at 11:23
  • 1
    1 Crore = 10 000 000; 1 lakh = 100 000 – Rick Smith Aug 12 '19 at 12:06
  • 1
    So why not delete batches of 50000 rows? – Dour High Arch Aug 12 '19 at 19:11
  • the requirement is to Delete 80 million rows in one go or in batches of 10 Million So that the time required for deleting records will be less. If i delete in batches 50,000 records i have to submit the job for 1600 times. Max i can set the limit to 5,00,000 which will reduce to 160 time – Garima Aug 13 '19 at 08:30

1 Answers1

0

Unload the keys to the rows you wish to delete to a flat file. You likely have a utility on hand to do this, DSNTEP4 or Syncsort would do. You may have to post-process the output if it isn't in the format you need.

Write a batch program that reads the unloaded keys and deletes the corresponding row. Parameterize the program so you can specify how often to issue a COMMIT. Count how many rows you've deleted since the last COMMIT and when you reach the parameter threshold issue another COMMIT.

Pseudocode...

retrieve COMMIT threshold parm
open key-file
read key-file
loop:
    DELETE WHERE KEY = key from key-file
    count++
    if count = COMMIT threshold parm then 
        COMMIT
        count = 0
    end-if
    read key-file
    if EOF exit loop
end-loop
COMMIT
close key-file

The COMMIT threshold is a parameter because you may be advised to adjust it up or down by your DBAs or CICS Sysprogs in order to avoid locking contention problems.

cschneid
  • 10,237
  • 1
  • 28
  • 39