Thread timeout issue

Robert Knight

Thread timeout issue

The following came from a server area technician.

I am seeing a large number of database connection timeouts in the application log.
This seems to happen when the portal can't get a DB2 connection for queries from a server.

SQL Exception caught while obtaining a connection to the {xxx} data source.
No managed connections available within configured blocking timeout (30000 ms) nested throwable.

Right now they have a maximum of 20 DB2 connections configured in the portal and they are requesting an increase in the DB2 side of which I don't agree.


In looking at the zparm (V8) all the system defaults were applied when going to V8.

Condbat = 10000
Maxdbat = 200
Cthread = 200


Any suggestions or recommendations.

Bob Knight











________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Myron Miller

Re: Thread timeout issue
(in response to Robert Knight)
Robert,
My first question would be how many of these threads are you seeing active on
DB2? And what is your inactive thread timeout parm (THDINACT) on DB2?

I'd really try to analyze and see how long the threads are staying active on
DB2. And whether they are actually doing work there or just staying connected
doing nothing. If the latter, then there's a problem on the server side that
the applications need to address. If they are truly active doing DB2 calls,
then there is enough volume to increase their number of connections on the
server side to 30-50. But they should have some type of much smaller number to
reduce to if the activity goes down. They should not be holding 30-50 threads
all the time while inactive.

Typical I see is min of 5-10 and max of 25-40. One system we have has min of 10
and max of 50 (but it's really really active at some points during the day.)
But other times, they shrink the pool back to a minimal level.

If the threads are Java threads, i'd really carefully check the code and ensure
that the connection is properly being closed. If the Java code opens a
connection, then a statement and then a result set, these must all be closed and
in the reverse order, i.e. result closed then the statement, then the
connection. Not doing it this way will cause the connection to be kept open in
DB2 connect and DB2, which will tie up connections in the pool incorrectly. I
can't count the number of times, I've seen a connection open and a result set
and then an error happens and the Java code goes to a try-finally section where
the connection is closed but the result set was not, with the result that the
connection is left open on DB2.

Your overall system numbers seem pretty fine to me and don't need to be
increased.

Just some suggestions from my point of view.

Myron



________________________________
From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 11:39:26 AM
Subject: [DB2-L] Thread timeout issue



The following came from a server area technician.

I am seeing a large number of database connection timeouts in the application
log.

This seems to happen when the portal can’t get a DB2 connection for queries from
a server.


SQL Exception caught while obtaining a connection to the {xxx} data source.
No managed connections available within configured blocking timeout (30000 ms)
nested throwable.


Right now they have a maximum of 20 DB2 connections configured in the portal and
they are requesting an increase in the DB2 side of which I don’t agree.



In looking at the zparm (V8) all the system defaults were applied when going to
V8.


Condbat = 10000
Maxdbat = 200
Cthread = 200


Any suggestions or recommendations.

Bob Knight










________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Robert Knight

Re: Thread timeout issue
(in response to Myron Miller)

Thanks for the response

And what is your inactive thread timeout parm (THDINACT) on DB2?

* can't find this parameter

But IDTHTOIN=1800, is high

After a look at the display thread they are executing JAVA and I found 3 threads that display as DISCONN.

This is a good place to start.

Bob

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, January 26, 2011 12:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Thread timeout issue

Robert,
My first question would be how many of these threads are you seeing active on DB2? And what is your inactive thread timeout parm (THDINACT) on DB2?

I'd really try to analyze and see how long the threads are staying active on DB2. And whether they are actually doing work there or just staying connected doing nothing. If the latter, then there's a problem on the server side that the applications need to address. If they are truly active doing DB2 calls, then there is enough volume to increase their number of connections on the server side to 30-50. But they should have some type of much smaller number to reduce to if the activity goes down. They should not be holding 30-50 threads all the time while inactive.

Typical I see is min of 5-10 and max of 25-40. One system we have has min of 10 and max of 50 (but it's really really active at some points during the day.) But other times, they shrink the pool back to a minimal level.

If the threads are Java threads, i'd really carefully check the code and ensure that the connection is properly being closed. If the Java code opens a connection, then a statement and then a result set, these must all be closed and in the reverse order, i.e. result closed then the statement, then the connection. Not doing it this way will cause the connection to be kept open in DB2 connect and DB2, which will tie up connections in the pool incorrectly. I can't count the number of times, I've seen a connection open and a result set and then an error happens and the Java code goes to a try-finally section where the connection is closed but the result set was not, with the result that the connection is left open on DB2.

Your overall system numbers seem pretty fine to me and don't need to be increased.

Just some suggestions from my point of view.

Myron

________________________________
From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 11:39:26 AM
Subject: [DB2-L] Thread timeout issue

The following came from a server area technician.

I am seeing a large number of database connection timeouts in the application log.
This seems to happen when the portal can't get a DB2 connection for queries from a server.

SQL Exception caught while obtaining a connection to the {xxx} data source.
No managed connections available within configured blocking timeout (30000 ms) nested throwable.

Right now they have a maximum of 20 DB2 connections configured in the portal and they are requesting an increase in the DB2 side of which I don't agree.


In looking at the zparm (V8) all the system defaults were applied when going to V8.

Condbat = 10000
Maxdbat = 200
Cthread = 200


Any suggestions or recommendations.

Bob Knight











________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Myron Miller

Re: Thread timeout issue
(in response to Robert Knight)
You're right. My memory is very bad (senior moments!!!!)

We use Idle Thread Timeout (Seconds)..............(IDTHTOIN).. 3600 .
So I doubt I could say yours is high. I don't like it this much but we have
users with Access and Excell that need this long as well as some Cognos and
Informatica threads that actually have well over 1/2 hour between DB2 calls.

Do you show any threads executing anything that are showing but are really
inactive? I typically can find some like this that have sat there for 10-20
minutes. And if they are JAVA, I know we have an issue.

NAME ST A
SERVER RA *


Myron




________________________________
From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 12:52:30 PM
Subject: Re: [DB2-L] Thread timeout issue



Thanks for the response

And what is your inactive thread timeout parm (THDINACT) on DB2?
* can’t find this parameter

But IDTHTOIN=1800, is high

After a look at the display thread they are executing JAVA and I found 3 threads
that display as DISCONN.


This is a good place to start.

Bob


________________________________

From:IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, January 26, 2011 12:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Thread timeout issue

Robert,
My first question would be how many of these threads are you seeing active on
DB2? And what is your inactive thread timeout parm (THDINACT) on DB2?

I'd really try to analyze and see how long the threads are staying active on
DB2. And whether they are actually doing work there or just staying connected
doing nothing. If the latter, then there's a problem on the server side that
the applications need to address. If they are truly active doing DB2 calls,
then there is enough volume to increase their number of connections on the
server side to 30-50. But they should have some type of much smaller number to
reduce to if the activity goes down. They should not be holding 30-50 threads
all the time while inactive.

Typical I see is min of 5-10 and max of 25-40. One system we have has min of 10
and max of 50 (but it's really really active at some points during the day.)
But other times, they shrink the pool back to a minimal level.

If the threads are Java threads, i'd really carefully check the code and ensure
that the connection is properly being closed. If the Java code opens a
connection, then a statement and then a result set, these must all be closed and
in the reverse order, i.e. result closed then the statement, then the
connection. Not doing it this way will cause the connection to be kept open in
DB2 connect and DB2, which will tie up connections in the pool incorrectly. I
can't count the number of times, I've seen a connection open and a result set
and then an error happens and the Java code goes to a try-finally section where
the connection is closed but the result set was not, with the result that the
connection is left open on DB2.

Your overall system numbers seem pretty fine to me and don't need to be
increased.

Just some suggestions from my point of view.

Myron


________________________________

From:Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 11:39:26 AM
Subject: [DB2-L] Thread timeout issue

The following came from a server area technician.

I am seeing a large number of database connection timeouts in the application
log.

This seems to happen when the portal can’t get a DB2 connection for queries from
a server.


SQL Exception caught while obtaining a connection to the {xxx} data source.
No managed connections available within configured blocking timeout (30000 ms)
nested throwable.


Right now they have a maximum of 20 DB2 connections configured in the portal and
they are requesting an increase in the DB2 side of which I don’t agree.



In looking at the zparm (V8) all the system defaults were applied when going to
V8.


Condbat = 10000
Maxdbat = 200
Cthread = 200


Any suggestions or recommendations.

Bob Knight












________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.



________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Robert Knight

Re: Thread timeout issue
(in response to Myron Miller)
The last display did not show any inactive threads.

I am leaning on them to increase the number of threads on their portal side and see if this relieves the problem.

Thanks for responding it's greatly appreciated.

As for senior moments my retirement date is 12/1/12 if the Mayans are correct I won't get my first SS check........

Bob

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, January 26, 2011 1:32 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Thread timeout issue

You're right. My memory is very bad (senior moments!!!!)

We use Idle Thread Timeout (Seconds)..............(IDTHTOIN).. 3600 . So I doubt I could say yours is high. I don't like it this much but we have users with Access and Excell that need this long as well as some Cognos and Informatica threads that actually have well over 1/2 hour between DB2 calls.

Do you show any threads executing anything that are showing but are really inactive? I typically can find some like this that have sat there for 10-20 minutes. And if they are JAVA, I know we have an issue.

NAME ST A
SERVER RA *


Myron


________________________________
From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 12:52:30 PM
Subject: Re: [DB2-L] Thread timeout issue

Thanks for the response

And what is your inactive thread timeout parm (THDINACT) on DB2?

* can't find this parameter

But IDTHTOIN=1800, is high

After a look at the display thread they are executing JAVA and I found 3 threads that display as DISCONN.

This is a good place to start.

Bob

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, January 26, 2011 12:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Thread timeout issue

Robert,
My first question would be how many of these threads are you seeing active on DB2? And what is your inactive thread timeout parm (THDINACT) on DB2?

I'd really try to analyze and see how long the threads are staying active on DB2. And whether they are actually doing work there or just staying connected doing nothing. If the latter, then there's a problem on the server side that the applications need to address. If they are truly active doing DB2 calls, then there is enough volume to increase their number of connections on the server side to 30-50. But they should have some type of much smaller number to reduce to if the activity goes down. They should not be holding 30-50 threads all the time while inactive.

Typical I see is min of 5-10 and max of 25-40. One system we have has min of 10 and max of 50 (but it's really really active at some points during the day.) But other times, they shrink the pool back to a minimal level.

If the threads are Java threads, i'd really carefully check the code and ensure that the connection is properly being closed. If the Java code opens a connection, then a statement and then a result set, these must all be closed and in the reverse order, i.e. result closed then the statement, then the connection. Not doing it this way will cause the connection to be kept open in DB2 connect and DB2, which will tie up connections in the pool incorrectly. I can't count the number of times, I've seen a connection open and a result set and then an error happens and the Java code goes to a try-finally section where the connection is closed but the result set was not, with the result that the connection is left open on DB2.

Your overall system numbers seem pretty fine to me and don't need to be increased.

Just some suggestions from my point of view.

Myron

________________________________
From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 11:39:26 AM
Subject: [DB2-L] Thread timeout issue

The following came from a server area technician.

I am seeing a large number of database connection timeouts in the application log.
This seems to happen when the portal can't get a DB2 connection for queries from a server.

SQL Exception caught while obtaining a connection to the {xxx} data source.
No managed connections available within configured blocking timeout (30000 ms) nested throwable.

Right now they have a maximum of 20 DB2 connections configured in the portal and they are requesting an increase in the DB2 side of which I don't agree.


In looking at the zparm (V8) all the system defaults were applied when going to V8.

Condbat = 10000
Maxdbat = 200
Cthread = 200


Any suggestions or recommendations.

Bob Knight











________________________________

[ http://www.idug.org/images/banners/idug_2011.gif ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Carol Anne Sutfin

Re: Thread timeout issue
(in response to Robert Knight)
Myron,

I have been following this because I have JAVA threads that sit idle for
hours.

IDTHTION is set to 600

What is the problem with Java?
I just assumed that the thread was not being released by the JAVA task on
the server.
I keep forgetting to complain to the vendor about this.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



From: Myron Miller <[login to unmask email]>
To: [login to unmask email]
Date: 01/26/2011 12:31 PM
Subject: Re: [DB2-L] Thread timeout issue
Sent by: IDUG DB2-L <[login to unmask email]>



You're right. My memory is very bad (senior moments!!!!)

We use Idle Thread Timeout (Seconds)..............(IDTHTOIN)..
3600 . So I doubt I could say yours is high. I don't like it this much
but we have users with Access and Excell that need this long as well as
some Cognos and Informatica threads that actually have well over 1/2 hour
between DB2 calls.

Do you show any threads executing anything that are showing but are really
inactive? I typically can find some like this that have sat there for
10-20 minutes. And if they are JAVA, I know we have an issue.

NAME ST A
SERVER RA *


Myron


From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 12:52:30 PM
Subject: Re: [DB2-L] Thread timeout issue




Thanks for the response





And what is your inactive thread timeout parm (THDINACT) on DB2?
can’t find this parameter





But IDTHTOIN=1800, is high





After a look at the display thread they are executing JAVA and I found 3
threads that display as DISCONN.





This is a good place to start.





Bob






From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, January 26, 2011 12:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Thread timeout issue



Robert,
My first question would be how many of these threads are you seeing
active on DB2? And what is your inactive thread timeout parm (THDINACT)
on DB2?

I'd really try to analyze and see how long the threads are staying active
on DB2. And whether they are actually doing work there or just staying
connected doing nothing. If the latter, then there's a problem on the
server side that the applications need to address. If they are truly
active doing DB2 calls, then there is enough volume to increase their
number of connections on the server side to 30-50. But they should have
some type of much smaller number to reduce to if the activity goes down.
They should not be holding 30-50 threads all the time while inactive.

Typical I see is min of 5-10 and max of 25-40. One system we have has min
of 10 and max of 50 (but it's really really active at some points during
the day.) But other times, they shrink the pool back to a minimal level.

If the threads are Java threads, i'd really carefully check the code and
ensure that the connection is properly being closed. If the Java code
opens a connection, then a statement and then a result set, these must all
be closed and in the reverse order, i.e. result closed then the statement,
then the connection. Not doing it this way will cause the connection to be
kept open in DB2 connect and DB2, which will tie up connections in the pool
incorrectly. I can't count the number of times, I've seen a connection open
and a result set and then an error happens and the Java code goes to a
try-finally section where the connection is closed but the result set was
not, with the result that the connection is left open on DB2.

Your overall system numbers seem pretty fine to me and don't need to be
increased.

Just some suggestions from my point of view.

Myron




From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 11:39:26 AM
Subject: [DB2-L] Thread timeout issue



The following came from a server area technician.





I am seeing a large number of database connection timeouts in the
application log.


This seems to happen when the portal can’t get a DB2 connection for queries
from a server.





SQL Exception caught while obtaining a connection to the {xxx} data source.
No managed connections available within configured blocking timeout (30000
ms) nested throwable.





Right now they have a maximum of 20 DB2 connections configured in the
portal and they are requesting an increase in the DB2 side of which I don’t
agree.








In looking at the zparm (V8) all the system defaults were applied when
going to V8.





Condbat = 10000


Maxdbat = 200


Cthread = 200








Any suggestions or recommendations.





Bob Knight





































The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.







The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Myron Miller

Re: Thread timeout issue
(in response to Carol Anne Sutfin)
Carol,
The only reason I have ever seen Java threads hang around (and this is what
I have seen, your mileage may vary), is when the thread is not properly closed
(or not closed at all. Generally they don't close the statement or result set
before closing the connection (close.conn). That is documented in the DB2
connect manuals on Java as an issue and will leave threads open. Where I
typically find a problem is when the Java code hits an exception and goes to the
try-finally section of the code at the end of the method. it's very common for
the connections, statements, result sets not to be handled in the final error
handling process. Lots of programmers code for everything working correctly but
forget to handle everything if something goes wrong. 99% of my threads hanging
is due to this issue.

in addition, some add-ons, such as IBATIS, or others, do not clean up and close
the thread correctly in all cases. If the programmer really understands it and
codes it correctly, IBATIS will close the connections properly and then you
never see threads hanging around basically doing nothing in DB2.

However, if the connection has not been properly closed, it's quite common for
the threads to stick around in DB2. They will sit there for your timeout period
of 6 minutes before DB2 will time them out.

One other item that might be an issue. Weblogic, Websphere have a feature
called Test connection. It can be configured to test a DB2 connection pool
thread before, after and every so many minutes/seconds as well. Testing after
every single SQL statement is serious overkill but I've seen systems configured
that way. Similarly, testing every few seconds/minutes can be overkill as well
if the connection is tested before every single sql statement. And certainly
testing every few seconds/minutes can be a lot of overhead and can issue a lot
of SQL statements and make the Java thread look busy and the thread stay open
when nothing really is going on.

Most of our app's don't properly handle a bad Open.conn situation where the
connection pool thread is bad because DB2 system is not available (poor code),
so they test before every time they open a connection. And consider that the
default test code is SELECT * FROM SYSIBM.SYSTABLES or SELECT COUNT(*) FROM
SYSIBM.SYSTABLES.

I insist that the test code use SELECT 1 FROM SYSIBM.SYSDUMMY1 where 1 = 0.
That has saved literally minutes a day in CPU (and I'm talking tens of minutes)
- one system alone was doing several million tests per day. This normally uses
about 200-300 microseconds versus the 100-200 milliseconds for the default SQL
test code.

So you could be seeing the Java code connection stay alive from either improper
coding - not closing the connection properly or from this testing non-sense.

Hope this helps. And remember, this is just my observations from what I've
experienced.

Myron



________________________________
From: "[login to unmask email]" <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 7:00:41 PM
Subject: Re: [DB2-L] Thread timeout issue

Myron,

I have been following this because I have JAVA threads that sit idle for
hours.

IDTHTION is set to 600

What is the problem with Java?
I just assumed that the thread was not being released by the JAVA task on
the server.
I keep forgetting to complain to the vendor about this.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
[login to unmask email]



From: Myron Miller <[login to unmask email]>
To: [login to unmask email]
Date: 01/26/2011 12:31 PM
Subject: Re: [DB2-L] Thread timeout issue
Sent by: IDUG DB2-L <[login to unmask email]>



You're right. My memory is very bad (senior moments!!!!)

We use Idle Thread Timeout (Seconds)..............(IDTHTOIN)..
3600 . So I doubt I could say yours is high. I don't like it this much
but we have users with Access and Excell that need this long as well as
some Cognos and Informatica threads that actually have well over 1/2 hour
between DB2 calls.

Do you show any threads executing anything that are showing but are really
inactive? I typically can find some like this that have sat there for
10-20 minutes. And if they are JAVA, I know we have an issue.

NAME ST A
SERVER RA *


Myron


From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 12:52:30 PM
Subject: Re: [DB2-L] Thread timeout issue




Thanks for the response





And what is your inactive thread timeout parm (THDINACT) on DB2?
can’t find this parameter





But IDTHTOIN=1800, is high





After a look at the display thread they are executing JAVA and I found 3
threads that display as DISCONN.





This is a good place to start.





Bob






From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Myron Miller
Sent: Wednesday, January 26, 2011 12:21 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Thread timeout issue



Robert,
My first question would be how many of these threads are you seeing
active on DB2? And what is your inactive thread timeout parm (THDINACT)
on DB2?

I'd really try to analyze and see how long the threads are staying active
on DB2. And whether they are actually doing work there or just staying
connected doing nothing. If the latter, then there's a problem on the
server side that the applications need to address. If they are truly
active doing DB2 calls, then there is enough volume to increase their
number of connections on the server side to 30-50. But they should have
some type of much smaller number to reduce to if the activity goes down.
They should not be holding 30-50 threads all the time while inactive.

Typical I see is min of 5-10 and max of 25-40. One system we have has min
of 10 and max of 50 (but it's really really active at some points during
the day.) But other times, they shrink the pool back to a minimal level.

If the threads are Java threads, i'd really carefully check the code and
ensure that the connection is properly being closed. If the Java code
opens a connection, then a statement and then a result set, these must all
be closed and in the reverse order, i.e. result closed then the statement,
then the connection. Not doing it this way will cause the connection to be
kept open in DB2 connect and DB2, which will tie up connections in the pool
incorrectly. I can't count the number of times, I've seen a connection open
and a result set and then an error happens and the Java code goes to a
try-finally section where the connection is closed but the result set was
not, with the result that the connection is left open on DB2.

Your overall system numbers seem pretty fine to me and don't need to be
increased.

Just some suggestions from my point of view.

Myron




From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 11:39:26 AM
Subject: [DB2-L] Thread timeout issue



The following came from a server area technician.





I am seeing a large number of database connection timeouts in the
application log.


This seems to happen when the portal can’t get a DB2 connection for queries
from a server.





SQL Exception caught while obtaining a connection to the {xxx} data source.
No managed connections available within configured blocking timeout (30000
ms) nested throwable.





Right now they have a maximum of 20 DB2 connections configured in the
portal and they are requesting an increase in the DB2 side of which I don’t
agree.








In looking at the zparm (V8) all the system defaults were applied when
going to V8.





Condbat = 10000


Maxdbat = 200


Cthread = 200








Any suggestions or recommendations.





Bob Knight





































The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.







The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.









The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA
*
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the
home of IDUG's Listserv


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Robert Knight

Re: Thread timeout issue
(in response to Myron Miller)
Myron One last question:

When I display the threads I get a number of DISCONN threads.

Then a minute or so later I do a display thread again and they disappear.

Then do another display and 1 or 2 show back up.

I am at a loss of why this happens, do you have an explanation?

Greatly appreciate your help

Bob Knight

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Myron Miller

Re: Thread timeout issue
(in response to Robert Knight)
DISCONN threads are those that have been finished but haven't been fully
terminated by TCPIP from DB2. There is a TCPIP system parm that says how
frequently it'll scan to do cleanup. The default is generally 2 minutes (and I
don't remember the parm, sorry). But these types of threads, I've found, will
hang around until TCPIP goes thru its cycle cleanup and then go away. And if
you have quick running threads, they'll come back and then go away again.

And remember some of the DISCONN threads can be re-used if certain conditions,
such as the same AUTHID, and other conditions are met. So they can go from
DISCONN to CONN to DISCONN to disappear within a few minutes.

That's my explanation for what is happening. I'm sure some of the communication
experts probably can explain this better than I can.

Myron



________________________________
From: Robert Knight <[login to unmask email]>
To: [login to unmask email]
Sent: Tue, February 8, 2011 11:22:01 AM
Subject: Re: [DB2-L] Thread timeout issue


Myron One last question:

When I display the threads I get a number of DISCONN threads.

Then a minute or so later I do a display thread again and they disappear.

Then do another display and 1 or 2 show back up.

I am at a loss of why this happens, do you have an explanation?

Greatly appreciate your help

Bob Knight
________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.

________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not
already an IDUG member, please register here.


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv