IBM Support

LI74498: DB2 MAY ISSUE SQL0344N COMPILING A QUERY REFERENCING A VIEW WITH COALESCE OR VALUE IN ITS OUTPUT LIST

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • DB2 may issue sqlcode SQL0344N compiling a query that references
    a view which was created with the COALESCE or VALUE function in
    its output list.
    
    The sqlcode will be issued only if all the following conditions
    are satisfied:
    - The view was created on DB2 Version 9.5 GA, Fix Pack 1 or Fix
    Pack 2
    - The output of the first parameter of the COALESCE or VALUE
    function is not nullable
    - The output of the first parameter of the COALESCE or VALUE
    function flows a default value
    
    An example scenario:
    
    In Fix Pack 2:
    CREATE TABLE T (C INT NOT NULL WITH DEFAULT 2);
    CREATE VIEW V (VAL_C) AS SELECT VALUE(C, 1) FROM T;
    SELECT VAL_C FROM V; -- <-- works fine
    
    In Fix Pack 3:
    SELECT VAL_C FROM V; -- <-- SQL0344N issued
    
    SQL0344N  The recursive common table expression "<schema>.V" has
    mismatched
    data types, lengths or code pages for column "VAL_C".
    SQLSTATE=42825
    
    For views created on DB2 Version 9.5 Fix Pack 3 or Fix Pack 4
    that satisfy the conditions above, upgrading to Fix Pack 5 may
    lead to an SQL0344N.
    This can occur due to incorrect view information stored in the
    catalog tables. The error may be resolved by following the
    provided local fix
    

Local fix

  • Manually recreate the affected view(s).
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * EE or EEE                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * DB2 may issue sqlcode SQL0344N compiling a query             *
    * thatreferencesa view which was created with the COALESCE or  *
    * VALUE functioninits output list.The sqlcode will be issued   *
    * only if all the followingconditionsare satisfied:- The view  *
    * was created on DB2 Version 9.5 GA, Fix Pack 1 orFixPack 2-   *
    * The output of the first parameter of the COALESCE or         *
    * VALUEfunction is not nullable- The output of the first       *
    * parameter of the COALESCE or VALUEfunction flows a default   *
    * valueAn example scenario:In Fix Pack 2:CREATE TABLE T (C INT *
    * NOT NULL WITH DEFAULT 2);CREATE VIEW V (VAL_C) AS SELECT     *
    * VALUE(C, 1) FROM T;SELECT VAL_C FROM V; -- <-- works fineIn  *
    * Fix Pack 3:SELECT VAL_C FROM V; -- <-- SQL0344N              *
    * issuedSQL0344N  The recursive common table expression        *
    * "<schema>.V"hasmismatcheddata types, lengths or code pages   *
    * for column "VAL_C".SQLSTATE=42825For views created on DB2    *
    * Version 9.5 Fix Pack 3 or Fix Pack4that satisfy the          *
    * conditions above, upgrading to Fix Pack 5maylead to an       *
    * SQL0344N.This can occur due to incorrect view information    *
    * stored inthecatalog tables. The error may be resolved by     *
    * following theprovided local fix                              *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.5 Fix Pack 5.                       *
    ****************************************************************
    

Problem conclusion

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

Temporary fix

Comments

APAR Information

  • APAR number

    LI74498

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-05-05

  • Closed date

    2010-02-19

  • Last modified date

    2010-02-19

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

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

    IC62504 IC62506

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
19 February 2010