Error Code =-805 Sqlstate=51002
Contents |
Technote (troubleshooting) This document applies only to the following language version(s): English Problem(Abstract) The error message -805 sqlstate 51002 package not found was received from an application stating that the package SYSLH21E was
Out Of Package Error Occurred
not found. Symptom The following are examples of the symptom that could be observed. SQL0805N Package "SYSLH21E"
Db2 Sql Error Sqlcode=-805
not found. com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002, SQLERRMC: DB2PROD.NULLID.SYSLH21E.5359534C564C3031;DISTSERV;04 DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002, SQLERRMC: NULLID.SYSLH21E Cause The SQL0805 NULLID.SYSL[HN]xyy error normally shows
Sqlcode: -805, Sqlstate: 51002
up when an application runs out of statement handles. The number of statement handles available to a CLI application depends on the number of large packages the application has defined and is limited by overall system resources. By default, 3 small and 3 large packages are created. Each small package allows a maximum of 64 com.ibm.db2.jcc.am.sqlexception: db2 sql error: sqlcode=-805, sqlstate=51002 statement handles per connection, and each large package allows a maximum of 384 statements per connection, giving a total of 1,344 statement handles. If your application requires more than 1,344 handles, you will need to increase the number of large packages by setting the CLIPKG keyword up to a value up to 30, which you have already done. Some definitions for the package name SYSL[HN]xyy: [HN] - One or the other. H means with hold, N means without hold. x - Isolation level: 0=NC, 1=UR, 2=CS, 4=RS, 8=RR yy - Package iteration 00 through FF. For yy, the max is 1D, which is hex for 29. This means that you can have a maximum of 30 (packages are numbered 0-29) large packages, for a grand total of (64*3)+(384*30)=11,712 statement handles. The message received is error for package SYSLH21E. Thus the cause of the SQL0805N error is when the application is trying to use a CLIPKG value greater than 30 which is 1E in hex.
Java application and how to increase it? Technote (FAQ) Question When a DB2 java application is executing a lot of concurrent SQL statements, 51002 sqlstate return code it may hit the limit reporting "Out of Package Error Occurred" with db2 clipkg the SQLCODE -805. If you encounter this error, You may ask what the limit is for the number sqlcode 818 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 http://www.ibm.com/support/docview.wss?uid=swg21366855 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 http://www-01.ibm.com/support/docview.wss?uid=swg21670200 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, SQLST
your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the http://www.dbforums.com/showthread.php?1668878-Db2-quot-sqlcode-805-sqlstate-51002-sqlerrmc-Nullid-quot forum that you want to visit from the selection below. Results 1 to 10 of 10 Thread: Db2 "sqlcode: -805, sqlstate: 51002, sqlerrmc: Nullid" Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 08-04-11,03:52 #1 mel123 View Profile View Forum Posts Registered User Join Date Aug 2011 Posts 5 Unanswered: Db2 "sqlcode: -805, sqlstate: 51002, db2 sql sqlerrmc: Nullid" We are running the following: AIX 5.3 WAS 6.0, WAS ND 6.0.2.17, WCC 6.5 DB2 v8.1.1.160 (fix pack 18) We get the following error: ############ Error code: 200000100 Error Message: Reading an extension Person failed Throwable Message: java.lang.Exception: Exception while retrieving Alert Objects com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002, SQLERRMC: NULLID.SYSLN21E 0X5359534C564C3031 com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002, SQLERRMC: NULLID.SYSLN21E 0X5359534C564C3031 We have: 1. reviewed all application db2 sql error code and confirmed all open statements have a corresponding close statement 2. our configuration for number of pkgs is at the maximum 3. been advised this is fixed in v9.2 (upgrading is not an option at this point in time) Any ideas? Reply With Quote 08-04-11,04:41 #2 przytula_guy View Profile View Forum Posts Visit Homepage Registered User Join Date Apr 2006 Location Belgium Posts 2,514 Provided Answers: 11 been advised this is fixed in v9.2 (???? this is not a problem : please read the doc and find the sql code ..... -805 just means : package missing have a look at doc and execute the bind for needed db2 packages IBM DB2 9.5 Information Center for Linux, UNIX, and Windows this is common for all versions Best Regards, Guy Przytula Database Software Consultant Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs. Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence. DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop.. Information Server Datastage Certified http://www.infocura.be Reply With Quote 08-04-11,04:59 #3 mel123 View Profile View Forum Posts Registered User Join Date Aug 2011