DB2 FOR LUW / DB2FORLUW / 970 - DB2

When an application calls the CLI (Call Level Interface) API 
functions SQLExecDirect, SQLPrepare or SQLNativeSql, it will 
trap if both of the following conditions are true: 
1) The SQL statement which you pass to CLI API function contains 
an ODBC (Open Database Connectivity) vendor escape clause; and: 
2) The function call in the ODBC vendor escape clause contains 
doubly nested calls to ODBC extended scalar functions that are 
not in an ODBC vendor escape clause. 
 
An ODBC vendor escape clause is a call to an ODBC extended 
scalar functions that is contained within 
curly brackets ("{ fn ...}"). 
 
For example, with a database called APARTEST, if you run the 
following script, the db2cli command traps. 
That is because the SQL statement that is an argument of 
SQLNativeSql contains an ODBC vendor escape clause (to 
callfunction ABS) which contains doubly nested calls to function 
ABS that are not in an ODBC vendor escape clause. 
 
~/sqllib/samples/cli/db2cli <<! 
QuickConnect 10 20 apartest 
SQLNativeSql 20 "select {fn abs(abs(abs(6)))} from 
sysibm.sysdummy1" -3 500 
SQLError 0 20 SQL_NUL_HSTMT 
! 
 
 
But if you run the following script, the db2cli command does not 
trap, because all of the function calls, including the function 
calls that are nested within the outermost function call, are in 
an ODBC vendor escape clause. 
 
~/sqllib/samples/cli/db2cli <<! 
QuickConnect 10 20 apartest 
SQLNativeSql 20 "select {fn abs({fn abs({fn abs(6)})})} from 
sysibm.sysdummy1" -3 500 
SQLError 0 20 SQL_NUL_HSTMT 
! 
 
 
And if you run the following script, the db2cli command does not 
trap, because there is no ODBC vendor escape clause at all. 
 
~/sqllib/samples/cli/db2cli <<! 
QuickConnect 10 20 apartest 
SQLNativeSql 20 "select abs(abs(abs(6))) from sysibm.sysdummy1" 
-3 500 
SQLError 0 20 SQL_NUL_HSTMT 
!

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of DB2 for Linux, UNIX and Windows who run             * 
* applications that use CLI (Call Level Interface)             * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* .                                                            * 
****************************************************************

As a workaround, you can either: 
1) remove the ODBC vendor escape clause, or: 
2) ensure that all the nested calls to extended scalar functions 
are in an ODBC vendor escape clause.

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 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
 

Problem was first fixed in Version 9.7 Fix Pack 5. 
At a minimum, this fix should be applied on the client.

As a workaround, you can either: 
1) remove the ODBC vendor escape clause, or: 
2) ensure that all the nested calls to extended scalar functions 
are in an ODBC vendor escape clause.

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

13.06.2011
17.01.2012
17.01.2012

9.7.FP5