Re: jdbc sql cli handle leaks

Ok, please, no one go off ranting at me over this.  Afterall, it is in
regard to a potential problem and there is information in it concerning the
Native JDBC driver that all might find helpful.  But the note is really
intended for Alex Garrison who I don't seem to be able to send e-mail to
directly anymore.

If anyone else can jump in and provide more information, feel free.  If
there is a problem here with the freeing of resources, I really want to get
to the bottom of it.  Thanks.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"TRUE! nervous, very, very dreadfully nervous I had been and am; but why
WILL you say that I am mad?
The disease had sharpened my senses, not destroyed, not dulled them. "

- Edgar Allan Poe
"The Tell-Tale Heart"



---------------------- Forwarded by Richard Dettinger/Rochester/IBM on
03/10/2000 09:13 AM ---------------------------

Richard Dettinger
03/10/2000 08:54 AM

To:   "Alex Garrison" <agarrison@logtech.com>
cc:
From: Richard Dettinger/Rochester/IBM@IBMUS
Subject:  Re: jdbc sql cli handle leaks  (Document link: Richard Dettinger)

Sorry it has taken me so long to get back to you... I have had a busy week.
I can offer an explanation for the extra handle allocations you see.  When
we talk about statement handles and the allocation of them, we are really
simplifying the internal concept.  Statement handles are really the only
thing that users typically run into problems with (Connection handles could
cause similar problems, but being that connections are usually pooled and
we don't have the issues with JDBC garbage collection of connection what we
do with statements, we don't typically see problems there).

Internal to the system though, there are other types of handles.  These are
descriptor handles for parameters and result sets.  I don't really exactly
how they are needed to explain why we have them, but let me try to comment
the trace as I understand it in the hopes that it will make what you see
more clear...

// This seems wierd, and you have a lot of error calls in the trace... do
// you know why this is happening?  It seems funny.
0000000E:327336 SQL CLI: SQLError ( 0 , 0 , 346 , SQLCHAR*, SQLINTEGER*

// A statement handle is allocated to do your query.
0000000E:329640 SQL CLI: SQLAllocHandle ( 3 , 2 , 351 )

// An option is being set - probably implicitly by the JDBC driver.
0000000E:329712 SQL CLI: SQLSetStmtOption ( 351 , 10014 , 1 )

// The statement is executed on your behalf.
0000000E:331424 SQL CLI: SQLExecDirect ( 351 , select SR.SROID,RTRIM(SR

// The ResultSet descriptor handle is allocated implicitly.
0000000E:331616 SQL CLI: SQLAllocHandle ( 4 , 2 , 352 )

// We find out info about the executed statement.
0000000E:474552 SQL CLI: SQLRowCount ( 351 , SQLINTEGER* )
0000000E:474640 SQL CLI: SQLColAttributes ( 351 , 1 , 1 , SQLCHAR* , 0
0000000E:474760 SQL CLI: SQLDescribeCol ( 351 , 1 , SQLCHAR* , 30 , SQL

// At the time that we bind the first column, a parameter desciptor is
// allocated. There should be just one of these for all the parameters.
0000000E:474840 SQL CLI: SQLBindCol ( 351 , 1 , 4, SQLPOINTER, 5, SQLIN
0000000E:475136 SQL CLI: SQLAllocHandle ( 4 , 2 , 353 )

It wasn't completely clear to me from the trace exactly what your program
was trying to do.  I got the SQL
statement and made one of my own that was very similar.  Then I executed it
two ways in a loop for
100,000 times each.  The goal here was to try to make the CLI not free its
handles.  I happen to know that
internally the CLI can only keep track of about 40,000 handles at a time,
so if anything was getting lost
in either of these tests, looping 100,000 times should cause an
accumulation that would cause it to
die.  However, things worked perfectly and I got the expected output.

I am providing the program I created to you thinking you might be able to
see what you are doing different in your program than I do here.  If you
can either provide a program or alter this one is a way that recreates the
problem, I can investigate this further.

I will be at COMMON next week, but will be back the week after.  Feel free
to send me info on this and I will look into it then.

(See attached file: Handles.java)


Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"TRUE! nervous, very, very dreadfully nervous I had been and am; but why
WILL you say that I am mad?
The disease had sharpened my senses, not destroyed, not dulled them. "

- Edgar Allan Poe
"The Tell-Tale Heart"




"Alex Garrison" <agarrison@logtech.com> on 03/07/2000 02:01:49 PM

To:   Richard Dettinger/Rochester/IBM@IBMUS
cc:
Subject:  jdbc sql cli handle leaks




Richard,

4) Alex, please send me personally an example of the behavior you are
describing if you can.  I will see to it that it gets resolved (Native JDBC
is my baby). Currently, I am not aware of any problems at all with
statement handle resources.

Thanks for offering to help us with the handle leak problem.  These CLI
traces are new to me, so it is totally possible I am misreading the
information.  However, I have attached a cli trace that shows the behavior
I was describing.  You might look at the traces around the SQLAllocHandle
for handles 352,353,354,356,359 in particular.  These are examples of
handles that get allocated immediately after an sql with an inner join that
never get freed or reused.

For example,

0000000E:327336 SQL CLI: SQLError ( 0 , 0 , 346 , SQLCHAR*, SQLINTEGER*
0000000E:329640 SQL CLI: SQLAllocHandle ( 3 , 2 , 351 )
0000000E:329712 SQL CLI: SQLSetStmtOption ( 351 , 10014 , 1 )
0000000E:331424 SQL CLI: SQLExecDirect ( 351 , select SR.SROID,RTRIM(SR
0000000E:331616 SQL CLI: SQLAllocHandle ( 4 , 2 , 352 )
0000000E:474552 SQL CLI: SQLRowCount ( 351 , SQLINTEGER* )
0000000E:474640 SQL CLI: SQLColAttributes ( 351 , 1 , 1 , SQLCHAR* , 0
0000000E:474760 SQL CLI: SQLDescribeCol ( 351 , 1 , SQLCHAR* , 30 , SQL
0000000E:474840 SQL CLI: SQLBindCol ( 351 , 1 , 4, SQLPOINTER, 5, SQLIN
0000000E:475136 SQL CLI: SQLAllocHandle ( 4 , 2 , 353 )

You can see that handle 352 and 353 get allocated here, but I will not find
them being reused anywhere later in the trace.

Alex Garrison
agarrison@logtech.com
(423)636-7213

Handles.java