-805 in DB2 for z/Os

Hi!

I'm trying to run a Spring Batch over a DB2 for z/Os database. All runs pretty well at the begining but when the number of records increases an -805 error appers.
The exception looks like this:
DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002, SQLERRMC: CAGDSNP.NULLID.SYSLH203.5359534C564C3031;DISTSERV; 04

I've tried changing the commit interval but there was no difference. Then I talk with de dba and they did a bind on package SYSLH203. Then, job holds a little bit more but crashed again with SYSLH204 package. Actually, I have no idea about DB2 administration, but the dba says that it's something that Spring Batch is doing wrong, but I disagree.

If anyone can brought me some light about this, it would be very very appreciated.

The amount of SQL Queries are about 2.000 if it helps.


Thanks in advance.

Is it potentially related to this issue?

http://jira.springframework.org/browse/BATCH-820

It's fixed in 1.1.3 (which was released today)

Don't seems to be related.
I've been googling and think the problem is with the transaction isolation levels.
According with the DB2 java manual, it creates a package for every isolation and cursor holdability which makes a 4x3 possibilities = 12 packages.
What I don't understand is how isolation or cursor holdability is changing to reach the fourth package.
In omegamon I can see how packages changes from 200 to 203 and then crashes with a message about the 204.

I’m still stuck with that problem. In this link: www-01.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=SYSLH203&uid=s wg21205020&loc=en_US&cs=utf-8&lang=en appears some information. It’s seems related to the fact that there’s a query limit for connection. Due to Hibernate is not closing the connection, the limit is reached quite fast. I’m simply try with two datasources to open two connections and it works. But this solution is not enough to leave me deploy my batch in a production environment.

If anyone reach that post due to this error I've found the solution. Thanks god.

You can look at this url to learn a few things about Db2 packages: http://raikar.wordpress.com/db2-pack...ndle-sql0805n/

And the conclusion I extract of this read is that in some place I'm not closing the Statements. How if I'm using Hibernate and Spring? Easy. I'm using a XA connection pool from http://xapool.experlog.com/ . This pool has a funny parameter: preparedStmtCacheSize
With a value in this parameter greater than zero, the datasource didn't close prepared statements. Setting this parameter to 0 and all runs pretty well.

I deserve a whisky.

Finally I found the problem.

The link from ibm I've posted was clear, in somewhere the aplication was not closing the statements.

The problem was in a opensource datasource pool that we are using. This pool does prepared statements cache, but It never reuses the statements neither close them. Cause of that the connection reach the limit of statements quickly and throws that error.

Long story short. -805 = statements not closed.