Can someone enlighten me on 'handles'?

[Jurgen Haan]

Hi there.

Recently, one of our application servers is moaning about its handles.
(no more handles available).

I'm guessing it has to do with some bug in our software where it does
not release handles properly and every x thousand queries, one available
handle gets lost (unreleased).

Are handles available PER application, or is there one general pool of
handles from which agents get their share?

In DB2 8.2 LUW 64bit, how many handles are available?
Can this limit be changed or is it hardcoded?

Can I get an overview of handles in use/available (I know about the
application snapshot, but I was hoping for something a little more...
brief.)

Can I assume for every cursor in use, 1 handle is allocated en vice
versa? Or is this wrong to think when you have a query that operates on
multiple datebbase objects. (for instance, 1 query that joins over 3
tables. Is that 1 handle and 3 cursors?)

Perhaps someone knows of some interesting reading on this subject.
Searching for handles in 1 pain in the ** because all I get is how DB2
is *handling* some stuff.

Thanks.

-R-

[Mark A]

"Jurgen Haan" <ju****@fake.tl dwrote in message
news:48******** *************** @news.xs4all.nl ...

Hi there.

Recently, one of our application servers is moaning about its handles.
(no more handles available).

I'm guessing it has to do with some bug in our software where it does
not release handles properly and every x thousand queries, one available
handle gets lost (unreleased).

Are handles available PER application, or is there one general pool of
handles from which agents get their share?

In DB2 8.2 LUW 64bit, how many handles are available?
Can this limit be changed or is it hardcoded?

Can I get an overview of handles in use/available (I know about the
application snapshot, but I was hoping for something a little more...
brief.)

Can I assume for every cursor in use, 1 handle is allocated en vice
versa? Or is this wrong to think when you have a query that operates on
multiple datebbase objects. (for instance, 1 query that joins over 3
tables. Is that 1 handle and 3 cursors?)

Perhaps someone knows of some interesting reading on this subject.
Searching for handles in 1 pain in the ** because all I get is how DB2
is *handling* some stuff.

Thanks.

-R-

Most likely, the applications are not closing transactions as soon as they
could (or should). Or they are not committing as often as they should. These
are common problems in many shops these days where programmers are very
poorly trained.

[Jurgen Haan]

Mark A wrote:

>
Most likely, the applications are not closing transactions as soon as they
could (or should). Or they are not committing as often as they should. These
are common problems in many shops these days where programmers are very
poorly trained.

Lol!

I kind of figured it's most likely to be a fault in our software.
But nevertheless, I would like some clarification on the subjects stated
in my original post; First to be able to answer the programmers'
specific questions, and second for my own peace of mind, I like to know
what's going on under the bonnet.

And in defence of our programmers; they know their stuff, but mistakes
are easily made. (know that a hardcopy of your reply is going their way. :P)

Thanks for your reply.

-R-

[Jeroen van den Broek]

On Jun 5, 1:17*pm, Jurgen Haan <[e-mail] dwrote:

Hi there.

Recently, one of our application servers is moaning about its handles.
(no more handles available).

I'm guessing it has to do with some bug in our software where it does
not release handles properly and every x thousand queries, one available
handle gets lost (unreleased).

Are handles available PER application, or is there one general pool of
handles from which agents get their share?

In DB2 8.2 LUW 64bit, how many handles are available?
Can this limit be changed or is it hardcoded?

Can I get an overview of handles in use/available (I know about the
application snapshot, but I was hoping for something a little more...
brief.)

Can I assume for every cursor in use, 1 handle is allocated en vice
versa? Or is this wrong to think when you have a query that operates on
multiple datebbase objects. (for instance, 1 query that joins over 3
tables. Is that 1 handle and 3 cursors?)

Perhaps someone knows of some interesting reading on this subject.
Searching for handles in 1 pain in the ** because all I get is how DB2
is *handling* some stuff.

Thanks.

-R-

Please always supply DB2 version and OS/platform.

Assuming DB2 LUW v8, here is some info on different type of "handles":
http://publib.boulder.ibm.com/infoce...d/c0004933.htm

HTH

--
Jeroen

[Mark A]

>"Jeroen van den Broek" <nl****@baasbov enbaas.demon.nl wrote in message

>news:6b******* *************** ************@c5 8g2000hsc.googl egroups.com...
Please always supply DB2 version and OS/platform.
Assuming DB2 LUW v8, here is some info on different type of "handles":
http://publib.boulder.ibm.com/infoce...d/c0004933.htm

HTH
Jeroen

Although the OP did not state it explicitly, my guess is that the problem is
cursor handles. Java programmers usually don't have to open a cursor, even
though the JDBC interface opens one (or many) cursors for them depending on
what they are doing.

The resulting error is usually a -805 "package not found" when DB2 runs out
of system packages to handle cursors (usually large cursors). The large
cursor packages can handle thousands of handles each, but for some sloppy
programs that is not enough. The default is to have 3 large cursor packages
available, and the error is returned when DB2 cannot find the 4th package
(the first large cursor package ends in x'00', so the 4th one end in x'03').
This can be increased to 30 packages by rebinding the package with an option
to have more, but in my experience if the programmers can't get by with 3,
they will soon exhaust all 30 if you give them that many. They are like drug
addicts, the more you give them, the more they want.

Now it is possible, since the OP did not mention -805 error code, then he is
experiences some other type of handle problem. In that case it would help to
have more detailed information about what error message is being received.

[The Boss]

Mark A wrote:

>"Jeroen van den Broek" <nl****@baasbov enbaas.demon.nl wrote in
message
news:6b******* *************** ************@c5 8g2000hsc.googl egroups.com...
Please always supply DB2 version and OS/platform. Assuming DB2 LUW v8,
here is some info on different type of
"handles":
http://publib.boulder.ibm.com/infoce...d/c0004933.htm

HTH
Jeroen

Although the OP did not state it explicitly, my guess is that the
problem is cursor handles. Java programmers usually don't have to
open a cursor, even though the JDBC interface opens one (or many)
cursors for them depending on what they are doing.

The resulting error is usually a -805 "package not found" when DB2
runs out of system packages to handle cursors (usually large
cursors). The large cursor packages can handle thousands of handles
each, but for some sloppy programs that is not enough. The default is
to have 3 large cursor packages available, and the error is returned
when DB2 cannot find the 4th package (the first large cursor package
ends in x'00', so the 4th one end in x'03'). This can be increased to
30 packages by rebinding the package with an option to have more, but
in my experience if the programmers can't get by with 3, they will
soon exhaust all 30 if you give them that many. They are like drug
addicts, the more you give them, the more they want.
Now it is possible, since the OP did not mention -805 error code,
then he is experiences some other type of handle problem. In that
case it would help to have more detailed information about what error
message is being received.

Yep, that's why I only could offer the link to the docs.
It could even be OP has issues with file handles on OS level...

--
Jeroen

[Jurgen Haan]

Jeroen van den Broek wrote:

On Jun 5, 1:17 pm, Jurgen Haan <[e-mail] dwrote:

>Hi there.

Recently, one of our application servers is moaning about its handles.
(no more handles available).

I'm guessing it has to do with some bug in our software where it does
not release handles properly and every x thousand queries, one available
handle gets lost (unreleased).

Are handles available PER application, or is there one general pool of
handles from which agents get their share?

In DB2 8.2 LUW 64bit, how many handles are available?
Can this limit be changed or is it hardcoded?

Can I get an overview of handles in use/available (I know about the
application snapshot, but I was hoping for something a little more...
brief.)

Can I assume for every cursor in use, 1 handle is allocated en vice
versa? Or is this wrong to think when you have a query that operates on
multiple datebbase objects. (for instance, 1 query that joins over 3
tables. Is that 1 handle and 3 cursors?)

Perhaps someone knows of some interesting reading on this subject.
Searching for handles in 1 pain in the ** because all I get is how DB2
is *handling* some stuff.

Thanks.

-R-

Please always supply DB2 version and OS/platform.

Assuming DB2 LUW v8, here is some info on different type of "handles":
http://publib.boulder.ibm.com/infoce...d/c0004933.htm

HTH

--
Jeroen

:S

I quote:
"In DB2 8.2 LUW 64bit, how many handles are available?"

[Jurgen Haan]

Jeroen van den Broek wrote:

>
Assuming DB2 LUW v8, here is some info on different type of "handles":
http://publib.boulder.ibm.com/infoce...d/c0004933.htm

HTH

--
Jeroen

Thank you. Is a very helpful link.