DB2 FOR LUW / DB2FORLUW / 950 - DB2

An SQL0901N may be issued when executing a query an SQL UDF that 
takes as input the output of a UNION ALL (UA) view or subselect. 
 
The following conditions must be satisfied for the scenario to 
occur: 
- The UDF must be non-atomic 
- The output data-type of the SQL UDF must be a VARCHAR or 
VARGRAPHIC 
- The maximum length of the output expression of the UDF must be 
strictly less than the length of the output data-type of the UDF 
- The UDF must use the output columns of a UA as its input 
- The UDF must form the input to a subselect 
- The output column of the subselect derived from the UDF must 
be used in a GROUP BY clause 
 
An example scenario follows: 
 
-- Table DDL 
CREATE TABLE T(DRIVER_C CHAR(2), DUMMY_C INT); 
 
-- SQL UDF DDL 
CREATE FUNCTION DUMMY_FN (INPUT_C VARCHAR(2)) 
RETURNS VARCHAR(20) 
LANGUAGE SQL 
DETERMINISTIC 
NO EXTERNAL ACTION 
RETURN CASE WHEN INPUT_C = 'AA' THEN 'VC-19-CNST-LT-VC-20' ELSE 
'BB' 
END 
; 
 
-- Query that will issue the SQL0901N 
SELECT DRIVER_C, 
       SUM(DUMMY_C) 
FROM 
  ( SELECT DUMMY(DRIVER_C), DUMMY_C FROM 
    ( 
      SELECT DRIVER_C, 
             DUMMY_C 
      FROM   T 
      UNION ALL 
      SELECT DRIVER_C, 
             DUMMY_C 
      FROM   T 
    ) AS UA (DRIVER_C, DUMMY_C) 
  ) AS SELSTAR (DRIVER_C, DUMMY_C) 
GROUP BY DRIVER_C 
; 
 
-- SQLCODE issued 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed.  (Reason "Describe: 
Invalid svar 
len".)  SQLSTATE=58004 
 
This problem will be first fixed in DB2 Version 9.1 Fix Pack 8 
and DB2 Version 9.5 Fix Pack 5.

**************************************************************** 
* USERS AFFECTED:                                              * 
* EE or EEE                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.5 Fix Pack 5.                       * 
****************************************************************

- Reduce the value of the CURRENT QUERY OPTIMIZATION special 
register to 0. 
- Manually rewrite the query in question to workaround the 
issue. For example, the query above may be semantically 
equivalently rewritten to: 
SELECT DRIVER_C, 
       SUM(DUMMY_C) 
FROM 
  ( SELECT DUMMY(DRIVER_C), 
           DUMMY_C 
    FROM   T 
    UNION ALL 
    SELECT DUMMY(DRIVER_C), 
           DUMMY_C 
    FROM   T 
  ) AS UA (DRIVER_C, DUMMY_C) 
GROUP BY DRIVER_C 
;

DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
 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
 

Problem was first fixed in DB2 Version 9.5 Fix Pack 5.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

21.07.2009
19.02.2010
19.02.2010

9.5.FP5