Hi,
I'm getting SQLCODE: -805, SQLSTATE: 51002, SQLERRMC:
NULLID.SYSLH203. I've seen this problem posted before and I thought
it might be a statement handle leak. I'm using a pooled datasource,
then I issue sql statements. I've cleaned up all my sql calls
ensuring there is prepared statement close and result set close for
each one, but it didn't change the problem. I added a commit for
good measure, but that didn't help either. As suggested by another
post, I added 20 packages and upped my applheapsize=16384 and
maxappls=50, but that only delayed the problem for a couple hours.

I've taken health snapshots to get a sense of whats going on. The
memory stats look fine. here's some other stats from the snapshot:

Commit statements attempted = 15779
Rollback statements attempted = 41
Dynamic statements attempted = 62819
Static statements attempted = 15826
Failed statement operations = 139
Select SQL statements executed = 13464
Update/Insert/Delete statements executed = 11787
DDL statements executed = 11
Inactive stmt history memory usage (bytes) = 0

Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 37
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0

Rows deleted = 0
Rows inserted = 8904
Rows updated = 3595
Rows selected = 11452
Rows read = 2696559
Binds/precompiles attempted = 0

Number of SQL requests since last commit = 95
Commit statements = 15480
Rollback statements = 0
Dynamic SQL statements attempted = 60820
Static SQL statements attempted = 15480
Failed statement operations = 109
Select SQL statements executed = 13022
Update/Insert/Delete statements executed = 11478

I am running DB2 v8.1.0.96", "s050811", "MI00138", and FixPak "10".

Does anyone know what might be going on, or how to debug this
problem? Everything I tried doesn't seem to make a difference.
Thanks,
Louise

lsaidnawey@yahoo.com wrote:

> Hi,
> I'm getting SQLCODE: -805, SQLSTATE: 51002, SQLERRMC:
> NULLID.SYSLH203. I've seen this problem posted before and I thought
> it might be a statement handle leak. I'm using a pooled datasource,
> then I issue sql statements. I've cleaned up all my sql calls
> ensuring there is prepared statement close and result set close for
> each one, but it didn't change the problem. I added a commit for
> good measure, but that didn't help either. As suggested by another
> post, I added 20 packages and upped my applheapsize=16384 and
> maxappls=50, but that only delayed the problem for a couple hours.
>
> I've taken health snapshots to get a sense of whats going on. The
> memory stats look fine. here's some other stats from the snapshot:
>
> Commit statements attempted = 15779
> Rollback statements attempted = 41
> Dynamic statements attempted = 62819
> Static statements attempted = 15826
> Failed statement operations = 139
> Select SQL statements executed = 13464
> Update/Insert/Delete statements executed = 11787
> DDL statements executed = 11
> Inactive stmt history memory usage (bytes) = 0
>
> Internal automatic rebinds = 0
> Internal rows deleted = 0
> Internal rows inserted = 0
> Internal rows updated = 0
> Internal commits = 37
> Internal rollbacks = 0
> Internal rollbacks due to deadlock = 0
>
> Rows deleted = 0
> Rows inserted = 8904
> Rows updated = 3595
> Rows selected = 11452
> Rows read = 2696559
> Binds/precompiles attempted = 0
>
> Number of SQL requests since last commit = 95
> Commit statements = 15480
> Rollback statements = 0
> Dynamic SQL statements attempted = 60820
> Static SQL statements attempted = 15480
> Failed statement operations = 109
> Select SQL statements executed = 13022
> Update/Insert/Delete statements executed = 11478
>
> I am running DB2 v8.1.0.96", "s050811", "MI00138", and FixPak "10".
>
> Does anyone know what might be going on, or how to debug this
> problem? Everything I tried doesn't seem to make a difference.

Do you properly return your connections to the connection pool?
DB2PooledConnection has a specific method for that. Don't rely on Java's
garbage collector for something like that.

Besides that, you may want to take a JDBC trace to figure out which handle
receives the -805. Then have a look at the number of the handles. If they
are constantly growing, you still have a leak somewhere.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

On Feb 23, 3:07 am, Knut Stolze <sto...@de.ibm.com> wrote:
> lsaidna...@yahoo.com wrote:
> > Hi,
> > I'm getting SQLCODE: -805, SQLSTATE: 51002, SQLERRMC:
> > NULLID.SYSLH203. I've seen this problem posted before and I thought
> > it might be a statement handle leak. I'm using a pooled datasource,
> > then I issue sql statements. I've cleaned up all my sql calls
> > ensuring there is prepared statement close and result set close for
> > each one, but it didn't change the problem. I added a commit for
> > good measure, but that didn't help either. As suggested by another
> > post, I added 20 packages and upped my applheapsize=16384 and
> > maxappls=50, but that only delayed the problem for a couple hours.
>
> > I've taken health snapshots to get a sense of whats going on. The
> > memory stats look fine. here's some other stats from the snapshot:
>
> > Commit statements attempted = 15779
> > Rollback statements attempted = 41
> > Dynamic statements attempted = 62819
> > Static statements attempted = 15826
> > Failed statement operations = 139
> > Select SQL statements executed = 13464
> > Update/Insert/Delete statements executed = 11787
> > DDL statements executed = 11
> > Inactive stmt history memory usage (bytes) = 0
>
> > Internal automatic rebinds = 0
> > Internal rows deleted = 0
> > Internal rows inserted = 0
> > Internal rows updated = 0
> > Internal commits = 37
> > Internal rollbacks = 0
> > Internal rollbacks due to deadlock = 0
>
> > Rows deleted = 0
> > Rows inserted = 8904
> > Rows updated = 3595
> > Rows selected = 11452
> > Rows read = 2696559
> > Binds/precompiles attempted = 0
>
> > Number of SQL requests since last commit = 95
> > Commit statements = 15480
> > Rollback statements = 0
> > Dynamic SQL statements attempted = 60820
> > Static SQL statements attempted = 15480
> > Failed statement operations = 109
> > Select SQL statements executed = 13022
> > Update/Insert/Delete statements executed = 11478
>
> > I am running DB2 v8.1.0.96", "s050811", "MI00138", and FixPak "10".
>
> > Does anyone know what might be going on, or how to debug this
> > problem? Everything I tried doesn't seem to make a difference.
>
> Do you properly return your connections to the connection pool?
> DB2PooledConnection has a specific method for that. Don't rely on Java's
> garbage collector for something like that.
>
> Besides that, you may want to take a JDBC trace to figure out which handle
> receives the -805. Then have a look at the number of the handles. If they
> are constantly growing, you still have a leak somewhere.
>
> --
> Knut Stolze
> DB2 z/OS Utilities Development
> IBM Germany- Hide quoted text -
>
> - Show quoted text -

Thanks for the quick reply. I am not returning the connection, so
that may be my problem. However I am using
DB2ConnectionPoolDataSource(), not DB2PooledConnection() to establish
my connection. I see the connectionReuseProtocol and
statementReuseProtocol properties in DB2ConnectionPoolDataSource.
Can these be used to return the connection? Are you suggesting
that I switch to DB2PooledConnection and use recycleConnection or
resetPhysicalConnection instead?

lsaidnawey@yahoo.com wrote:

> On Feb 23, 3:07 am, Knut Stolze <sto...@de.ibm.com> wrote:
>> lsaidna...@yahoo.com wrote:
>> > Hi,
>> > I'm getting SQLCODE: -805, SQLSTATE: 51002, SQLERRMC:
>> > NULLID.SYSLH203. I've seen this problem posted before and I thought
>> > it might be a statement handle leak. I'm using a pooled datasource,
>> > then I issue sql statements. I've cleaned up all my sql calls
>> > ensuring there is prepared statement close and result set close for
>> > each one, but it didn't change the problem. I added a commit for
>> > good measure, but that didn't help either. As suggested by another
>> > post, I added 20 packages and upped my applheapsize=16384 and
>> > maxappls=50, but that only delayed the problem for a couple hours.

Adding packages is usually not a fix for the problem; it just delays it. If
you are lucky, the delay is big enough to hide the problem. But as you
noticed, that's not reliable at all.

>> > I've taken health snapshots to get a sense of whats going on. The
>> > memory stats look fine. here's some other stats from the snapshot:
>>
>> > Commit statements attempted = 15779
>> > Rollback statements attempted = 41
>> > Dynamic statements attempted = 62819
>> > Static statements attempted = 15826
>> > Failed statement operations = 139
>> > Select SQL statements executed = 13464
>> > Update/Insert/Delete statements executed = 11787
>> > DDL statements executed = 11
>> > Inactive stmt history memory usage (bytes) = 0
>>
>> > Internal automatic rebinds = 0
>> > Internal rows deleted = 0
>> > Internal rows inserted = 0
>> > Internal rows updated = 0
>> > Internal commits = 37
>> > Internal rollbacks = 0
>> > Internal rollbacks due to deadlock = 0
>>
>> > Rows deleted = 0
>> > Rows inserted = 8904
>> > Rows updated = 3595
>> > Rows selected = 11452
>> > Rows read = 2696559
>> > Binds/precompiles attempted = 0
>>
>> > Number of SQL requests since last commit = 95
>> > Commit statements = 15480
>> > Rollback statements = 0
>> > Dynamic SQL statements attempted = 60820
>> > Static SQL statements attempted = 15480
>> > Failed statement operations = 109
>> > Select SQL statements executed = 13022
>> > Update/Insert/Delete statements executed = 11478
>>
>> > I am running DB2 v8.1.0.96", "s050811", "MI00138", and FixPak "10".
>>
>> > Does anyone know what might be going on, or how to debug this
>> > problem? Everything I tried doesn't seem to make a difference.
>>
>> Do you properly return your connections to the connection pool?
>> DB2PooledConnection has a specific method for that. Don't rely on Java's
>> garbage collector for something like that.
>>
>> Besides that, you may want to take a JDBC trace to figure out which
>> handle
>> receives the -805. Then have a look at the number of the handles. If
>> they are constantly growing, you still have a leak somewhere.
>
> Thanks for the quick reply. I am not returning the connection, so
> that may be my problem. However I am using
> DB2ConnectionPoolDataSource(), not DB2PooledConnection() to establish
> my connection. I see the connectionReuseProtocol and
> statementReuseProtocol properties in DB2ConnectionPoolDataSource.
> Can these be used to return the connection?

Have a look here:

http://java.sun.com/j2se/1.4.2/docs/...onnection.html

You have to close a PooledConnection so that it is returned to the pool
properly.

However, I have my doubts that the handling of Connection or
PooledConnection objects causes this problem. Statements use packages -
not connections. What happens if you don't use connection pooling?

What have you found in the JDBC trace?

> Are you suggesting
> that I switch to DB2PooledConnection and use recycleConnection or
> resetPhysicalConnection instead?

No, I'm not suggestion that. Besides, you shouldn't fiddle with the
physical connection at all if you're using connection pooling. (The Java
docs are quite clear on that.)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

On Feb 23, 10:37 am, Knut Stolze <sto...@de.ibm.com> wrote:
> lsaidna...@yahoo.com wrote:
> > On Feb 23, 3:07 am, Knut Stolze <sto...@de.ibm.com> wrote:
> >> lsaidna...@yahoo.com wrote:
> >> > Hi,
> >> > I'm getting SQLCODE: -805, SQLSTATE: 51002, SQLERRMC:
> >> > NULLID.SYSLH203. I've seen this problem posted before and I thought
> >> > it might be a statement handle leak. I'm using a pooled datasource,
> >> > then I issue sql statements. I've cleaned up all my sql calls
> >> > ensuring there is prepared statement close and result set close for
> >> > each one, but it didn't change the problem. I added a commit for
> >> > good measure, but that didn't help either. As suggested by another
> >> > post, I added 20 packages and upped my applheapsize=16384 and
> >> > maxappls=50, but that only delayed the problem for a couple hours.
>
> Adding packages is usually not a fix for the problem; it just delays it. If
> you are lucky, the delay is big enough to hide the problem. But as you
> noticed, that's not reliable at all.
>
>
>
>
>
> >> > I've taken health snapshots to get a sense of whats going on. The
> >> > memory stats look fine. here's some other stats from the snapshot:
>
> >> > Commit statements attempted = 15779
> >> > Rollback statements attempted = 41
> >> > Dynamic statements attempted = 62819
> >> > Static statements attempted = 15826
> >> > Failed statement operations = 139
> >> > Select SQL statements executed = 13464
> >> > Update/Insert/Delete statements executed = 11787
> >> > DDL statements executed = 11
> >> > Inactive stmt history memory usage (bytes) = 0
>
> >> > Internal automatic rebinds = 0
> >> > Internal rows deleted = 0
> >> > Internal rows inserted = 0
> >> > Internal rows updated = 0
> >> > Internal commits = 37
> >> > Internal rollbacks = 0
> >> > Internal rollbacks due to deadlock = 0
>
> >> > Rows deleted = 0
> >> > Rows inserted = 8904
> >> > Rows updated = 3595
> >> > Rows selected = 11452
> >> > Rows read = 2696559
> >> > Binds/precompiles attempted = 0
>
> >> > Number of SQL requests since last commit = 95
> >> > Commit statements = 15480
> >> > Rollback statements = 0
> >> > Dynamic SQL statements attempted = 60820
> >> > Static SQL statements attempted = 15480
> >> > Failed statement operations = 109
> >> > Select SQL statements executed = 13022
> >> > Update/Insert/Delete statements executed = 11478
>
> >> > I am running DB2 v8.1.0.96", "s050811", "MI00138", and FixPak "10".
>
> >> > Does anyone know what might be going on, or how to debug this
> >> > problem? Everything I tried doesn't seem to make a difference.
>
> >> Do you properly return your connections to the connection pool?
> >> DB2PooledConnection has a specific method for that. Don't rely on Java's
> >> garbage collector for something like that.
>
> >> Besides that, you may want to take a JDBC trace to figure out which
> >> handle
> >> receives the -805. Then have a look at the number of the handles. If
> >> they are constantly growing, you still have a leak somewhere.
>
> > Thanks for the quick reply. I am not returning the connection, so
> > that may be my problem. However I am using
> > DB2ConnectionPoolDataSource(), not DB2PooledConnection() to establish
> > my connection. I see the connectionReuseProtocol and
> > statementReuseProtocol properties in DB2ConnectionPoolDataSource.
> > Can these be used to return the connection?
>
> Have a look here:
>
> http://java.sun.com/j2se/1.4.2/docs/...onnection.html
>
> You have to close a PooledConnection so that it is returned to the pool
> properly.
>
> However, I have my doubts that the handling of Connection or
> PooledConnection objects causes this problem. Statements use packages -
> not connections. What happens if you don't use connection pooling?
>
> What have you found in the JDBC trace?
>
> > Are you suggesting
> > that I switch to DB2PooledConnection and use recycleConnection or
> > resetPhysicalConnection instead?
>
> No, I'm not suggestion that. Besides, you shouldn't fiddle with the
> physical connection at all if you're using connection pooling. (The Java
> docs are quite clear on that.)
>
> --
> Knut Stolze
> DB2 z/OS Utilities Development
> IBM Germany- Hide quoted text -
>
> - Show quoted text -

Only one connection is established and all the sql statements are
executed using this connection. (I'm not sure why connection pooling
was used as I am not the original author.) I can look into changing
it to DriverManager, and I will also set up a JDBC trace. I haven't
set up a trace before, so it will take a little while for me to get it
working. I'll let you know what I find.

Are there special requirements for JDBCTrace? This looks very
simple, so I am not sure why I am having problems. I set up the
db2cli.ini file as documented in the IBM manuals. The /home/db2inst1/
trace dir has perm 777 and there is a blank line at the end of the
db2cli.in file. db2 get cli cfg for section common shows these
settings:
Section: common
-------------------------------------------------
jdbctrace=1
JdbcTracePathName=/home/db2inst1/trace
JDBCTraceFlush=1

I stop and start db2, (db2stop, db2start) and then start my
application, but trace files do not appear in the directory. I have
searched my system from / down, and there are no *.trc files on it.

My test env is DB2 PE on Red Hat. I am using db2 = new
DB2ConnectionPoolDataSource(); to establish the connection, and I
tried manually setting the jdbc driver to 2, db2.setDriverType(2); ,
to ensure that the correct driver is being used.
Louise



lsaidnawey@yahoo.com wrote:
> On Feb 23, 10:37 am, Knut Stolze <sto...@de.ibm.com> wrote:
> > lsaidna...@yahoo.com wrote:
> > > On Feb 23, 3:07 am, Knut Stolze <sto...@de.ibm.com> wrote:
> > >> lsaidna...@yahoo.com wrote:
> > >> > Hi,
> > >> > I'm getting SQLCODE: -805, SQLSTATE: 51002, SQLERRMC:
> > >> > NULLID.SYSLH203. I've seen this problem posted before and I thought
> > >> > it might be a statement handle leak. I'm using a pooled datasource,
> > >> > then I issue sql statements. I've cleaned up all my sql calls
> > >> > ensuring there is prepared statement close and result set close for
> > >> > each one, but it didn't change the problem. I added a commit for
> > >> > good measure, but that didn't help either. As suggested by another
> > >> > post, I added 20 packages and upped my applheapsize=16384 and
> > >> > maxappls=50, but that only delayed the problem for a couple hours.
> >
> > Adding packages is usually not a fix for the problem; it just delays it. If
> > you are lucky, the delay is big enough to hide the problem. But as you
> > noticed, that's not reliable at all.
> >
> >
> >
> >
> >
> > >> > I've taken health snapshots to get a sense of whats going on. The
> > >> > memory stats look fine. here's some other stats from the snapshot:
> >
> > >> > Commit statements attempted = 15779
> > >> > Rollback statements attempted = 41
> > >> > Dynamic statements attempted = 62819
> > >> > Static statements attempted = 15826
> > >> > Failed statement operations = 139
> > >> > Select SQL statements executed = 13464
> > >> > Update/Insert/Delete statements executed = 11787
> > >> > DDL statements executed = 11
> > >> > Inactive stmt history memory usage (bytes) = 0
> >
> > >> > Internal automatic rebinds = 0
> > >> > Internal rows deleted = 0
> > >> > Internal rows inserted = 0
> > >> > Internal rows updated = 0
> > >> > Internal commits = 37
> > >> > Internal rollbacks = 0
> > >> > Internal rollbacks due to deadlock = 0
> >
> > >> > Rows deleted = 0
> > >> > Rows inserted = 8904
> > >> > Rows updated = 3595
> > >> > Rows selected = 11452
> > >> > Rows read = 2696559
> > >> > Binds/precompiles attempted = 0
> >
> > >> > Number of SQL requests since last commit = 95
> > >> > Commit statements = 15480
> > >> > Rollback statements = 0
> > >> > Dynamic SQL statements attempted = 60820
> > >> > Static SQL statements attempted = 15480
> > >> > Failed statement operations = 109
> > >> > Select SQL statements executed = 13022
> > >> > Update/Insert/Delete statements executed = 11478
> >
> > >> > I am running DB2 v8.1.0.96", "s050811", "MI00138", and FixPak "10".
> >
> > >> > Does anyone know what might be going on, or how to debug this
> > >> > problem? Everything I tried doesn't seem to make a difference.
> >
> > >> Do you properly return your connections to the connection pool?
> > >> DB2PooledConnection has a specific method for that. Don't rely on Java's
> > >> garbage collector for something like that.
> >
> > >> Besides that, you may want to take a JDBC trace to figure out which
> > >> handle
> > >> receives the -805. Then have a look at the number of the handles. If
> > >> they are constantly growing, you still have a leak somewhere.
> >
> > > Thanks for the quick reply. I am not returning the connection, so
> > > that may be my problem. However I am using
> > > DB2ConnectionPoolDataSource(), not DB2PooledConnection() to establish
> > > my connection. I see the connectionReuseProtocol and
> > > statementReuseProtocol properties in DB2ConnectionPoolDataSource.
> > > Can these be used to return the connection?
> >
> > Have a look here:
> >
> > http://java.sun.com/j2se/1.4.2/docs/...onnection.html
> >
> > You have to close a PooledConnection so that it is returned to the pool
> > properly.
> >
> > However, I have my doubts that the handling of Connection or
> > PooledConnection objects causes this problem. Statements use packages -
> > not connections. What happens if you don't use connection pooling?
> >
> > What have you found in the JDBC trace?
> >
> > > Are you suggesting
> > > that I switch to DB2PooledConnection and use recycleConnection or
> > > resetPhysicalConnection instead?
> >
> > No, I'm not suggestion that. Besides, you shouldn't fiddle with the
> > physical connection at all if you're using connection pooling. (The Java
> > docs are quite clear on that.)
> >
> > --
> > Knut Stolze
> > DB2 z/OS Utilities Development
> > IBM Germany- Hide quoted text -
> >
> > - Show quoted text -
>
> Only one connection is established and all the sql statements are
> executed using this connection. (I'm not sure why connection pooling
> was used as I am not the original author.) I can look into changing
> it to DriverManager, and I will also set up a JDBC trace. I haven't
> set up a trace before, so it will take a little while for me to get it
> working. I'll let you know what I find.

On Feb 24, 12:48 pm, lsaidna...@yahoo.com wrote:
> Are there special requirements for JDBCTrace? This looks very
> simple, so I am not sure why I am having problems. I set up the
> db2cli.ini file as documented in the IBM manuals. The /home/db2inst1/
> trace dir has perm 777 and there is a blank line at the end of the
> db2cli.in file. db2 get cli cfg for section common shows these
> settings:
> Section: common
> -------------------------------------------------
> jdbctrace=1
> JdbcTracePathName=/home/db2inst1/trace
> JDBCTraceFlush=1
>
> I stop and start db2, (db2stop, db2start) and then start my
> application, but trace files do not appear in the directory. I have
> searched my system from / down, and there are no *.trc files on it.
>
> My test env is DB2 PE on Red Hat. I am using db2 = new
> DB2ConnectionPoolDataSource(); to establish the connection, and I
> tried manually setting the jdbc driver to 2, db2.setDriverType(2); ,
> to ensure that the correct driver is being used.
> Louise
>
>
>
> lsaidna...@yahoo.com wrote:
> > On Feb 23, 10:37 am, Knut Stolze <sto...@de.ibm.com> wrote:
> > > lsaidna...@yahoo.com wrote:
> > > > On Feb 23, 3:07 am, Knut Stolze <sto...@de.ibm.com> wrote:
> > > >> lsaidna...@yahoo.com wrote:
> > > >> > Hi,
> > > >> > I'm getting SQLCODE: -805, SQLSTATE: 51002, SQLERRMC:
> > > >> > NULLID.SYSLH203. I've seen this problem posted before and I thought
> > > >> > it might be a statement handle leak. I'm using a pooled datasource,
> > > >> > then I issue sql statements. I've cleaned up all my sql calls
> > > >> > ensuring there is prepared statement close and result set close for
> > > >> > each one, but it didn't change the problem. I added a commit for
> > > >> > good measure, but that didn't help either. As suggested by another
> > > >> > post, I added 20 packages and upped my applheapsize=16384 and
> > > >> > maxappls=50, but that only delayed the problem for a couple hours.
>
> > > Adding packages is usually not a fix for the problem; it just delays it. If
> > > you are lucky, the delay is big enough to hide the problem. But as you
> > > noticed, that's not reliable at all.
>
> > > >> > I've taken health snapshots to get a sense of whats going on. The
> > > >> > memory stats look fine. here's some other stats from the snapshot:
>
> > > >> > Commit statements attempted = 15779
> > > >> > Rollback statements attempted = 41
> > > >> > Dynamic statements attempted = 62819
> > > >> > Static statements attempted = 15826
> > > >> > Failed statement operations = 139
> > > >> > Select SQL statements executed = 13464
> > > >> > Update/Insert/Delete statements executed = 11787
> > > >> > DDL statements executed = 11
> > > >> > Inactive stmt history memory usage (bytes) = 0
>
> > > >> > Internal automatic rebinds = 0
> > > >> > Internal rows deleted = 0
> > > >> > Internal rows inserted = 0
> > > >> > Internal rows updated = 0
> > > >> > Internal commits = 37
> > > >> > Internal rollbacks = 0
> > > >> > Internal rollbacks due to deadlock = 0
>
> > > >> > Rows deleted = 0
> > > >> > Rows inserted = 8904
> > > >> > Rows updated = 3595
> > > >> > Rows selected = 11452
> > > >> > Rows read = 2696559
> > > >> > Binds/precompiles attempted = 0
>
> > > >> > Number of SQL requests since last commit = 95
> > > >> > Commit statements = 15480
> > > >> > Rollback statements = 0
> > > >> > Dynamic SQL statements attempted = 60820
> > > >> > Static SQL statements attempted = 15480
> > > >> > Failed statement operations = 109
> > > >> > Select SQL statements executed = 13022
> > > >> > Update/Insert/Delete statements executed = 11478
>
> > > >> > I am running DB2 v8.1.0.96", "s050811", "MI00138", and FixPak "10".
>
> > > >> > Does anyone know what might be going on, or how to debug this
> > > >> > problem? Everything I tried doesn't seem to make a difference.
>
> > > >> Do you properly return your connections to the connection pool?
> > > >> DB2PooledConnection has a specific method for that. Don't rely on Java's
> > > >> garbage collector for something like that.
>
> > > >> Besides that, you may want to take a JDBC trace to figure out which
> > > >> handle
> > > >> receives the -805. Then have a look at the number of the handles. If
> > > >> they are constantly growing, you still have a leak somewhere.
>
> > > > Thanks for the quick reply. I am not returning the connection, so
> > > > that may be my problem. However I am using
> > > > DB2ConnectionPoolDataSource(), not DB2PooledConnection() to establish
> > > > my connection. I see the connectionReuseProtocol and
> > > > statementReuseProtocol properties in DB2ConnectionPoolDataSource.
> > > > Can these be used to return the connection?
>
> > > Have a look here:
>
> > >http://java.sun.com/j2se/1.4.2/docs/...onnection.html
>
> > > You have to close a PooledConnection so that it is returned to the pool
> > > properly.
>
> > > However, I have my doubts that the handling of Connection or
> > > PooledConnection objects causes this problem. Statements use packages -
> > > not connections. What happens if you don't use connection pooling?
>
> > > What have you found in the JDBC trace?
>
> > > > Are you suggesting
> > > > that I switch to DB2PooledConnection and use recycleConnection or
> > > > resetPhysicalConnection instead?
>
> > > No, I'm not suggestion that. Besides, you shouldn't fiddle with the
> > > physical connection at all if you're using connection pooling. (The Java
> > > docs are quite clear on that.)
>
> > > --
> > > Knut Stolze
> > > DB2 z/OS Utilities Development
> > > IBM Germany- Hide quoted text -
>
> > > - Show quoted text -
>
> > Only one connection is established and all the sql statements are
> > executed using this connection. (I'm not sure why connection pooling
> > was used as I am not the original author.) I can look into changing
> > it to DriverManager, and I will also set up a JDBC trace. I haven't
> > set up a trace before, so it will take a little while for me to get it
> > working. I'll let you know what I find.- Hide quoted text -
>
> - Show quoted text -

I upgraded to fixpak 14, and turned on the trace. The trace is
quite large since this happens after 15000 sql calls. It also does
not happen after the same sql call. Is there something in the trace
that I should be looking for?

On Mar 16, 8:33 am, lsaidna...@yahoo.com wrote:
> On Feb 24, 12:48 pm, lsaidna...@yahoo.com wrote:
>
>
>
>
>
> > Are there special requirements for JDBCTrace? This looks very
> > simple, so I am not sure why I am having problems. I set up the
> > db2cli.ini file as documented in the IBM manuals. The /home/db2inst1/
> > trace dir has perm 777 and there is a blank line at the end of the
> > db2cli.in file. db2 get cli cfg for section common shows these
> > settings:
> > Section: common
> > -------------------------------------------------
> > jdbctrace=1
> > JdbcTracePathName=/home/db2inst1/trace
> > JDBCTraceFlush=1
>
> > I stop and start db2, (db2stop, db2start) and then start my
> > application, but trace files do not appear in the directory. I have
> > searched my system from / down, and there are no *.trc files on it.
>
> > My test env is DB2 PE on Red Hat. I am using db2 = new
> > DB2ConnectionPoolDataSource(); to establish the connection, and I
> > tried manually setting the jdbc driver to 2, db2.setDriverType(2); ,
> > to ensure that the correct driver is being used.
> > Louise
>
> > lsaidna...@yahoo.com wrote:
> > > On Feb 23, 10:37 am, Knut Stolze <sto...@de.ibm.com> wrote:
> > > > lsaidna...@yahoo.com wrote:
> > > > > On Feb 23, 3:07 am, Knut Stolze <sto...@de.ibm.com> wrote:
> > > > >> lsaidna...@yahoo.com wrote:
> > > > >> > Hi,
> > > > >> > I'm getting SQLCODE: -805, SQLSTATE: 51002, SQLERRMC:
> > > > >> > NULLID.SYSLH203. I've seen this problem posted before and I thought
> > > > >> > it might be a statement handle leak. I'm using a pooled datasource,
> > > > >> > then I issue sql statements. I've cleaned up all my sql calls
> > > > >> > ensuring there is prepared statement close and result set close for
> > > > >> > each one, but it didn't change the problem. I added a commit for
> > > > >> > good measure, but that didn't help either. As suggested by another
> > > > >> > post, I added 20 packages and upped my applheapsize=16384 and
> > > > >> > maxappls=50, but that only delayed the problem for a couple hours.
>
> > > > Adding packages is usually not a fix for the problem; it just delays it. If
> > > > you are lucky, the delay is big enough to hide the problem. But as you
> > > > noticed, that's not reliable at all.
>
> > > > >> > I've taken health snapshots to get a sense of whats going on. The
> > > > >> > memory stats look fine. here's some other stats from the snapshot:
>
> > > > >> > Commit statements attempted = 15779
> > > > >> > Rollback statements attempted = 41
> > > > >> > Dynamic statements attempted = 62819
> > > > >> > Static statements attempted = 15826
> > > > >> > Failed statement operations = 139
> > > > >> > Select SQL statements executed = 13464
> > > > >> > Update/Insert/Delete statements executed = 11787
> > > > >> > DDL statements executed = 11
> > > > >> > Inactive stmt history memory usage (bytes) = 0
>
> > > > >> > Internal automatic rebinds = 0
> > > > >> > Internal rows deleted = 0
> > > > >> > Internal rows inserted = 0
> > > > >> > Internal rows updated = 0
> > > > >> > Internal commits = 37
> > > > >> > Internal rollbacks = 0
> > > > >> > Internal rollbacks due to deadlock = 0
>
> > > > >> > Rows deleted = 0
> > > > >> > Rows inserted = 8904
> > > > >> > Rows updated = 3595
> > > > >> > Rows selected = 11452
> > > > >> > Rows read = 2696559
> > > > >> > Binds/precompiles attempted = 0
>
> > > > >> > Number of SQL requests since last commit = 95
> > > > >> > Commit statements = 15480
> > > > >> > Rollback statements = 0
> > > > >> > Dynamic SQL statements attempted = 60820
> > > > >> > Static SQL statements attempted = 15480
> > > > >> > Failed statement operations = 109
> > > > >> > Select SQL statements executed = 13022
> > > > >> > Update/Insert/Delete statements executed = 11478
>
> > > > >> > I am running DB2 v8.1.0.96", "s050811", "MI00138", and FixPak "10".
>
> > > > >> > Does anyone know what might be going on, or how to debug this
> > > > >> > problem? Everything I tried doesn't seem to make a difference.
>
> > > > >> Do you properly return your connections to the connection pool?
> > > > >> DB2PooledConnection has a specific method for that. Don't rely on Java's
> > > > >> garbage collector for something like that.
>
> > > > >> Besides that, you may want to take a JDBC trace to figure out which
> > > > >> handle
> > > > >> receives the -805. Then have a look at the number of the handles. If
> > > > >> they are constantly growing, you still have a leak somewhere.
>
> > > > > Thanks for the quick reply. I am not returning the connection, so
> > > > > that may be my problem. However I am using
> > > > > DB2ConnectionPoolDataSource(), not DB2PooledConnection() to establish
> > > > > my connection. I see the connectionReuseProtocol and
> > > > > statementReuseProtocol properties in DB2ConnectionPoolDataSource.
> > > > > Can these be used to return the connection?
>
> > > > Have a look here:
>
> > > >http://java.sun.com/j2se/1.4.2/docs/...onnection.html
>
> > > > You have to close a PooledConnection so that it is returned to the pool
> > > > properly.
>
> > > > However, I have my doubts that the handling of Connection or
> > > > PooledConnection objects causes this problem. Statements use packages -
> > > > not connections. What happens if you don't use connection pooling?
>
> > > > What have you found in the JDBC trace?
>
> > > > > Are you suggesting
> > > > > that I switch to DB2PooledConnection and use recycleConnection or
> > > > > resetPhysicalConnection instead?
>
> > > > No, I'm not suggestion that. Besides, you shouldn't fiddle with the
> > > > physical connection at all if you're using connection pooling. (The Java
> > > > docs are quite clear on that.)
>
> > > > --
> > > > Knut Stolze
> > > > DB2 z/OS Utilities Development
> > > > IBM Germany- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Only one connection is established and all the sql statements are
> > > executed using this connection. (I'm not sure why connection pooling
> > > was used as I am not the original author.) I can look into changing
> > > it to DriverManager, and I will also set up a JDBC trace. I haven't
> > > set up a trace before, so it will take a little while for me to get it
> > > working. I'll let you know what I find.- Hide quoted text -
>
> > - Show quoted text -
>
> I upgraded to fixpak 14, and turned on the trace. The trace is
> quite large since this happens after 15000 sql calls. It also does
> not happen after the same sql call. Is there something in the trace
> that I should be looking for?- Hide quoted text -
>
> - Show quoted text -

found it. It appears that db2 does not handle the dal.sql
staement. My original code looked like this
// add a record
Dal dal = new Dal(sql statement 1);
dal.getPrepared().execute();

// fetch the idx for the precise record we just added
dal.sql(sql statement 2);
ResultSet rs = dal.getPrepared().executeQuery();
if (rs.next()) {
refid = rs.getInt("idx");
}
rs.close();
dal.close();

Needed to be changed to
// add a record
Dal dal = new Dal(sql statement 1);
dal.getPrepared().execute();
dal.close();
// fetch the idx for the precise record we just added
Dal dal2 = new Dal(sql statement 2);
ResultSet rs = dal2.getPrepared().executeQuery();
if (rs.next()) {
refid = rs.getInt("idx");
}
rs.close();
dal2.close();

I'm pretty confident that the dal.sql statement caused the problem
because I inadvertantly left it in the code when I first made my
change, i.e.
Dal dal2 = new Dal(sql statement 2);
dal2.sql(sql statement 2);
and I still had the problem.