All applications are not perfect. There might be instances where applications do not close their cursors after they are done with them. This causes DB2 dynamic packages to be held and eventually gets used up. \u00c2\u00a0When all the default number of packages are used up, the application will start getting -805 on NULL.SYS* packages.\u00c2\u00a0<\/p>\n
Today at my work such an incident\u00c2\u00a0occurred, first I start terminating applications that had the earliest connect timestamp and was waiting on application. This freed up some of the packages, but the application quickly used up all the free ones. \u00c2\u00a0We ended up terminating all the threads, deactivating the database and activating the database. This seems to have eliminated the \u00c2\u00a0problem (for now). \u00c2\u00a0 If I am not able to identify the cursor(s) that is causing it, my next step is to increase the number of default client packages. \u00c2\u00a0 This can be done by \u00c2\u00a0:<\/p>\n
db2 \u00e2\u20ac\u0153bind @db2cli.lst blocking all sqlerror continue grant public CLIPKG 12?<\/strong><\/p>\n This will increase the number of available packages from 3 to 12.\u00c2\u00a0By default when you bind the db2cli.lst against your database there are\u00c2\u00a03 small dynamic packages and 3 large dynamic packages bound into system\u00c2\u00a0catalog table syscat.packages. \u00c2\u00a0Applications that doesn’t close the cursors properly will use up all the three copies.\u00c2\u00a0\u00c2\u00a0The number of small packages is fixed at\u00c2\u00a03, but the number of large packages is variable in range from the\u00c2\u00a0default of 3 to 30, and is controlled using the bind option CLIPKG N.\u00c2\u00a0For the default of CLIPKG 3, there are 3 of each dynamic package bound\u00c2\u00a0to the database, for example: NULLID.SYSLN200, NULLID.SYSLN201 and\u00c2\u00a0NULLID.SYSLN202. \u00c2\u00a0Run the following query on your database to see how many SYS packages are there :<\/p>\n db2 select pkgname from syscat.packages\u00c2\u00a0where pkgname \u00c2\u00a0like \u00c2\u00a0\u00e2\u20ac\u02dcSYS%?<\/strong><\/p>\n For each small package,\u00c2\u00a0\u00c2\u00a0there are 64 handles available, while each large package contains 364\u00c2\u00a0handles. This means that by default each application will have (64 * 3)\u00c2\u00a0+ (364 * 3), or 1284 handles available. Each prepare statement and each\u00c2\u00a0 I found the following links to be useful in resolving the issue :<\/p>\n
\ncursor that is created by the application utilizes one of these handles,\u00c2\u00a0and the handle will not be released until the prepared statement or\u00c2\u00a0cursor are closed. With JDBC applications, you can not guarantee when\u00c2\u00a0garbage collection will occur, so you should ensure that stmt.close and\u00c2\u00a0cursor.close are being issued when the associated statement or cursor\u00c2\u00a0has completed its operation, especially if the prepare statement and\/or\u00c2\u00a0declare cursor calls are present inside a loop or a method that are\u00c2\u00a0
\nexecuted multiple times during the session.\u00c2\u00a0Once your application has used all of the available handles, DB2 will\u00c2\u00a0then attempt to access the next package name in sequence in order to\u00c2\u00a0access more handles. When your application has allocated all the 1284 handles\u00c2\u00a0and needs a 1285th, DB2 will attempt to locate NULLID.SYSLN203 for\u00c2\u00a0access to an additional 364 handles. Unless the db2cli.lst has been\u00c2\u00a0bound with at least an option of CLIPKG 4, this package will not be\u00c2\u00a0found, and the SQL0805N error will result. The quick solution, in this case,\u00c2\u00a0is to rebind the db2cli.lst with a sufficient number of packages to be \u00c2\u00a0able to process the number of handles required by your application. But before doing that you should first determine whether you can get your application to close the cursor properly. \u00c2\u00a0If its actually the volume thats driving the error then you should increase the number of packages.<\/p>\n