code=-805, state="51002", message="DB2 SQL error: SQLCODE: -805, SQLSTATE:

[Former Member]

Hello,

we have a portal system (nw04s, sp11) on aix 5.2.8 and db2 v8.2.2 (fixpak 10) where the activity report in the portal isn't running anymore. In the portal logfiles we can see the following error. Any idea why the portal isn't able to insert the data?

regards,

alexander

22.08.2007 08:00:43.362 ERROR 100: PreparedStatement = INSERT INTO WCR_USERSTAT (TIMESTAMPHOUR,AGGREGATIONLEVEL,HASHEDUSERID,USERTYPE,LOGONID) VALUES (? , ? , ? , ? , ?)

22.08.2007 08:00:44.131 ERROR SQL error occurred on connection b0d02t02:PP2:SAPPP2DB: code=-805, state="51002", message="DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002, SQLERR

MC: NULLID.SYSLH103 0X5359534C564C3031";

22.08.2007 08:00:44.174 ERROR Exception during aggregation

22.08.2007 08:00:44.174 ERROR Exception during aggregation.

22.08.2007 08:16:37.500 ERROR Exception ID:08:16_22/08/07_0016_14024663

[Frank-Martin · Accepted solution]

Hi Alexander,

this typically happens if an application opens a large number of PreparedStatement objects and does not close them properly . Please see note 733671 . You can try to workaround the problem by increasing the maximum number of open objects that can be handled in a connection as described in the note but typically this is an application problem.

If this is a standard SAP application please open a OSS message on the corresponding component. If this application is self written please review your code. I saw the following wrong application coding using "SAP Open SQL for Java" recently.

try

{

PreparedStatement pinsert = con.prepareStatement( "INSERT ..." );

pinsert.set...

pinsert.executeUpdate();

pinsert.close();

}

catch ( ... )

{

/* handle duprecs and other SQL errors */

....

}

In this case each ( non-severe ) SQL error like a duplicate record ( SQL0803 )leaves open an active preparedStatement object. Since "SAP Open SQL for Java" buffers such objects, they are not automatically closed or removed by the garbage collector. Thus this coding creates a preparedStatement Object leak.

The problem can be avoided by closing the Statement in a "finally" block.

Regards

Frank

[Former Member · Accepted solution]

Hello Frank,

it is a sap application. It collects user information. How many users are logged on, etc. We have a portal with 50000 defined users. Maybe we hit a maximum. The last time we report worked 1500 users were logged on in the same time. I have opened a sap call. Thanks for the help.

Alexander

[Frank-Martin]

Hi Alexander,

yes, this fits with my theory. As a default DB2 can handle about 1600 concurrently open preparedStatement Objects per connection.

Regards

Frank

[Former Member]

Hello, Alexander

See note 823019.

With best regards, Dmitry