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.

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."

DB2: SQL statement to delete records older than x number of months

To delete records older than 13 months from table ACTION_LOG, execute the following SQL:

 DELETE  FROM SERVICES.ACTION_LOG WHERE TIMESTAMPDIFF(64,CHAR((CURRENT TIMESTAMP -TIMESTAMP))) > 13

 

For more information on TIMESTAMPDIFF, check the following article on developerWorks Fun with Date & Time