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: 1610
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: 331
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: 1610
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: 1610
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: 1715
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: 1610
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: 1529
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 JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
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 How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


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