DB2 FOR LUW / DB2FORLUW / A10 - DB2

When CURSORHOLD=1 (which is the default) and 
STMTCONCENTRATOR=LITERALS is set in the db2cli.ini file, an 
SQL0644N error may occur when a select statement is ran followed 
by another statement that is not a select statement.  The WITH 
HOLD clause is being incorrectly appended to the subsequent 
non-select statement and is causing the SQL0644N error. 
 
The following is an example of the error. 
 
quickc 1 1 sample 
SQLAllocHandle SQL_HANDLE_STMT 1 1 
SQLExecDirect 1 "SELECT * FROM DEPT where deptno = 'A00'" 
SQL_NTS 
fetchall 1 
SQLFreeStmt 1 SQL_UNBIND 
SQLFreeStmt 1 SQL_RESET_PARAMS 
SQLFreeStmt 1 SQL_CLOSE 
SQLTransact 1 1 SQL_COMMIT 
SQLFreeStmt 1 SQL_UNBIND 
SQLFreeStmt 1 SQL_RESET_PARAMS 
SQLFreeStmt 1 SQL_CLOSE 
SQLExecDirect 1 "LOCK TABLE DEPT IN EXCLUSIVE MODE" SQL_NTS 
SQLGetDiagRec SQL_HANDLE_STMT 1 1 
 
Output: 
 
$ db2cli < script.txt 
IBM DATABASE 2 Interactive CLI Sample Program 
(C) COPYRIGHT International Business Machines Corp. 1993,1996 
All Rights Reserved 
Licensed Materials - Property of IBM 
US Government Users Restricted Rights - Use, duplication or 
disclosure restricted by GSA ADP Schedule Contract with IBM 
Corp. 
> SQLAllocEnv: rc = 0 (SQL_SUCCESS) 
             CLI henv = 1, Test Driver henv = 1 
SQLAllocConnect: rc = 0 (SQL_SUCCESS) 
             CLI hdbc = 1, Test Driver hdbc = 1 
SQLConnect: rc = 0 (SQL_SUCCESS) 
> SQLAllocHandle: rc = 0 (SQL_SUCCESS) 
              CLI handle = 1, Test Driver handle = 1 
> SQLExecDirect: rc = 0 (SQL_SUCCESS) 
> FetchAll:  Columns: 5 
  DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION 
  A00, SPIFFY COMPUTER SERVICE DIV., 000010, A00, - 
FetchAll: 1 rows fetched. 
> SQLFreeStmt: rc = 0 (SQL_SUCCESS) 
> SQLFreeStmt: rc = 0 (SQL_SUCCESS) 
> SQLFreeStmt: rc = 0 (SQL_SUCCESS) 
> SQLTransact: rc = 0 (SQL_SUCCESS) 
> SQLFreeStmt: rc = 0 (SQL_SUCCESS) 
> SQLFreeStmt: rc = 0 (SQL_SUCCESS) 
> SQLFreeStmt: rc = 0 (SQL_SUCCESS) 
> SQLExecDirect: rc = -1 (SQL_ERROR) 
> SQLGetDiagRec: rc = 0 (SQL_SUCCESS) 
SQLGetDiagRec: SQLState     : 42615 
          fNativeError : -644 
          szErrorMsg   : [IBM][CLI Driver][DB2/6000] SQL0644N 
Invalid value specified for keyword "WITH" in statement 
"<ATTRIBUTE-STRING>".  SQLSTATE=42615 
 
          cbErrorMsg   : 132 
> 
Memory count is 45

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v10.1 Fixpack 3                               * 
****************************************************************

If the application does not require cursorhold=1 behavior, 
cursorhold=0 can be added to the db2cli.ini file and will allow 
STMTCONCENTRATOR=LITERALS to work correctly.  Please read the 
cursorhold documentation at the following link to understand the 
implications of setting cursorhold=0. 
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2. 
luw.apdv.cli.doc/doc/r0008781.html

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

First fixed in DB2 v10.1 Fixpack 3

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

22.01.2013
03.10.2013
03.10.2013