|
Problem IC74272
|
Status: Closed |
DB2 MIGHT NOT RETURN EXPECTED RESULTS FOR SCALE FUNCTIONS WITH SMALLINT INPUT. |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
DB2 might return an error message or incorrect results when
scale function (LEFT, RIGHT, OVERLAY, INSERT) are used with
SMALLINT as inputs.
For example, although correct, following query might return
error code SQL0138N:
SELECT LEFT('002', SMALLINT(2)) FROM SYSIBM.SYSDUMMY1
SQL0138N A numeric argument of a built-in string function is
out of range.
SQLSTATE=22011 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users running DB2 v9.5 FP7 and earlier. *
****************************************************************
* PROBLEM DESCRIPTION: *
* DB2 might return an error message or incorrect results when *
* *
* scale function (LEFT, RIGHT, OVERLAY, INSERT) are used with *
* *
* SMALLINT as inputs. *
* *
* *
* *
* For example, although correct, following query might return *
* *
* error code SQL0138N: *
* *
* *
* *
* SELECT LEFT('002', SMALLINT(2)) FROM SYSIBM.SYSDUMMY1 *
* *
* *
* *
* SQL0138N A numeric argument of a built-in string function *
* is *
* out of range. *
* *
* SQLSTATE=22011 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v9.5 FP8. *
**************************************************************** |
| Local Fix: |
Explicitly cast the small int as integer.
eg. db2 "SELECT LEFT('002', cast(SMALLINT(2) as integer)) FROM
SYSIBM.SYSDUMMY1"; |
| available fix packs: |
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
The problem was fixed in DB2 v9.5 FP8. |
| Workaround |
Explicitly cast the small int as integer.
eg. db2 "SELECT LEFT('002', cast(SMALLINT(2) as integer)) FROM
SYSIBM.SYSDUMMY1"; |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 07.02.2011
05.07.2011
05.07.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP8
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.5.0.8
|
 |
