|
Problem IC74014
|
Status: Closed |
SYS_CONNECT_BY_PATH FAILS WITH -901 WHEN VARCHAR2 COMPATIBILITY FEATURE IS NOT ENABLED. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
On a database without the VARCHAR2 compatibility feature
enabled, SYS_CONNECT_BY_PATH may fail at execution with the
following error:
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason
"sqlricc2 result type".)
SQLSTATE=58004
This will happen when the length of the first argument times two
plus the length of the second argument exceeds 4000. For
example:
CREATE TABLE connby ( name VARCHAR(2000), id INTEGER, pid
INTEGER );
SELECT SYS_CONNECT_BY_PATH( name, '/' )
FROM connby START WITH id = 1 CONNECT BY PRIOR id = pid;
The length of the first argument (name) is 2000, the length of
second argument is 1: 2*2000+1 = 4001 > 4000. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* On a database without the VARCHAR2 compatibility feature *
* enabled, SYS_CONNECT_BY_PATH may fail at execution with the *
* following error: *
* *
* SQL0901N The SQL statement failed because of a non-severe *
* system error. *
* Subsequent SQL statements can be processed. (Reason *
* "sqlricc2 result type".) *
* SQLSTATE=58004 *
* *
* This will happen when the length of the first argument times *
* two *
* plus the length of the second argument exceeds 4000. For *
* example: *
* *
* CREATE TABLE connby ( name VARCHAR(2000), id INTEGER, pid *
* INTEGER ); *
* SELECT SYS_CONNECT_BY_PATH( name, '/' ) *
* FROM connby START WITH id = 1 CONNECT BY PRIOR id = pid; *
* *
* The length of the first argument (name) is 2000, the length *
* of *
* second argument is 1: 2*2000+1 = 4001 > 4000. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 4. *
**************************************************************** |
| Local Fix: |
If possible, cast the first argument such that the combined
length <= 4000. Note that this may result in truncation of the
result |
| available fix packs: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
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
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 4. |
| Workaround |
If possible, cast the first argument such that the combined
length <= 4000. Note that this may result in truncation of the
result |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.01.2011
05.05.2011
05.05.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
