2

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

1 Answer 1

Reset to default
5

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. Commented May 11, 2016 at 18:07

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.