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: 1529
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: 1529
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: 1609
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


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