Timeouts / Deadlocks on resource type D01.(DBID, OBID)

Brian Picard

Timeouts / Deadlocks on resource type D01.(DBID, OBID)
Hi,
We seem to have a number of occurrences of timeouts / deadlocks in our
application.
the reason code is C9008E or C90088
type is D01
Name is 362.289(DBID.OBID)

While I understand what this is, I am interested in knowing why this
happens. When does a timeout or deadlock happen with reference to DBID and
OBID?. The application is a Dynamic SQL application. Is it related to the
shortage of space in EDM pool?. How do we fix this?.
Would appreciate any feedback / suggestions.
Brian



Sanjeev (CTS) S

Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)
(in response to Brian Picard)
Hi Brain,

Have you tried looking at this DBID.OBID corresponds to which object ?. I
am sure shortage in the space of EDM pool will not give you deadlock and
timeout. Infact nothing, except the exclusive locks on DBD and
SYSIBM.SYSPLANAUTH and SYSPACKAUTH will cause deadlock and timeout which is
somewhat related to EDM pool ,in that case also no message is given which
refers to EDM pool. And locks in DBD (exclusive) are under specific
conditions.

I feel it is related to the object access and you try finding out the
correlation id of the plans/packages causing the deadlock/ timeout. Look at
the complete message in the *MSTR address space. The solution to the problem
is look at the commit frequency.

HTH

Regards,
Sanjeev

> -----Original Message-----
> From: Brian Picard [SMTP:[login to unmask email]
> Sent: Wednesday, December 20, 2000 9:52 PM
> To: [login to unmask email]
> Subject: Timeouts / Deadlocks on resource type D01.(DBID, OBID)
>
> Hi,
> We seem to have a number of occurrences of timeouts / deadlocks in our
> application.
> the reason code is C9008E or C90088
> type is D01
> Name is 362.289(DBID.OBID)
>
> While I understand what this is, I am interested in knowing why this
> happens. When does a timeout or deadlock happen with reference to DBID and
> OBID?. The application is a Dynamic SQL application. Is it related to the
> shortage of space in EDM pool?. How do we fix this?.
> Would appreciate any feedback / suggestions.
> Brian
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Brian Picard

Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)
(in response to Sanjeev (CTS) S)
Hi Sanj,
The application team is trying to run about 10 concurrent jobs.(same
program and difft plans each using difft qualifier). All the 9 jobs went
down, except job1. Subsequent restarts also failed because job1 had not
completed at that time.
There were no DDL executed at this database at that time.
This is a dynamic application and we have to use the delivered program. We
cannot change the commit frequency as it is next to impossible. It is not a
static program where you can analyze and commit periodically(thats what I
was told when I asked the same question, don't know exactly what the
complexities are, perhaps being able to restart).
This is not a everyday affair. According to the application, these abends
happen at their own wish and will and not necessarily everytime. So I am
involved now to look at the cause at the holiday season being a poor
systems programmer.
By looking at the EDM pool snapshot I see, the available free pages is
close to zero at most of the time and hence i assume approximately this
could have been the case when the abends occured. Almost 99% has been taken
by Dyn SQL Cache. We didnt have history capture turned on unfortunately.
I thought during the mini bind process(Dyn SQL), it is trying to load a DBD
which is not in the pool and failed to fit the dbd block. The DBD in
question needs 61 pages. We run Ver 5.1 and I checked the ZPARM SPRMDF has
been set as NO. But even this guess logic by me doesn't make any sense as
one program was able to succesfully bind(mini bind) and execute so the DBD
must have been in the EDM Pool.
The plan has been granted to public, with zero auth cache. We don't use
packages. So I would rule out PLANAUTH locks.
MSTR information is of no use other than telling which jobs timedout and
what the DBID.OBID table was. It refers to the same table in all the abend
situations.
When would DBDs be locked exclusively in the absence of a DDL?. Even in
this case it should be IX and not exclusive. From the manuals, Dyn SQLs
take a shared lock on the DBD. So timeout or deadlock on a shared lock
doesnt make any sense.
Regards,
Brian



Slot J.P.

Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)
(in response to Brian Picard)
Brian,

Do you you have any utils running at that time, theycan spoil it too..

Kind regards,

Jaap Slot

Phone 0031 (0)30 215 2220
Mobile 0031 (0)6 5374 0167
e-Mail <mailto:[login to unmask email]>

Reserve your AGENDA for next DB2 Users Group Conference IDUG Florence -
8/11 October 2001 - < http://www.idug.org >

No trees were killed in the sending of this message. However - a large
number of electrons were terribly inconvenienced.



-----Oorspronkelijk bericht-----
Van: Brian Picard [mailto:[login to unmask email]
Verzonden: donderdag 21 december 2000 15:33
Aan: [login to unmask email]
Onderwerp: Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)


Hi Sanj,
The application team is trying to run about 10 concurrent jobs.(same
program and difft plans each using difft qualifier). All the 9 jobs went
down, except job1. Subsequent restarts also failed because job1 had not
completed at that time.
There were no DDL executed at this database at that time.
This is a dynamic application and we have to use the delivered program. We
cannot change the commit frequency as it is next to impossible. It is not a
static program where you can analyze and commit periodically(thats what I
was told when I asked the same question, don't know exactly what the
complexities are, perhaps being able to restart).
This is not a everyday affair. According to the application, these abends
happen at their own wish and will and not necessarily everytime. So I am
involved now to look at the cause at the holiday season being a poor
systems programmer.
By looking at the EDM pool snapshot I see, the available free pages is
close to zero at most of the time and hence i assume approximately this
could have been the case when the abends occured. Almost 99% has been taken
by Dyn SQL Cache. We didnt have history capture turned on unfortunately.
I thought during the mini bind process(Dyn SQL), it is trying to load a DBD
which is not in the pool and failed to fit the dbd block. The DBD in
question needs 61 pages. We run Ver 5.1 and I checked the ZPARM SPRMDF has
been set as NO. But even this guess logic by me doesn't make any sense as
one program was able to succesfully bind(mini bind) and execute so the DBD
must have been in the EDM Pool.
The plan has been granted to public, with zero auth cache. We don't use
packages. So I would rule out PLANAUTH locks.
MSTR information is of no use other than telling which jobs timedout and
what the DBID.OBID table was. It refers to the same table in all the abend
situations.
When would DBDs be locked exclusively in the absence of a DDL?. Even in
this case it should be IX and not exclusive. From the manuals, Dyn SQLs
take a shared lock on the DBD. So timeout or deadlock on a shared lock
doesnt make any sense.
Regards,
Brian







De informatie opgenomen in dit bericht kan vertrouwelijk zijn en
is uitsluitend bestemd voor de geadresseerde. Indien u dit bericht
onterecht ontvangt, wordt u verzocht de inhoud niet te gebruiken en
de afzender direct te informeren door het bericht te retourneren.

The information contained in this message may be confidential
and is intended to be exclusively for the addressee. Should you
receive this message unintentionally, please do not use the contents
herein and notify the sender immediately by return e-mail.



Brian Picard

Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)
(in response to Slot J.P.)
No I don' think so.
What type of utils, you are so specific about Slot?. Not any COPY, REORG or
RECOVER etc were running at that time. The other stand alone utils, no body
runs without involving sys administrators first.
Brian...



Siegfried Lindhoff

Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)
(in response to Brian Picard)
Hi,

when we saw it the OBID always matched to a table in a segmented
tablespace and the
timeout was caused by a lock escalation.

kind regards
Siegfried Lindhoff





DB2 Data Base Discussion List <[login to unmask email]> on 21.12.2000 15:33:25

Bitte antworten an DB2 Data Base Discussion List <[login to unmask email]>

An: [login to unmask email]
Kopie:
Thema: Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)



Hi Sanj,
The application team is trying to run about 10 concurrent jobs.(same
program and difft plans each using difft qualifier). All the 9 jobs went
down, except job1. Subsequent restarts also failed because job1 had not
completed at that time.
There were no DDL executed at this database at that time.
This is a dynamic application and we have to use the delivered program. We
cannot change the commit frequency as it is next to impossible. It is not a
static program where you can analyze and commit periodically(thats what I
was told when I asked the same question, don't know exactly what the
complexities are, perhaps being able to restart).
This is not a everyday affair. According to the application, these abends
happen at their own wish and will and not necessarily everytime. So I am
involved now to look at the cause at the holiday season being a poor
systems programmer.
By looking at the EDM pool snapshot I see, the available free pages is
close to zero at most of the time and hence i assume approximately this
could have been the case when the abends occured. Almost 99% has been taken
by Dyn SQL Cache. We didnt have history capture turned on unfortunately.
I thought during the mini bind process(Dyn SQL), it is trying to load a DBD
which is not in the pool and failed to fit the dbd block. The DBD in
question needs 61 pages. We run Ver 5.1 and I checked the ZPARM SPRMDF has
been set as NO. But even this guess logic by me doesn't make any sense as
one program was able to succesfully bind(mini bind) and execute so the DBD
must have been in the EDM Pool.
The plan has been granted to public, with zero auth cache. We don't use
packages. So I would rule out PLANAUTH locks.
MSTR information is of no use other than telling which jobs timedout and
what the DBID.OBID table was. It refers to the same table in all the abend
situations.
When would DBDs be locked exclusively in the absence of a DDL?. Even in
this case it should be IX and not exclusive. From the manuals, Dyn SQLs
take a shared lock on the DBD. So timeout or deadlock on a shared lock
doesnt make any sense.
Regards,
Brian








Mark Harmon

Re: Timeouts / Deadlocks on resource type D01.(DBID, OBID)
(in response to Siegfried Lindhoff)
About commit frequency: Do you have an DB2 Restart product? At least two of
these products (maybe more) can force DB2 commits into programs that do not
take them often enough. They can also provide automatic retry of some
errors. Depending on the product, this might be done without source code
changes. The product I am familiar with is Smart/Restart at www.relarc.com
but there are others on the market. If you have a DB2 restart product
peruse the manual for it.