Lock on TEMP tablespace

Ava Collins

Lock on TEMP tablespace
Philip,

Did you find a resolution to this problem? We're experiencing the same symptom. I'm pasting in your original email.

Jacquie

Original email......

Once in a while we are getting a database lock on TEMP database by some
client application. Once this happen nobody can create any declare global
temporary tables. This cause most of our application to go down since we use
lot of TEMP tables . Let me know if anybody has experienced this.

This is what I see in the DB2 master log.

DSNT501I :DB2P DSNILMCL RESOURCE UNAVAILABLE
[login to unmask email]_
CONNECTION-ID=SERVER
LUW-ID=AC1F204E.G8F9.B872C08E65EF=127877
REASON 00C9008E
TYPE 00000100
NAME TMDPROD --- Temporary database

When will a program take a TEMP database lock? If it takes, when will it
release the lock.

We are on DB2 V6.1 on OS/390 2.9



Sibimon Philip

Re: Lock on TEMP tablespace
(in response to Ava Collins)
No, we have not found any solution. Since I cannot open a ticket directly to
IBM, We did two thing

1. Reduced thread timeout value to 10 minutes instead of 1 hour.
2. set TCPKPALV to 630 seconds. All the time when this happened I could not
find the offending process in NT side, so this may take care that by
dropping such connection from DB2.

Any way for 10 minutes nothing will work unless somebody manually kill the
thread. Initially when we killed the thread, DB2 abended and then we applied
some PTF and that is not happening now.

I do not know why DB2 taking a lock for temp tables on database.

thanks..sibi


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Tuesday, December 10, 2002 01:34 PM
To: [login to unmask email]
Subject: Lock on TEMP tablespace


Philip,

Did you find a resolution to this problem? We're experiencing the same
symptom. I'm pasting in your original email.

Jacquie

Original email......

Once in a while we are getting a database lock on TEMP database by some
client application. Once this happen nobody can create any declare global
temporary tables. This cause most of our application to go down since we use
lot of TEMP tables . Let me know if anybody has experienced this.

This is what I see in the DB2 master log.

DSNT501I :DB2P DSNILMCL RESOURCE UNAVAILABLE
[login to unmask email]_
CONNECTION-ID=SERVER
LUW-ID=AC1F204E.G8F9.B872C08E65EF=127877
REASON 00C9008E
TYPE 00000100
NAME TMDPROD --- Temporary database

When will a program take a TEMP database lock? If it takes, when will it
release the lock.

We are on DB2 V6.1 on OS/390 2.9








Myron Miller

Re: Lock on TEMP tablespace
(in response to Sibimon Philip)
I have a PMR open with IBM about this problem: 52594,344

They've suggested PQ53714 for V7 as a fix to the problem (nothing for V6).

One application solution was to have the application drop the temp table after
it was thru with the data. Then the threads went away. Closing the result set
and closing the connection didn't work. Only until we also did a DROP TEMP
TABLE did we see the threads go away.


--- "Philip, Sibimon" <[login to unmask email]> wrote:
> No, we have not found any solution. Since I cannot open a ticket directly to
> IBM, We did two thing
>
> 1. Reduced thread timeout value to 10 minutes instead of 1 hour.
> 2. set TCPKPALV to 630 seconds. All the time when this happened I could not
> find the offending process in NT side, so this may take care that by
> dropping such connection from DB2.
>
> Any way for 10 minutes nothing will work unless somebody manually kill the
> thread. Initially when we killed the thread, DB2 abended and then we applied
> some PTF and that is not happening now.
>
> I do not know why DB2 taking a lock for temp tables on database.
>
> thanks..sibi
>
>
> -----Original Message-----
> From: [login to unmask email] [mailto:[login to unmask email]
> Sent: Tuesday, December 10, 2002 01:34 PM
> To: [login to unmask email]
> Subject: Lock on TEMP tablespace
>
>
> Philip,
>
> Did you find a resolution to this problem? We're experiencing the same
> symptom. I'm pasting in your original email.
>
> Jacquie
>
> Original email......
>
> Once in a while we are getting a database lock on TEMP database by some
> client application. Once this happen nobody can create any declare global
> temporary tables. This cause most of our application to go down since we use
> lot of TEMP tables . Let me know if anybody has experienced this.
>
> This is what I see in the DB2 master log.
>
> DSNT501I :DB2P DSNILMCL RESOURCE UNAVAILABLE
> [login to unmask email]_
> CONNECTION-ID=SERVER
> LUW-ID=AC1F204E.G8F9.B872C08E65EF=127877
> REASON 00C9008E
> TYPE 00000100
> NAME TMDPROD --- Temporary database
>
> When will a program take a TEMP database lock? If it takes, when will it
> release the lock.
>
> We are on DB2 V6.1 on OS/390 2.9
>
>
>
>
>
>
>
>
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



James Campbell

Re: Lock on TEMP tablespace
(in response to Myron Miller)
Do PQ56779/UQ62339 and PQ54135/UQ60778 help?

James Campbell


On Wed, 18 Dec 2002 11:39:29 -0800, Myron Miller <[login to unmask email]>
wrote:

>I have a PMR open with IBM about this problem: 52594,344
>
>They've suggested PQ53714 for V7 as a fix to the problem (nothing for V6).
<rest snipped>



Myron Miller

Re: Lock on TEMP tablespace
(in response to James Campbell)
It might. But they imply that commits are required. In many cases actual
commit statements are not issued. Just the close result set, close cursor,
close connection. So without a physical commit being issued and some testing
I'm not sure.

We have the equivalents on our V7 system and I'll be testing that sometime next
week.
--- James Campbell <[login to unmask email]> wrote:
> Do PQ56779/UQ62339 and PQ54135/UQ60778 help?
>
> James Campbell
>
>
> On Wed, 18 Dec 2002 11:39:29 -0800, Myron Miller <[login to unmask email]>
> wrote:
>
> >I have a PMR open with IBM about this problem: 52594,344
> >
> >They've suggested PQ53714 for V7 as a fix to the problem (nothing for V6).
> <rest snipped>
>
>
>
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



Ava Collins

Re: Lock on TEMP tablespace
(in response to Myron Miller)
myronwmiller writes:

> <snip>They've suggested PQ53714 for V7 as a fix to the problem (nothing for
> V6).
>
> One application solution was to have the application drop the temp table
> after
> it was thru with the data. Then the threads went away. Closing the result
> set
> and closing the connection didn't work. Only until we also did a DROP TEMP
> TABLE did we see the threads go away.

Myron,

We've have had PQ53714 for V7 applied since August. When you say "DROP TEMP
TABLE", do you mean the instance's DTT tables or the whole TEMP table? Our
application is dropping the instance of the DTT table before it creates it,
and it is dropping the remote DTT's when all data from it's open cursor has
been processed.

We are verifying that all our stored procedures have RELEASE(COMMIT) and
KEEPDYNAMIC(NO). The occurrences of the lock on the TEMP tablespace have
decreased since then. Also the calling application issues a COMMIT at the
end of every unit of work. Time will tell if that is a solution, but keep
this LISTSERV posted about the results of the PMR.

Thanks!

Jacquie

Sibimon Philip

Re: Lock on TEMP tablespace
(in response to Ava Collins)
Dropping temp table instance is not an option in our case since most of the
stored procedures open the return result set on temp table. So if we drop
the temp table before returning the control to client/server, no result will
come back to the client.

Sibi


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, December 19, 2002 08:33 PM
To: [login to unmask email]
Subject: Re: Lock on TEMP tablespace


myronwmiller writes:



<snip>They've suggested PQ53714 for V7 as a fix to the problem (nothing for
V6).

One application solution was to have the application drop the temp table
after
it was thru with the data. Then the threads went away. Closing the result
set
and closing the connection didn't work. Only until we also did a DROP TEMP
TABLE did we see the threads go away.



Myron,

We've have had PQ53714 for V7 applied since August. When you say "DROP TEMP
TABLE", do you mean the instance's DTT tables or the whole TEMP table? Our
application is dropping the instance of the DTT table before it creates it,
and it is dropping the remote DTT's when all data from it's open cursor has
been processed.

We are verifying that all our stored procedures have RELEASE(COMMIT) and
KEEPDYNAMIC(NO). The occurrences of the lock on the TEMP tablespace have
decreased since then. Also the calling application issues a COMMIT at the
end of every unit of work. Time will tell if that is a solution, but keep
this LISTSERV posted about the results of the PMR.

Thanks!

Jacquie

Myron Miller

Re: Lock on TEMP tablespace
(in response to Sibimon Philip)
We create the Temp tables as Declare so they drop the actual table. DROP TABLE
command. Issuing commits causes application problems. I'll let you know what
happens towards the end of next week.

Myron
--- [login to unmask email] wrote:
> myronwmiller writes:
>
> > <snip>They've suggested PQ53714 for V7 as a fix to the problem (nothing for
> > V6).
> >
> > One application solution was to have the application drop the temp table
> > after
> > it was thru with the data. Then the threads went away. Closing the result
> > set
> > and closing the connection didn't work. Only until we also did a DROP TEMP
> > TABLE did we see the threads go away.
>
> Myron,
>
> We've have had PQ53714 for V7 applied since August. When you say "DROP TEMP
> TABLE", do you mean the instance's DTT tables or the whole TEMP table? Our
> application is dropping the instance of the DTT table before it creates it,
> and it is dropping the remote DTT's when all data from it's open cursor has
> been processed.
>
> We are verifying that all our stored procedures have RELEASE(COMMIT) and
> KEEPDYNAMIC(NO). The occurrences of the lock on the TEMP tablespace have
> decreased since then. Also the calling application issues a COMMIT at the
> end of every unit of work. Time will tell if that is a solution, but keep
> this LISTSERV posted about the results of the PMR.
>
> Thanks!
>
> Jacquie
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



Myron Miller

Re: Lock on TEMP tablespace
(in response to Myron Miller)
That's true. We have the client app drop the temp table rather than the stored
procedure since our situation is exactly like yours. So far its the only way
we've been able to get rid of the locks on the temp table.


--- "Philip, Sibimon" <[login to unmask email]> wrote:
> Dropping temp table instance is not an option in our case since most of the
> stored procedures open the return result set on temp table. So if we drop
> the temp table before returning the control to client/server, no result will
> come back to the client.
>
> Sibi
>
>
> -----Original Message-----
> From: [login to unmask email] [mailto:[login to unmask email]
> Sent: Thursday, December 19, 2002 08:33 PM
> To: [login to unmask email]
> Subject: Re: Lock on TEMP tablespace
>
>
> myronwmiller writes:
>
>
>
> <snip>They've suggested PQ53714 for V7 as a fix to the problem (nothing for
> V6).
>
> One application solution was to have the application drop the temp table
> after
> it was thru with the data. Then the threads went away. Closing the result
> set
> and closing the connection didn't work. Only until we also did a DROP TEMP
> TABLE did we see the threads go away.
>
>
>
> Myron,
>
> We've have had PQ53714 for V7 applied since August. When you say "DROP TEMP
> TABLE", do you mean the instance's DTT tables or the whole TEMP table? Our
> application is dropping the instance of the DTT table before it creates it,
> and it is dropping the remote DTT's when all data from it's open cursor has
> been processed.
>
> We are verifying that all our stored procedures have RELEASE(COMMIT) and
> KEEPDYNAMIC(NO). The occurrences of the lock on the TEMP tablespace have
> decreased since then. Also the calling application issues a COMMIT at the
> end of every unit of work. Time will tell if that is a solution, but keep
> this LISTSERV posted about the results of the PMR.
>
> Thanks!
>
> Jacquie
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



Ava Collins

Re: Lock on TEMP tablespace
(in response to Myron Miller)
Sibi writes:

> "<snip> if we drop the temp table before returning the control to
> client/server, no result will come back to the client.<snip>"

Sibi,
Yes, we're in that same situation. We're just dropping and declaring it
again at the beginning of the next call of that SP.

We will both be interested in what Myron hears from IBM!

Jacquie