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?

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

1 Answer 1

Reset to default
4

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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