IBM Support

IT19608: DB2 MAY CONVERT VIEW COLUMN TYPES INCORRECTLY OR RETURN SQL0418NUPON REVALIDATION OF A VIEW WITH UNTYPED EXPRESSIONS

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • If a view containing untyped expressions becomes invalid, DB2
    may incorrectly convert certain columns within that view to an
    incorrect data type upon revalidation. This means that the data
    type of the untyped expression is determined successfully by
    view creation, but is incorrectly determined upon view
    revalidation. Alternatively, DB2 may return SQL0418N.
    
    Conditions that cause this issue:
     - View contains an untyped expression AND
     - View contains a UNION / UNION ALL on the untyped expression
    AND
     - View is invalidated AND
     - View is revalidated
    
    Example of sql that could convert a view column to an incorrect
    data type:
    
    CREATE TABLE t1 (c1 CHAR(3));
    
    CREATE VIEW v AS
    WITH
    inline_view AS (SELECT NULL AS c2 FROM SYSIBM.SYSDUMMY1)
    SELECT c1 FROM t1
    UNION
    SELECT c2 FROM inline_view;
    
    When describing this view, c1 should appear as a CHAR(3).
    However when revalidating this view (for example after t1 is
    dropped and recreated), c1 appears as type VARCHAR(3) which is
    incorrect.
    

Local fix

  • There are two fixes available:
    
    1) Perform an explicit cast of the untyped expression to the
    desired type. From the above example, change "inline_view AS
    (SELECT
    NULL AS c2 FROM SYSIBM.SYSDUMMY1)" to "inline_view AS (SELECT
    CAST(NULL as CHAR(3)) AS c2 FROM SYSIBM.SYSDUMMY1)".
    
    2) Recreate the view before revalidating.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 11.1 Mod 2 Fix Pack 2 or higher               *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 11.1 Mod 2 Fix Pack 2
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT19608

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-03-08

  • Closed date

    2017-06-27

  • Last modified date

    2017-06-27

  • APAR is sysrouted FROM one or more of the following:

    IT12781

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020