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 Contention with same PLAN for partitioned table spaces.

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

New User


Joined: 21 Feb 2011
Posts: 8
Location: Chennai

PostPosted: Thu Jun 30, 2011 5:43 pm    Post subject: DB2 Contention with same PLAN for partitioned table spaces.
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 30, 2011 9:30 pm    Post subject:
Reply with quote

Hello,

Do you understand what a deadlock really is?

You need to change the sequence in which rows are locked.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat Jul 02, 2011 2:25 pm    Post subject:
Reply with quote

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
View user's profile Send private message
prabu.sivaram

New User


Joined: 21 Feb 2011
Posts: 8
Location: Chennai

PostPosted: Thu Jul 07, 2011 6:00 pm    Post subject: Reply to: DB2 Contention with same PLAN for partitioned tabl
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jul 07, 2011 6:25 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jul 07, 2011 7:23 pm    Post subject:
Reply with quote

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
View user's profile Send private message
prabu.sivaram

New User


Joined: 21 Feb 2011
Posts: 8
Location: Chennai

PostPosted: Thu Jul 07, 2011 8:24 pm    Post subject: Reply to: DB2 Contention with same PLAN for partitioned tabl
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jul 07, 2011 9:08 pm    Post subject:
Reply with quote

sort it in the correct order and create you different files based on the new sort order, not the old.
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: Thu Jul 07, 2011 9:36 pm    Post subject:
Reply with quote

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. . . icon_confused.gif The approach to the updates is wrong and will have to be changed.
Back to top
View user's profile Send private message
prabu.sivaram

New User


Joined: 21 Feb 2011
Posts: 8
Location: Chennai

PostPosted: Fri Jul 08, 2011 4:56 pm    Post subject: Reply to: DB2 Contention with same PLAN for partitioned tabl
Reply with quote

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('icon_sad.gif')..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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 08, 2011 7:36 pm    Post subject:
Reply with quote

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
View user's profile Send private message
prabu.sivaram

New User


Joined: 21 Feb 2011
Posts: 8
Location: Chennai

PostPosted: Mon Jul 11, 2011 12:19 pm    Post subject: Reply to: DB2 Contention with same PLAN for partitioned tabl
Reply with quote

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Mon Jul 11, 2011 2:31 pm    Post subject: Re: Reply to: DB2 Contention with same PLAN for partitioned
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jul 11, 2011 7:46 pm    Post subject:
Reply with quote

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
View user's profile Send private message
prabu.sivaram

New User


Joined: 21 Feb 2011
Posts: 8
Location: Chennai

PostPosted: Mon Jul 18, 2011 7:03 pm    Post subject: Reply to: DB2 Contention with same PLAN for partitioned tabl
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jul 18, 2011 7:26 pm    Post subject:
Reply with quote

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
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 To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Removing Extra Spaces in a comma sepa... Puspojit DFSORT/ICETOOL 3 Tue Sep 13, 2016 7:46 am
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm


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