Performance load & SQLException SQLCODE -805 SQLSTATE 51002 SQLERRMC NULLID SYSLH203

Problem description
When commerce is under high load, orders might start to fail and the following exception shows up in WebSphere logs:

[10/4/12 8:33:28:267 BST] 00000036 CommerceSrvr E DataBeanManager activate(DataBean,ViewCommandContext,HttpServletRequest,HttpServletResponse) CMN0420E: The following command exception has occurred during processing: "com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH203 0X5359534C564C3031, DRIVER=4.11.77".com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH203 0X5359534C564C3031, DRIVER=4.11.77
at com.ibm.db2.jcc.am.gd.a(gd.java:679)
at com.ibm.db2.jcc.am.gd.a(gd.java:60)
at com.ibm.db2.jcc.am.gd.a(gd.java:127)
at com.ibm.db2.jcc.am.jn.c(jn.java:2561)
at com.ibm.db2.jcc.t4.cb.o(cb.java:857)
at com.ibm.db2.jcc.t4.cb.g(cb.java:143)
at com.ibm.db2.jcc.t4.cb.a(cb.java:40)
at com.ibm.db2.jcc.t4.q.a(q.java:32)
at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
at com.ibm.db2.jcc.am.jn.ib(jn.java:1996)
at com.ibm.db2.jcc.am.kn.sc(kn.java:3058)
at com.ibm.db2.jcc.am.kn.b(kn.java:3841)
at com.ibm.db2.jcc.am.kn.fc(kn.java:702)
at com.ibm.db2.jcc.am.kn.executeQuery(kn.java:672)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:1082)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:703)
at com.ibm.commerce.base.helpers.BaseJDBCHelper.executeQuery(BaseJDBCHelper.java:381)
at com.ibm.commerce.base.objects.ServerJDBCHelperBean.executeParameterizedQueryNoCache(ServerJDBCHelperBean.java:497)
at com.ibm.commerce.base.objects.ServerJDBCHelperBean.executeParameterizedQuery(ServerJDBCHelperBean.java:447)
at com.ibm.commerce.base.objects.ServerJDBCHelperBean.executeQuery(ServerJDBCHelperBean.java:523)
at com.weare2020.catalog.databeans.ExtendedCatEntryDataBean.populate(ExtendedCatEntryDataBean.java:153)
at com.ibm.commerce.beans.DataBeanManager.directActivate(DataBeanManager.java:465)
at com.ibm.commerce.beans.DataBeanManager.activate(DataBeanManager.java:195)
at com.ibm.commerce.beans.DataBeanManager.activate(DataBeanManager.java:383)
at com.ibm.commerce.taglibs.base.tag.UseBean.doEndTag(UseBean.java:99)
at com.ibm._jsp._MiniShopCartDisplay._jspService(_MiniShopCartDisplay.java:1518)
at com.ibm.ws.jsp.runtime.HttpJspBase.service(HttpJspBase.java:99)

Problem analysis

DB2 need package to execute any prepared statement. A package is some sort of wrapper which is required to translate prepared statement into DB2 low level query format. If you have a lot of parallel connections to the databae and not closing the PreparedStatements created properly, you will be consuming the default package created quickly and DB2 will fail to find extra packages and will throw the exception above.

Fix

Execute the DB2 command below to create extra packages. The command will grant public privileges for the new packages.

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

Detailed sample steps

• Login to database server as db2inst1
• Connect to database e.g. db2 connect to wdsdev user db2inst1 using mypassword
• cd /opt/IBM/db2/V9.7/bnd
• Execute the following command
• db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 30

Remarks

• If you executed the command above without grant public, you might start getting access permission exceptions in WebSphere logs if your application connects to database using an account other than db2inst1. To resolve the issue, you will have to grant your database user execute permission package by package. Below is a sample for what needs to be done but it might be different (e.g. different schema name) in your own environment

     db2 'grant execute on package NULLID.SYSLH200 to WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH200 to WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH200 to WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH200 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH200 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH200 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH200 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH201 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH202 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH203 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH204 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH205 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH206 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH207 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH208 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH209 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH20A to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH20B to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH20C to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH20D to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH20E to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH20F to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH210 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH211 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH212 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH213 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH214 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH215 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH216 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH217 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH218 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH219 to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH21A to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH21B to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH21C to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH21D to USER WCDBUSER'
     db2 'grant execute on package NULLID.SYSLH21E to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH200 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH201 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH202 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH203 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH204 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH205 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH206 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH207 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH208 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH209 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH210 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH20A to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH20B to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH20C to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH20D to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH20E to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH20F to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH20E to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH20G to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH210 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH211 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH212 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH213 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH214 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH215 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH216 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH217 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH218 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH218 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH219 to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH21A to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH21B to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH21C to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH21D to USER WCDBUSER'
     db2 'grant execute on package NULLIDR1.SYSLH21E to USER WCDBUSER'
     db2 'grant execute on package NULLIDRD.SYSLH200 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH200 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH201 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH202 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH203 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH204 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH205 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH206 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH207 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH208 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH209 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH210 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH211 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH210 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH20A to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH20B to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH20D to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH20A to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH20D to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH20E to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH20F to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH210 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH211 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH212 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH213 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH214 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH215 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH216 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH217 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH218 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH219 to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH21A to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH21B to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH21C to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH21D to USER WCDBUSER'
     db2 'grant execute on package NULLIDRA.SYSLH21E to USER WCDBUSER'