DB2 SQL Error – SQLCODE: -805, SQLSTATE: 51002

db2jdbc

I am doing a batch process which fetches rows from table 50000 times.

The query is:

SELECT * FROM <table name>  WHERE <condition; this changes for each iteration(50000)>

This batch process is done in java and uses jdbc connection.

The error shown is:

com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002

This error happens at certain iteration (between 20000 to 30000)

I logged the query which caused the error and took this query and executed it using both external agent and java/jdbc; it worked fine.

what would be the problem?

Best Answer

Sounds like you are not properly deallocating objects like Statement, ResultSet, etc., so your application is holding open statement handles in the database. This will eventually exhaust "CLI Packages" in the database, resulting in the SQL0805N error.

The proper solution is to make sure that you close these as soon as you're done reading them.

You may also find documents suggesting that you rebind to increase the number of CLI packages at the database level, but keep in mind that this is just a band-aid.

Related Solutions

DB2 passwords with special characters

This is a long-standing bug in the GUI tools.

However, as long as your password does not contain a double quote (") character, then you can work around this by putting double quotes around the password to connect.

connect to sample user test using test!
--> SQL0104N

connect to sample user test using "test!"
--> connection succeeded.

This method will also work if the GUI tools prompt you for a password with a dialog box.

If you have passwords with double quotes in them, you may be out of luck. You could open a ticket with IBM DB2 support and/or just change the password.

DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query

This question is somewhat old, so you may have already figured out your problem, but if I may offer an alternative...

It may be better to use a MERGE statement in this situation (click the link for the publib entry):

MERGE INTO mySchema.myTable tab USING (
        VALUES ('abc', '123')
    ) AS merge (C1, C2)
    ON  tab.column1 = merge.C1
    AND tab.column2 = merge.C2
    WHEN MATCHED THEN
        IGNORE
    WHEN NOT MATCHED THEN 
        INSERT (column1, column2)
        VALUES (merge.C1, merge.C2)

This will take the columns in the "fake" table merge, and compare them using the keys in the ON clause, and if there is not a match, then it will use the INSERT statement. If there is a match, that row will just be ignored.

However, the availability of MERGE depends on your platform. I'm fairly certain MERGE has been in Linux/Unix/Windows DB2 since v8 (although, you can only use PREPARE'd merges since 9.7), and it was added in z/OS DB2 in v9.1. I don't know about the other platforms (AS/400, etc.).

Best Answer

Related Solutions

DB2 passwords with special characters

DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query

Related Question