Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 QUERY

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Fri Feb 28, 2014 7:50 pm    Post subject: DB2 QUERY
Reply with quote

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
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Feb 28, 2014 7:58 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1535
Location: Andromeda Galaxy

PostPosted: Fri Feb 28, 2014 8:00 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Fri Feb 28, 2014 8:02 pm    Post subject: hi
Reply with quote

my DBA is not accepting to do the delete, they want to delete through the program only.
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Fri Feb 28, 2014 8:05 pm    Post subject:
Reply with quote

i am reading a file and delete the records in a single query without using CURSOR.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1535
Location: Andromeda Galaxy

PostPosted: Fri Feb 28, 2014 8:18 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1717
Location: NY,USA

PostPosted: Sat Mar 01, 2014 12:53 pm    Post subject:
Reply with quote

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..

http://ibmmainframes.com/about7691.html
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us