DB2 FOR LUW / DB2FORLUW / 970 - DB2

A query may return SQL30020N when all of the following criteria 
are satisfied: 
- the query is executed in DB2 version 9.7 Fixpack 4 
- The database was created in VARCHAR2 compatibility mode 
  (DB2_COMPATIBILITY_VECTOR=20) before Fixpack 4 
- the query references a view column that contains a CASE 
  expression 
- the view column resolves to a different nullability in 
  Fixpack 4 when compared with what was stored in the catalog 
  when the database was created 
 
  (see the example below on how to determine the nullability 
   change). 
 
This problem affects both user and system views. 
 
For system views, the following query may be used to check if a 
case expression is involved in the view. 
 
  SELECT TEXT FROM SYSCAT.VIEWS WHERE VIEWSCHEMA = <schema> AND 
  VIEWNAME = <name> 
 
 
 
Example: 
 
Assuming that the database was created using VARCHAR2 
compatibility mode (DB2_COMPATIBILITY_VECTOR=020) 
in DB2 Version 9.7 Fixpack 3, and the following query was 
executed in Fixpack 4. 
 
The query against a system view returns SQL30020N 
 
  SELECT MODULENAME, DIALECT, REMARKS  FROM SYSCAT.MODULES 
  WHERE MODULESCHEMA='NEWTON'; 
 
SQL30020N  Execution of the command or SQL statement failed 
because of a syntax error in the communication data stream that 
will affect the successful execution of subsequent commands and 
SQL statements:  Reason Code "". SQLSTATE=58009 
 
Check the view text of the system view for CASE expression: 
 
  SELECT TEXT FROM SYSCAT.VIEWS WHERE VIEWSCHEMA = 'SYSCAT' 
  AND VIEWNAME = 'MODULES'; 
 
  create view syscat.modules (... dialect, ...) 
    as select ... 
    CAST (CASE 
    WHEN moduletype = 'P' THEN 'PL/SQL' 
    WHEN moduletype = 'A' THEN ' ' 
    ELSE 'DB2 SQL PL' 
    END AS VARCHAR(10)), ... 
    from 
    sysibm.sysmodules left outer join sysibm.syscomments r 
    on moduleid = r.objectid and r.objecttype = 'm' 
 
Check the nullability of the DIALECT column in SYSCAT.MODULES 
when the database was created (what is stored in the catalog): 
 
    DESCRIBE TABLE SYSCAT.MODULES; 
 
 
                       Data type                  Column 
    Column name  schema    Data type    Length Scale Nulls 
    ------------ --------  ------------ ------ ----- ------ 
    DIALECT      SYSIBM    VARCHAR      10      0      Yes <=== 
 
 
 
Create a dummy view in Fixpack 4 with the same view definition 
found above: 
 
    CREATE VIEW DUMMYVIEW (....) AS (select .....); 
 
Check the nullability of the the DIALECT column in the 
DUMMYVIEW: 
 
    DESCRIBE TABLE DUMMYVIEW; 
 
    Data type                          Column 
    Column name  schema   Data type    Length Scale Nulls 
    ------------ -------- ------------ ------ ----- ------ 
    DIALECT      SYSIBM   VARCHAR      10      0    NO  <===

**************************************************************** 
* USERS AFFECTED:                                              * 
* a query is executed in DB2 version 9.7 Fixpack 4 but         * 
* database was created in VARCHAR2 compatibility mode before   * 
* Fixpack 4                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 5 or newer               * 
****************************************************************

For a user view, drop and re-create the view

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
 

Problem was first fixed in Version 9.7 Fix Pack 5

See above Local Fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

12.07.2011
13.01.2012
13.01.2012

9.7.FP5