Hi !

My dev team is getting this error when they run big load program :
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0601E Invalid
statement handle or statement is closed. SQLSTATE=S1000
..
The application runs fine when they run small loads. I checked the Db2
client FP..its on DV2 V8 FP10.

Any input is greatly appreciated.


Thanks

Roger wrote:

> My dev team is getting this error when they run big load program :
> COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0601E Invalid
> statement handle or statement is closed. SQLSTATE=S1000
> .
> The application runs fine when they run small loads. I checked the Db2
> client FP..its on DV2 V8 FP10.

Wild guess: another thread retrieves a statement from the connection in
the meantime and executes it or there is only one statement handle and a
different threads uses this handle from time to time.

Bernd

--
What do you get when you cross a mail pigeon with a parrot ? Voice mail

I don't think thats the case. We are running the insert process by
itself.


Bernd Hohmann wrote:
> Roger wrote:
>
> > My dev team is getting this error when they run big load program :
> > COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0601E Invalid
> > statement handle or statement is closed. SQLSTATE=S1000
> > .
> > The application runs fine when they run small loads. I checked the Db2
> > client FP..its on DV2 V8 FP10.
>
> Wild guess: another thread retrieves a statement from the connection in
> the meantime and executes it or there is only one statement handle and a
> different threads uses this handle from time to time.
>
> Bernd
>
> --
> What do you get when you cross a mail pigeon with a parrot ? Voice mail

Is there a connection pooling mechanism? Could something be timing out
on big loads and cutting the connection?

Roger wrote:
> I don't think thats the case. We are running the insert process by
> itself.
>
>
> Bernd Hohmann wrote:
>> Roger wrote:
>>
>>> My dev team is getting this error when they run big load program :
>>> COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0601E Invalid
>>> statement handle or statement is closed. SQLSTATE=S1000
>>> .
>>> The application runs fine when they run small loads. I checked the Db2
>>> client FP..its on DV2 V8 FP10.
>> Wild guess: another thread retrieves a statement from the connection in
>> the meantime and executes it or there is only one statement handle and a
>> different threads uses this handle from time to time.
>>
>> Bernd
>>
>> --
>> What do you get when you cross a mail pigeon with a parrot ? Voice mail
>

Roger wrote:

> I don't think thats the case. We are running the insert process by
> itself.

In this case: define "big load program".... If the connection idles for
a long while, it will be closed by the tcp/ip stack as every other
tcp/ip connection.

We had this case a while ago. Either you invent a ping-thread using the
same connection (which won't work here because you're waiting for the
result) or tune the timeout settings of the workstations tcp/ip stack.

Bernd

--
What do you get when you cross a mail pigeon with a parrot ? Voice mail

I am not using connection pooling. Runs on a type 4 driver. It runs for
1 hr and then gets the error



Bernd Hohmann wrote:
> Roger wrote:
>
> > I don't think thats the case. We are running the insert process by
> > itself.
>
> In this case: define "big load program".... If the connection idles for
> a long while, it will be closed by the tcp/ip stack as every other
> tcp/ip connection.
>
> We had this case a while ago. Either you invent a ping-thread using the
> same connection (which won't work here because you're waiting for the
> result) or tune the timeout settings of the workstations tcp/ip stack.
>
> Bernd
>
> --
> What do you get when you cross a mail pigeon with a parrot ? Voice mail

Roger wrote:
> I am not using connection pooling. Runs on a type 4 driver. It runs for
> 1 hr and then gets the error

1hr without any traffic from or to the database?

Bernd

--
What do you get when you cross a mail pigeon with a parrot ? Voice mail

Roger wrote:

> I am not using connection pooling. Runs on a type 4 driver. It runs for
> 1 hr and then gets the error

Activate a CLI trace and see what is going on with your statement handles.

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

thanks a trace revealed bug in the app that had close statement.

Knut Stolze wrote:
> Roger wrote:
>
> > I am not using connection pooling. Runs on a type 4 driver. It runs for
> > 1 hr and then gets the error
>
> Activate a CLI trace and see what is going on with your statement handles.
>
> --
> Knut Stolze
> DB2 z/OS Utilities Development
> IBM Germany

Roger wrote:
> thanks a trace revealed bug in the app that had close statement.
>
> Knut Stolze wrote:
>> Roger wrote:
>>
>>> I am not using connection pooling. Runs on a type 4 driver. It runs for
>>> 1 hr and then gets the error

Technically speaking you were using legacy App driver - since your CLI trace was
captured.
DB2 JCC Universal Driver in T2 (Type 2) connectivity does not use CLI layer -
but uses DB2 Client code.
DB2 JCC Universal Driver in T4 (Type 4) connectivity does not use CLI layer and
does not need DB2 client code installed.

Jan M. Nelken