DB2 FOR LUW / DB2FORLUW / 910 - 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 call 
function 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.1 Fix Pack 11  for Linux, UNIX and Windows
 DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows
 

Problem was first fixed in Version 9.1 Fix Pack 11. 
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.

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

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

08.06.2011
20.01.2012
20.01.2012

9.1.FP11