50 DB2 Nuggets #50: Expert Advice: Troubleshoot DB2 CLI error: SQL0245N The invocation of routine "<routine name>" is ambiguous.

Body

Hello,

Recently I worked on another interesting case where an ODBC/CLI application using SUBSTRING(..., CODEUNITS32) and LOCATE(..., CODEUNITS32) functions in case when first argument is a parameter and the function is used in WHERE or HAVING clause expression resulted in error:
[IBM][CLI Driver][DB2/NT] SQL0245N The invocation of routine "SUBSTRING" is ambiguous. The argument in position "1" does not have a best fit.
SQLSTATE=428F5.

This error is returned when an invocation of a function is ambiguous. This occurs when there are two or more possible candidate functions that satisfy the criteria for function resolution. In this case, two candidate functions have a parameter in position position, but the data types of the parameters for the two candidate functions are not in the same data type precedence list. A best fit for the argument cannot be determined.

Steps I did to reproduce the error:

db2 "create table t1 (col1 varchar(100))"
db2 "insert into t1 values ('one')"

CLI script used:
 

opt echo on
opt callerror on
opt autocommit on
sqlallochandle sql_handle_env 0 1
sqlallochandle sql_handle_dbc 1 1
sqlconnect 1 <dbname> -3 <User> -3 <pwd> -3
sqlallocstmt 1 1
SQLPrepare 1 "update t1 set col1='OK' where substring ('one11',1,3,codeunits32) = substring(?,1,3,codeunits32)" -3
getmem 1 1 sql_c_char 30
sqlbindparameter 1 1 sql_param_input sql_c_char sql_varchar 128 0 1
updatemem 1 sql_c_char value 8 "one1"
SQLExecute 1
sqltransact 1 1 SQL_COMMIT
killenv 1
 

To run:

Save the above script as testcli.txt.

From DB2 Command line:
 

db2cli < testcli.txt

Results:
> SQLPrepare 1 "update t1 set col1='OK' where substring ('one11',1,3,codeunits32) = substring(?,1,3,codeunits32)" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
> getmem 1 1 sql_c_char 30
GetMem: memory buffer 1 for statement handle 1 allocated.
> sqlbindparameter 1 1 sql_param_input sql_c_char sql_varchar 128 0 1
SQLBindParameter: rc = 0 (SQL_SUCCESS)
> updatemem 1 sql_c_char value 8 "one1"
UpdateMem: memory buffer 1 for statement handle 1 updated.
> SQLExecute 1
SQLExecute: rc = -1 (SQL_ERROR)
SQLError: rc = 0 (SQL_SUCCESS)
SQLError: SQLState     : 428F5
          fNativeError : -245
          szErrorMsg   : [IBM][CLI Driver][DB2/6000] SQL0245N  The invocation of routine "SUBSTRING" is ambiguous. The argument in position "1" does not have a best fit.  SQLSTATE=428F5

Workaround:

Based on the user response for the error message, changed the code to explicitly cast the data type for the parameter, which helped to resolve the error.

SQL0245N
User response
Change the SQL statement to explicitly cast the argument to the desired data type, the definition of a function, or the SQL path to remove the ambiguity from the set of candidate functions and try again.
sqlcode: -245

opt echo on
opt callerror on
opt autocommit on
sqlallochandle sql_handle_env 0 1
sqlallochandle sql_handle_dbc 1 1
sqlconnect 1 <dbname> -3 <User> -3 <pwd> -3
sqlallocstmt 1 1
SQLPrepare 1 "update t1 set col1='OK' where substring ('one11',1,3,codeunits32) = substring(cast(? as varchar(8)),1,3,codeunits32)" -3
getmem 1 1 sql_c_char 30
sqlbindparameter 1 1 sql_param_input sql_c_char sql_varchar 128 0 1
updatemem 1 sql_c_char value 8 "one1"
SQLExecute 1
sqltransact 1 1 SQL_COMMIT
killenv 1

But customer had a complex application where they cannot implement this code change.

Solution:

To resolve data types of untyped expressions, DB2 registry variable DB2_DEFERRED_PREPARE_SEMANTIC can be used:
DB2_DEFERRED_PREPARE_SEMANTICS
Default=NO, Values: YES or NO
When set to YES, this registry variable enables deferred prepare semantics such that all untyped parameter markers used in PREPARE
statements will derive their data types and length attributes based on the input descriptor associated with the subsequent OPEN or EXECUTE
statements. This allows untyped parameter markers to be used in more places than was supported previously.

db2set DB2_DEFERRED_PREPARE_SEMANTICS=Yes
db2stop
db2start

Now the application ran successfully without any errors.

Hope these steps help you to troubleshoot if you encounter similar error!

Please do post your feedback/comments or questions.

Thanks!

Subbulakshmi Prabhu