1

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?

  • Do you use PreparedStatement? Do you commit frequently? – mustaccio May 9 '16 at 14:37
  • 1
    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. – Ian Bjorhovde May 9 '16 at 18:39
  • @mustaccio It doesn't commit, we only use select, atleast now. – padippist May 10 '16 at 4:40
  • @IanBjorhovde I will try it – padippist May 10 '16 at 4:41
  • @IanBjorhovde - you should put that as an answer so padippist can mark it answered. – Chris Aldrich May 10 '16 at 14:04
3

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.

  • Sorry, I can't vote you up because I have note enough reputation. Any way thanks......this helped me a lot. – padippist May 11 '16 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.