SQL0805,SQLSTATE 51002 of DB2
SQL0805,SQLSTATE 51002
Certain packages like SYSLN,SYSLH require multiple copies due to thehighusage of them.
By default there are three copies made for these. Whathappens is an application is using a lot of cursors, sometimes becauseof application programming where cursors aren't being closed.By default when you bindthe db2cli.lst against your database there are 3 small dynamic packagesand 3 large dynamic packages bound into system catalog tablesyscat.packages. The number of small packages is fixed at 3, but thenumber of large packages is variable in range from the default of 3 to30, and is controlled using the bind option CLIPKG N. For the defaultof CLIPKG 3, there are 3 of each dynamic package bound to the database,for example: NULLID.SYSLH200, NULLID.SYSLH201 and NULLID.SYSLH202.The effect of this CLIPKG option is described in more detail in the CallLevel Interface Guide and Reference manual. For each small package,there are 64 handles available, while each large package contains 364handles. This means that by default each application will have (64 * 3)
+ (364 * 3), or 1284 handles available. Each prepare statement and
eachcursor that is created by the application utilizes one of thesehandles,and the handle will not be released until the prepared statement orcursor are closed. With JDBC applications, you can not guarantee whengarbage collection will occur, so you should ensure that stmt.close andcursor.close are being issued when the associated statement or cursorhas completed its operation, especially if the prepare statement and/ordeclare cursor calls are present inside a loop or a method that areexecuted multiple times during the session.Once your application has used all of the available handles, DB2 willthen attempt to access the next package name in sequence in order toaccess more handles. When your application has allocated 1284 handlesand needs a 1285th, DB2 will attempt to locate NULLID.SYSLH203 foraccess to an additional 364 handles. Unless the db2cli.lst has beenbound with at least an option of CLIPKG 4, this package will not befound, and the SQL0805N error will result.The solution, in this case, is to rebind the db2cli.lst with asufficient number of packages to be able to process the number ofhandles required by your application. You may wish to start by doublingthe default and use CLIPKG 6 as follows, which should provide you with atotal of 2376 handles for use:
db2 "bind @db2cli.lst blocking all sqlerror continue grant publicCLIPKG 6"This will then add SYSLH203, SYSLH204 and SYSLH205 to your database. Ifyou in turn use all 2376 handles, you will then find that you may obtainan SQL0805 error on SYSLH206 in turn. At that time, you may either wishto go back to the application code to ensure that all handles are beingreleased when they are no longer required, or you may wish to rebindwith yet more packages listed with the CLIPKG option. You may bind upto a maximum of 30 dynamic packages.If your application happens to use the type 4 JDBC driver from thedb2jcc.jar (using interface com.ibm.db2.jcc.DB2Driver), you will need touse an alternate method to generate the additional packages. The type 4JDBC driver uses a separate set of package names than CLI or JDBC type 2applications, and these packages are generated through the use of theDB2Binder.class. You may, therefore, accomplish the same task as aboveusing the type 4 driver syntax of:java com.ibm.db2.jcc.DB2Binder -url jdbc:db2//server:port:database -useruserID -password password -size 6Other than a change in the actual package names stored in thesyscat.packages system catalog table, the same rules apply to the -sizeoption of the DB2Binder.class as to the CLIPKG option of the db2 bindcommand.
----------------------------------
1.From the Start Menu, select Programs.
2.From the Programs Menu (or from 'All Programs' on Windows XP), select IBM DB2.
3.From the IBM DB2 menu, select "Command Line Tools".
4.From the "Command Line Tools" menu, select the DB2 Command Window.
5.Connect to the sample database by entering:
connect to sample user userid using password
where userid and password are the user ID and password of the instance where the sample database is located.
6.Bind the utilities to the database by entering:
bind ..\bnd\@db2cli.lst blocking all sqlerror continue grant public CLIPKG 10
