Resolving DB21034E SQLSTATE=42601

Problem

Sample SQL

insert into srchattrprop (srchattr_id, propertyname, propertyvalue) values (112, ‘facet’, ‘price_EGP:{* 5} 5;{5 10} 10;{10 15} 15;{15 20} 20;{20 25} 25;{25 30} 30;{30 40} 40;{40 50} 50;{50 60} 60;{60 70} 70;{70 80} 80;{80 90} 90;{90 100} 100;{100 110} 110;{110 120} 120;{120 135} 135;{135 150} 150;{150 165} 165;{165 180} 180;{180 200} 200;{200 220} 220;{220 240} 240;{240 250} 250;{250 275} 275;{275 300} 300;{300 320} 320;{320 335} 335;{335 350} 350;{350 375} 375;{375 400} 400;{400 415} 415;{415 430} 430;{430 450} 450;{450 475} 475;{475 500} 500;{500 550} 550;{550 600} 600;{600 650} 650;{650 700} 700;{700 750} 750;{750 800} 800;{800 850} 850;{850 900} 900;{900 *}’);

DB21034E  The command was processed as an SQL statement because it was not avalid Command Line Processor command.  During SQL processing it returned:SQL0104N  An unexpected token "850" was found following "0 800} 800;{800850}".  Expected tokens may include:  ", <rvc_element> )".  SQLSTATE=42601

Fix

1. ; is used by default as a termination character for queries in DB2 control center. You need to change the termination character to something else that doesn't conflict with your insert statement.
2. Make sure you are using an appropriate single quote to enclose your strings. It happens a lot to see SQLs using ‘ (e.g. Grave Accent) instead of ' (Single Quote). They look similar but first one isn't a proper single quote to enclose your query and it will cause odd errors which are hard to trace as the one above.

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'

SQLCode=-302, SQLSTATE=2200

Exception:

Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.11.77

      at com.ibm.db2.jcc.am.gd.a(gd.java:668)

      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.b(jn.java:2230)

      at com.ibm.db2.jcc.am.jn.c(jn.java:2213)

      at com.ibm.db2.jcc.t4.cb.k(cb.java:369)

      at com.ibm.db2.jcc.t4.cb.a(cb.java:61)

      at com.ibm.db2.jcc.t4.q.a(q.java:50)

      at com.ibm.db2.jcc.t4.sb.b(sb.java:226)

      at com.ibm.db2.jcc.am.kn.oc(kn.java:2930)

      at com.ibm.db2.jcc.am.kn.b(kn.java:3504)

      at com.ibm.db2.jcc.am.kn.b(kn.java:4047)

      at com.ibm.db2.jcc.am.kn.gc(kn.java:743)

      at com.ibm.db2.jcc.am.kn.executeUpdate(kn.java:722)

Fix

Cause: SQLCode=-302, SQLSTATE=22001 is related to column data size smaller than the data you are trying to insert. You need to carefully investigate your SQL statement and make sure the data will fit in the columns.

Hint: 
I faced this when I added a new WebSphere Commerce order status which I called "PRG" and despite the column status can accommodate three characters, I was using a custom method to update order status which was changing order items status too and status column in OrderItems table is 1 character only.

According to IBM support, it is described as follows:

"Character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a host variable, because it is too small."

Create trigger from DB2 control center command editor

When creating a trigger from DB2 control center command editor, make sure to change the statement terminator character from semicolon (which is default) to something else e.g. “,”. The semicolon is used to separate trigger SQL statements and using it as a terminator character will cause a problem in interpreting the trigger SQL statements.

If you don't change it you can see the following errors when trying to create a trigger:

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0104N An unexpected token "END-OF-STATEMENT" was found following

"get_store_id integer". Expected tokens may include: "<psm_semicolon>".

LINE NUMBER=6. SQLSTATE=42601

and/or

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".

Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

You can easily change it within the command editor from the small text box at the bottom of the editor as shown below.

DB2 Control Center

DB2 Sample restore command using redirect option

The sample DB2 commands below shows you how to restore a database to a different server which doesn't have the tablespaces created (five in our example) or in case you need to change the tablespace directory structure. For more information, check the following URL http://www.ibm.com/developerworks/data/library/techarticle/0211melnyk/0211melnyk.html

db2 RESTORE DATABASE wc7dev FROM "D:\DatabaseBackup" TAKEN AT 20110624110215 ON "C:" INTO WCDEV REDIRECT

db2 list tablespaces show detail

mkdir C:\DB2\NODE0000\WCDEV\T0000003

mkdir C:\DB2\NODE0000\WCDEV\T0000004

mkdir C:\DB2\NODE0000\WCDEV\T0000005

mkdir C:\DB2\NODE0000\WCDEV\T0000006

mkdir C:\DB2\NODE0000\WCDEV\T0000007

db2 set tablespace containers for 3 using (path 'C:\DB2\NODE0000\WCDEV\T0000003')

db2 set tablespace containers for 4 using (path 'C:\DB2\NODE0000\WCDEV\T0000004')

db2 set tablespace containers for 5 using (path 'C:\DB2\NODE0000\WCDEV\T0000005')

db2 set tablespace containers for 6 using (path 'C:\DB2\NODE0000\WCDEV\T0000006')

db2 set tablespace containers for 7 using (path 'C:\DB2\NODE0000\WCDEV\T0000007')

db2 RESTORE DATABASE wc7dev continue

DB2 GetExistingInstallNameCA setup wizard

Problem:

DB2 installation fails and the installation log contains the following error message:

GetExistingInstallNameCA

The DB2 setup wizard is unable to obtain the installation name of the product

Resolution:

There was a previous DB2 product installed on the machine and was manually removed or un-installed with errors.

You need to clear up the previous installation correctly to be able to proceed with new installation. To do so, you need to use the command “db2unins -f” which forces the un-installation of all DB2 products installed on the machine including cleaning up the registry. The command is available on the source media.

Hint:

• If the command terminates with an error on Windows server, then logout from the Domain (if you are using one) and login with a local Administrator account and try it again. It will work properly this time.

DB2 A communication error occurred ERRORCODE=-4499

Problem:

A Java program which connects to database to extract data from different tables, and after some time of operation, crashes with the stack trace shown below:

[jcc][t4][2030][11211][3.57.82] A communication error occurred during operations on the connection's underlying socket, socket input stream, or socket output stream. Error location: Reply.fill(). Message: Connection reset. ERRORCODE=-4499, SQLSTATE=08001

Resolution:

The problem was fixed once I changed the code to close the connection in final block. I was only closing the statement but not the connection itself. This is reminder of type of problems one might face if doesn't follow the simplest Java & JDBC coding best practices.

DB2 java.sql.SQLException: No suitable driver

Problem:

The following line of code throws the exception below:

url = "jdbc:db2//" + server + ":" + port + "/" + database;

Class.forName("com.ibm.db2.jcc.DB2Driver");

connection = DriverManager.getConnection(url, username, password);

java.sql.SQLException: No suitable driver

at java.sql.DriverManager.getConnection(DriverManager.java:559)

at java.sql.DriverManager.getConnection(DriverManager.java:189)

Resolution:

1-After investigation, I realized this is not caused by missing JARs (as I already added them all) but because the url is not properly formatted. The correct one should be:

url = "jdbc:db2://" + server + ":" + port + "/" + database;

2-The same problem happens too if you omit the line of code below:
Class.forName("com.ibm.db2.jcc.DB2Driver");

So whenever you face this exception, check the syntax of the url connecton and make sure you are loading the appropriate class for your driver before establishing the connection.

For more information, check the following link http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.java.doc/doc/tjvjcccn.htm

DB2 java.lang.ClassNotFoundException: com.ibm.db2.jcc.DB2Driver

This problem happens when you don't have the db2jcc.jar and db2jcc_license_cu.jar in your CLASSPATH. Once added, problem will be resolved. How to add them to your CLASSPATH depends on your application and how you execute it. For example, if it is a Java application that you execute from within Rational application developer (for example as a JUnit), or from Eclipse, then you need to add them from “Run Configuration” as clicking “Add JARs” or “Add External JARs”.

RAD Run Configuration