DB2 FOR LUW / DB2FORLUW / 970 - 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 v9.7 Fix Pack 8                               * 
****************************************************************

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 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in DB2 v9.7 Fix Pack 8

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC89720 
follow-up : 

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

19.10.2012
19.04.2013
19.04.2013

9.7.FP8