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
 

 

Commit limit for Delete query through Jcl

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

New User


Joined: 26 Jul 2005
Posts: 55

PostPosted: Thu Dec 17, 2015 12:56 am    Post subject: Commit limit for Delete query through Jcl
Reply with quote

I'm executing a delete query through JCL and its going to delete large number of rows ..is there anyway I can set a commit limit after every 100 records
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


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

PostPosted: Thu Dec 17, 2015 1:00 am    Post subject:
Reply with quote

Quote:
is there anyway I can set a commit limit after every 100 records

And why? and what do you want to achieve by doing this? and how do you restart then?
Do unload before ever you do mass delete that will save you.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 375
Location: USA

PostPosted: Thu Dec 17, 2015 1:59 am    Post subject: Reply to: Commit limit for Delete query through Jcl
Reply with quote

It doesn't make much sense.

I think, the best thing you can do is to split the Delete query into multiple parts based on different ranges for Primary key values.

.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Dec 17, 2015 9:11 am    Post subject:
Reply with quote

Hi,

Agree with Rahul..if at all you want to do it through JCL you will need to spllt query in to multiple queries

@ Rohit..just curious to you know what you mean by how to restart if its simple delete job do we really need to restart? if we are restarting it will simply delete the existing rows matching criteria..

Correct me if I am wrong,..

Regards,
Chandan
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Dec 18, 2015 5:56 am    Post subject:
Reply with quote

Chandan,You are right it do nothing. jumbled icon_rolleyes.gif
I would suggest a another way, see if that helps apart from the split.
1. Make a unload
2. Use a DFSORT to exclude records from it that you wanted to delete using query.
3. Use the file with remaining records to do a load replace.
4. Compare the stats and performance or outage and results of both the approach and use the best.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Dec 18, 2015 10:40 am    Post subject:
Reply with quote

Rohit..still the question will remain unanswered..how to perform commit after every 100 recs if using Delete through JCL icon_biggrin.gif

Also I don't see the approach that feasible if the table has million of rows and one is interested to delete few thousand rows then why to unload million rows and then again load replace ?

May be we are stretching topic too long..so lets OP try and decide the best approach

Regards,
Chandan
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Fri Dec 18, 2015 8:39 pm    Post subject:
Reply with quote

Quote:
Also I don't see the approach that feasible if the table has million of rows and one is interested to delete few thousand rows then why to unload million rows and then again load replace ?

You don't know the count for sure neither I do. However, in my shop every table that goes to production has a unload job created by default and may be he does too have otherwise if TS does mass delete without having a back up then it is a mess as told before.
Quote:
Rohit..still the question will remain unanswered. How to perform commit after every 100 recs if using Delete through JCL

I have seen queries with cursors in a control cards and and a loop statements and with the help of some mod function he can still achieve if he is will to research on that or he can also do delete by partition I guess if there is any.
Let him come back.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1800
Location: UK

PostPosted: Sat Dec 19, 2015 6:08 pm    Post subject:
Reply with quote

It would help if TS stated how the delete was being done! Is it by a series of delete statements - one for each row to be deleted or is it a simle SQL statement such as
Code:
DELETE FROM table WHERE col=xyz;
or
DELETE FROM table WHERE col LIKE 'mask';

Is it being done in an application program or is it by utility - which utility?
A simple commit every n deletes will avoid having to re-delete rows on a restart when doing it via an application program.

But maybe the TS isn't interested as he hasn't come back with any further information.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Dec 21, 2015 9:30 am    Post subject:
Reply with quote

@ Rohit..you mean to say at your shop Unload of prod tables happens every day as a part of Batch cycle?


I have seen these unload jobs created by default as well but we used to run those Jobs on Adhoc basis when we need to copy the Production data to lower region

Anyways agree with Nic..TS seems be not interested as he hasn't come back

Regards,
Chandan
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Mon Dec 21, 2015 9:04 pm    Post subject:
Reply with quote

Quote:
@ Rohit..you mean to say at your shop Unload of prod tables happens every day as a part of Batch cycle?


Yes Sir, We do to support business requests/questions or production tickets as we don't have direct Production DB2 accesses.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Tue Dec 22, 2015 12:55 am    Post subject:
Reply with quote

1.Unload the data you want to present in the table
2.Load replace using the unloaded data
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