How many concurrently running statements allowed for a DB2 Java application and how to increase it?

Question

When a DB2 java application is executing a lot of concurrent SQL statements, it may hit the limit reporting "Out of Package Error Occurred" with the SQLCODE -805. If you encounter this error, You may ask what the limit is for the number of concurrent statements for a DB2 Java application and how to increase it.

Answer

When a DB2 Java application is running a dynamic SQL statement, it uses a dynamic section from DB2 CLI packages at DB2 server side to prepare/execute the statement. Please note that these DB2 CLI packages are exactly the same packages used by a CLI application.
By default, there are 3 large CLI packages (containing 385 sections) and 3 small CLI packages (containing 65 sections) bound at a DB2 database server. There are two sections from each package (both large and small) which are reserved for positioned update/delete statements and execute immediate statements. Therefore the total number of available sections for all other statements by default is (3 * 63) + (3 * 383) = 1338, which means by default a DB2 Java application can only run 1338 dynamic statements at one time.

When this limit is hit, the application will receive an error of "Out of Package Error Occurred" with SQLCODE -805, which means the DB2 server was running out of dynamic sections available from the CLI packages.

For example, running below Java application would hit the limit and report the error as below:


PreparedStatement pStmt = null;
for (int i=1; i<=1339; i++) {
pStmt = db.con.prepareStatement("insert into myt values (1, 'name1')");
pStmt.execute();
}


***** Out of Package Error Occurred (2014-05-14 22:20:32.431) *****

Exception stack trace:
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH204 0X5359534C564C3031, DRIVER=3.64.106
com.ibm.db2.jcc.am.bd.a(bd.java:682)
com.ibm.db2.jcc.am.bd.a(bd.java:60)
com.ibm.db2.jcc.am.bd.a(bd.java:127)
com.ibm.db2.jcc.am.io.c(io.java:2706)
com.ibm.db2.jcc.t4.ab.p(ab.java:872)
com.ibm.db2.jcc.t4.ab.h(ab.java:144)
com.ibm.db2.jcc.t4.ab.b(ab.java:41)
com.ibm.db2.jcc.t4.p.a(p.java:32)
com.ibm.db2.jcc.t4.qb.i(qb.java:135)
com.ibm.db2.jcc.am.io.gb(io.java:2112)
com.ibm.db2.jcc.am.jo.rc(jo.java:3526)
com.ibm.db2.jcc.am.jo.b(jo.java:3976)
com.ibm.db2.jcc.am.jo.hc(jo.java:2732)
com.ibm.db2.jcc.am.jo.execute(jo.java:2715)
DbConn2.main(DbConn2.java:74)

Concurrently open statements:
1. SQL string: INSERT INTO MYT VALUES (1, 'NAME1')
Number of statements: 1339
********************

DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH204 0X5359534C564C3031, DRIVER=3.64.106


Then what to do if you encounter above errors?

1) firstly you need to check if you can modify the application to avoid running so many dynamic SQL statements at the same time. 

2) If you can't avoid it, you will need to increase the number of available sections by increasing the number of DB2 CLI packages. 

Please note that only the number of DB2 CLI large packages can be increased, not for small packages, and the maximum number of large packages you can increase to is 30, which also basically means the maximum available sections would be (3 * 63) + (30 * 383) = 11679.

3) To increase the number of large CLI packages, you can run DB2 bind command with CLIPKG option via the instance owner as following example:

cd ~/sqllib/bnd
db2 connect to SAMPLE

--CLPKG 10, means it will increase the number of large CLI packages to 10.
db2 "bind @db2cli.lst CLIPKG 10 grant public blocking all"

--You can then check the number of CLI large packages per isolation and cursor holdability, where you will see the number would be 10 now.

db2 "list packages for all"

db2 terminate

http://www-01.ibm.com/support/docview.wss?uid=swg21670200&myns=swgimgmt&mynp=OCSSEPGG&mync=E