SQL0805N on DB2 package SYSLH203

If you get a SQL0805N on package SYSLH203 then an application is holding a large number of statements open. If this is not an application bug you can bind more packages to allow for more statements open at the same time.

Depending on the type of statement you are executing, DB2 will use a particular package on the server. By default, DB2 creates three packages for each type of package. In this case NULLID.SYSLH2yy is reserved for statements with CURSORHOLD on and isolation level Cursor Stability. The package SYSLH203 means that DB2 is looking for the 4th package (200 is 1st, 201 is 2nd, etc) of this type, but it does not exist. You can create more packages on the server by connecting to the database and issuing the following bind command from the /sqllib/bnd directory:

db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 5

Note: CLIPKG 5 will create 5 large packages, and will give you the package that your application is looking for, as well as one more in this case. This setting only applies to large packages (containing 384 sections). The number of small packages (containing 64 sections) is 3 and cannot be changed.

To bind more CLI packages do the following:

• Find a machine that has the version and fixpack of DB2 that you want to bind. You can map to the machine you're going to bind on and then:
  - Go to the D:\SQLLIB\BND directory in the DB2 Command Line Processor (DB2CMD)
  - Look for the *.BND files in the D:\SQLLIB\BND directory
  - Connect to the host via DB2 connect statement like: db2 connect to dbalias user userid
  - X:\sqllib\bnd>db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 30 to bind the max of 30 CLI packages. Range is 4 to 30.

DB2 CREATE INDEX abended with S04E 00E70005

There is a couple of news items attached to this post:
1. Today is the first anniversary of this blog
2. This is the 50th post

Well, now into the actual blog item ...

My colleague was trying to drop an existing UNIQUE & CLUSTER index and recreate it after adding 3 new columns at the end of the table, with one of the new columns added to the index. However, the DDL failed with S04E reason code 00E70005.

DB2 messages and codes manual had this info on this reason code: "A relational data system (RDS) subcomponent internal inconsistency was detected". It didn't make any sense. We suspected data issue first, so we REORGed the tablespace, and tried to create the index, but that didn't fix the problem. Next, we tried to repair the DBD using this JCL:

//*===============================================
//* Start the database in UT mode
//*===============================================
//STEP0010   EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB    DD DSN=SYS2.DB2x.DSNLOAD,DISP=SHR
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//LISTING    DD SYSOUT=*
//SYSUDUMP   DD DUMMY
//SYSTSIN    DD *
  DSN SYSTEM(DB2x)
  -START DB(database) ACCESS(UT)
  END
/*
//*===============================================
//* Run REPAIR utility on the DBD
//*===============================================
//STEP020 EXEC PGM=DSNUTILB,PARM='DB2x,utilid'
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD  *
  REPAIR DBD REBUILD DATABASE database
/*
//*===============================================
//* Start the database in RW mode
//*===============================================
//STEP0030   EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB    DD DSN=SYS2.DB2.DB2x.DSNLOAD,DISP=SHR
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//LISTING    DD SYSOUT=*
//SYSUDUMP   DD DUMMY
//SYSTSIN    DD *
  DSN SYSTEM(DB2x)
  -START DB(database) ACCESS(RW)
  END
/*
 

Even that didn't fix the problem. We were not very keen to open a ticket with IBM, since we wanted to fix the problem fast rather than debugging it. So as a last resort we dropped and recreated the tablespace (off course, we had saved the data). That fixed the problem. We don't know what caused it, but the problem is fixed !! I really hate when I don't know what caused the problem, and the fix is based trial-and-error method.