DB2 QUERY

sivasaras · New User Joined: 29 Sep 2007 Posts: 93 Location: chenna/i-

hI,

I have written a one time program to delete 2milion records from the table,
i am still not clear how to use checkpoint in the delete query program.

my program is having only delete query

thnks
siva

dick scherrer · Posted: Fri Feb 28, 2014 7:58 pm

Hello,

Suggest you unload the table, copy the "Keepers" to a new file. reload the table with the copied file.

This will run faster than a ton of deletes and gives you a backup of the entire table before the deetes (the unloaded data).

I would not consider any kind of checkpoint/restart.

Pandora-Box · Posted: Fri Feb 28, 2014 8:00 pm

Are you just doing a delete via cursor or delete via single sql or reading a file and deleting the data?

When you say checkpoint I assume you ment a check point for a commit and a restart

sivasaras · New User Joined: 29 Sep 2007 Posts: 93 Location: chenna/i-

my DBA is not accepting to do the delete, they want to delete through the program only.

sivasaras · New User Joined: 29 Sep 2007 Posts: 93 Location: chenna/i-

i am reading a file and delete the records in a single query without using CURSOR.

Pandora-Box · Posted: Fri Feb 28, 2014 8:18 pm

Siva,

You could try Dick's solution and I feel it is the best as doing a load replace you could also take care of REORG + RUNSTATS

If you still insist an Application program

Take care of these things

- Set a optimal commit frequency (Talk to your DBA)
- Store your commit data in a restart table (if you have any) or use files for your program
- On beginning of program execution read the restart table and see if it normal run or restart run
- If restart run skip the number of records trhat were processed from file or skip based on the key.After succesful execution ensure to reset the flags/data in restart table for next run

Rohit Umarjikar · Posted: Sat Mar 01, 2014 12:53 pm

Please refer to the below thread and a very informational document is attached by dick at the end which contains sample programs as well and this could be very easy for you to understand..

ibmmainframes.com/about7691.html