|
View previous topic :: View next topic
|
| Author |
Message |
prabu.sivaram
New User
Joined: 21 Feb 2011
Posts: 8
Location: Chennai
|
|
|
|
HI All,
can anybody help me please for the following query.
I have a DB2 table that is partitioned into 26 table spaces.
We are running the job in 9 partitions based on our key range.
Ex:
AAA to DF5 --> 1st partition.
DF6 to HB4 --> 2nd partition.
.
.
.
.
123 to 999 --> 9th partition.
we are getting the deadlock issue very frequently for the above run.
We are using IMS checkpoint logic and We have kept the check point frequency as 500 for all 9 jobs.so it that commits for every 500 records.
I will really appreciate if we get prompted reply as this is one of the new issue for this forum and i am also new for this forum.
Please find below the error details that was captured in our D2PCMSTR.
00.08.39 STC25219 DSNT375I -D2PC PLAN=OM0P0037 WITH 176
176 CORRELATION-ID=0006OM0P0037
176 CONNECTION-ID=IMP2
176 LUW-ID=PLANO1.AP1BDBPC.C7FE21858FC8=102852
176 THREAD-INFO=GMOMM0B:*:*:*
176 IS DEADLOCKED WITH PLAN=OM0P0037 WITH
176 CORRELATION-ID=0001OM0P0037
176 CONNECTION-ID=IMP2
176 LUW-ID=PLANO1.AP1BDBPC.C7FE2185F849=102853
176 THREAD-INFO=GMOMM0B:*:*:*
176 ON MEMBER D2PC
00.08.39 STC25219 DSNT501I -D2PC DSNILMCL RESOURCE UNAVAILABLE 180
180 CORRELATION-ID=0006OM0P0037
180 CONNECTION-ID=IMP2
180 LUW-ID=PLANO1.AP1BDBPC.C7FE21858FC8=102852
180 REASON 00C90088
180 TYPE 00000302
180 NAME GMOMM011.S001 .X'B00589'
08.40 STC25219 DSN3201I -D2PC ABNORMAL EOT IN PROGRESS FOR 188
188 USER=GMOMM0B CONNECTION-ID=IMP2 CORRELATION-ID=0006OM0P0037
188 JOBNAME=GMOMM01K ASID=0031 TCB=008CCA08 |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Do you understand what a deadlock really is?
You need to change the sequence in which rows are locked. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Prabu,
Welcome to IBMMAINFRAMES!
| Quote: |
| We are running the job in 9 partitions based on our key range. |
9 Jobs, 9 Partitions. So, is it like 1 job per partition meaning that particular job will access only that partition. If so, your jobs are accessing data from different partitions. You need to check partition key ranges for partitions and compare it with the values used in each program/query in each job.
Thanks,
Sushanth |
|
| Back to top |
|
|
prabu.sivaram
New User
Joined: 21 Feb 2011
Posts: 8
Location: Chennai
|
|
|
|
Hi Dick and Sushanth,
Thank you very much for prompted reply....
I remember one small change that we did for this 9 jobs after looking response from dick as 'You need to change the sequence in which rows are locked.'
Basically Our table is arranged based on primary keys
1.)order number (First 3 digits will be alphanumeric and last 3 digits will be key range) and
2.)key range (AAA -- 999)....
we have one foreign key called Veh Identification number in the same table and it is of 16 digits. we have sorted the input (which we extracted based on key range AAA.....999) based on this field and then inputting to the 9 jobs.
Does this cause IMS deadlock...?
It will be better if you let me know the significance of this
REASON 00C90088 and how to interpret the D2PCMSTR info... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| It will be better if you let me know the significance of this |
in the event that if I take any action, I can blame it on this board,
and not take responsibility for determining the problem,
or taking any responsibility to create a solution.
THIS BOARD HAS BECOME A KINDERGARTEN!! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You need to post your understanding of what creates a deadlock condition.
If you are unwilling to learn how this happens, you very likely wil be unable to fix it. . . |
|
| Back to top |
|
|
prabu.sivaram
New User
Joined: 21 Feb 2011
Posts: 8
Location: Chennai
|
|
|
|
Hello,
I agree with your suggession...i am really sorry for that since i am new to this forum..
Here is the detailed issue..
The program contains IMS / DB2 scripts.We are using IMS GSAM file for both input and output in all the 9 jobs...I have given in earlier response how this input is being prepared..I understand this is purely because of IMS...
Here the row lock happens randomly as the input is not in the sorted order...
I need the technical details to explain the same to my team then only it will get approved and i can proceed further.. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| sort it in the correct order and create you different files based on the new sort order, not the old. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I understand this is purely because of IMS... |
I suspect this is not quite accurate. . .
| Quote: |
| I need the technical details to explain the same to my team then only it will get approved and i can proceed further.. |
What might be approved. . .  The approach to the updates is wrong and will have to be changed. |
|
| Back to top |
|
|
prabu.sivaram
New User
Joined: 21 Feb 2011
Posts: 8
Location: Chennai
|
|
|
|
Hello Dick,
Initially we had a check point frequency as 1000 for all 9 jobs and there were no DB2 update statement present. That is how it was running for the past 10 years without any contention.
But recently we introduced the one DB2 update statement and then reduced the checkpoint frequency as 500. This job runs only on weekdays. Even with this changed logic it is not abending in a daily basis.
It abends randomnly irrespective of any day..javascript:emoticon(' ')..Is there any threshold in terms of checkpoint frequency or table spaces parameters to use this IMS as well as DB2 logic in the program..
| Quote: |
sort it in the correct order and create you different files based on the new sort order, not the old. |
The is a constraint in keeping the new input in sorted order..It is sorted based on the fields Model_year(2007,2008,2009,......),Veh Identification number....We are changing the package set schema for every change in the model year..In this way we will lose the efficiency if we sort it based on the primary key veh_order number..
Keeping the input with this sorted format A is better than Format B..
A:
Only three time package set schema is getting changed..
2007
2007
2008
2008
2009
2009
B:
We need to change it 6 times for the following format..
2007
2008
2007
2009
2008
2007 |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
I suggest that it is more important that it run correctly every time than to run a bit more quickly with occasional abends. . .
That this ran for years one way and now (with changes) has problems tells nothing other than the changes are the culprit.
| Quote: |
| Even with this changed logic it is not abending in a daily basis |
Why does this matter?
Until the code is changed to lock rows in the same order, a deadlock can occur. . . I suspect you also have not done the research about how a deadlock happens. . . |
|
| Back to top |
|
|
prabu.sivaram
New User
Joined: 21 Feb 2011
Posts: 8
Location: Chennai
|
|
|
|
Hi,
| Quote: |
| I suspect you also have not done the research about how a deadlock happens. . . |
The sysdump is also not getting created during deadlock that is the reason i am not able to step forward to research on this deadlock.. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| prabu.sivaram wrote: |
Hi,
| Quote: |
| I suspect you also have not done the research about how a deadlock happens. . . |
The sysdump is also not getting created during deadlock that is the reason i am not able to step forward to research on this deadlock.. |
I don't think Dick is asking about your specific example.
Do you know how "a deadlock" comes about, can be created (accidently) by someone's code, happens. Look it up in the manual. If you have a deadlock, the code you (collectively, at your site) have written has brought it about. Understand the manual. If necessary, sit down with a friendly DBA, and if still unclear explain here what you do not understand. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| The sysdump is also not getting created during deadlock that is the reason i am not able to step forward to research on this deadlock.. |
Sorry, but there is most likely no reason to even look for/at a sysudump. . .
As Bill posted, you need to understand how a deadlock occurs and then identify the code design that causes the problem. What has been done to cause the problem is most likely rather simple. Fixing it may not be as it will most likely require some redesign.
Which should not be undertaken until you understand the cause and how to prevent. |
|
| Back to top |
|
|
prabu.sivaram
New User
Joined: 21 Feb 2011
Posts: 8
Location: Chennai
|
|
|
|
Hi,
We introduced a new update query in this table S001. (Before this change there were no contention and there were no update statement present)
| Quote: |
| 180 NAME GMOMM011.S001 .X'B00589' |
This table space is partitioned into 26 partitions. The deadlock is
occurring when two resources (PLAN) are accessing this same table..But we
didn't even have the same primary key in all our 9 partition jobs.I mean that we are updating different rows at any point of time..
Following is our table space definition...
--------------------------------------------------
BUFFERPOOL BP0
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
CCSID EBCDIC
--------------------------------------------------
when i approached the DBA they were suggesting this is bcoz of Page Lock...Can anybody help me please your views..... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Many (most) db2 installations use page locking - because that is the way the product is shown many places.
Most of us senior database people (long before db2) knew that page level locking can kill a system. Depending on things you should discuss with your dba, you could consider row-level locking.
Even with row-level locking, the deadlock can occur if the rows are locked "out of order" to prevent the deadlock. |
|
| Back to top |
|
|
|
|
