|
View previous topic :: View next topic
|
| Author |
Message |
mf_karthik
New User
Joined: 26 Jul 2005
Posts: 55
|
|
|
|
| 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 |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| 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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Chandan,You are right it do nothing. jumbled 
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Rohit..still the question will remain unanswered..how to perform commit after every 100 recs if using Delete through JCL 
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
@ 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
1.Unload the data you want to present in the table
2.Load replace using the unloaded data |
|
| Back to top |
|
|
|
|
