DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002

Question

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?

• mustaccio: Do you use PreparedStatement? Do you commit frequently?
• Ian Bjorhovde: Sounds like you are not properly deallocating objects like Statement, ResultSet, etc., so your application is holding open statement handles in the database. Make sure that you close these as soon as you're done reading them.
• padippist: @mustaccio It doesn't commit, we only use select, atleast now.
• padippist: @IanBjorhovde I will try it
• Chris Aldrich: @IanBjorhovde - you should put that as an answer so padippist can mark it answered.

Answer 1

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.

• padippist: Sorry, I can't vote you up because I have note enough reputation. Any way thanks......this helped me a lot.