|
Problem IC71942
|
Status: Closed |
SQL COMPILATION FAILS WITH SQL0401N OR SQL0440N WHEN QUERY USES BUILT-IN FUNCTION WITH STRING UNIT |
| product: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problem description: |
A query may fail to compile with SQL0401N or SQL0440N when the
query uses one of the following built-in string manipulation
functions:
SYSIBM.CHARACTER_LENGTH
SYSIBM.INSERT
SYSIBM.LCASE
SYSIBM.LEFT
SYSIBM.LENGTH
SYSIBM.LOCATE
SYSIBM.LOWER
SYSIBM.OVERLAY
SYSIBM.POSITION
SYSIBM.RIGHT
SYSIBM.SUBSTRING
SYSIBM.UCASE
SYSIBM.UPPER
The problem only occurs if one of the above string function
invocations includes the optional string unit parameter
(CODEUNITS16, CODEUNITS32 or OCTETS). For example:
select * from t1 where substring (c1, 1, 5, codeunits16) = ''
and c2=6939
SQL0401N The data types of the operands for the operation "="
are not compatible. SQLSTATE=42818
select * from t1 where c2=7453 and substring (c1, 1, 5, octets)
= ''
SQL0440N No authorized routine named "SUBSTRING" of type
"FUNCTION" having compatible arguments was found.
SQLSTATE=42884 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users who are using one of the built-in string manipulation *
* functions. *
****************************************************************
* PROBLEM DESCRIPTION: *
* If this APAR is not applied, it is possible that a query may *
* fail to compile with SQL0401N or SQL0440N. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.1, Fixpack 10. *
**************************************************************** |
| Local Fix: |
If possible, avoid the use of the string unit. |
| available fix packs: |
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
|
| Solution |
First fixed in DB2 Version 9.1, Fixpack 10. |
| Workaround |
n/a |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.10.2010
13.06.2011
13.06.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.1.FP10
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.10
|
 |
