IBM Support

JR29427: IMPROVEMENT TO COALESCE AND VALUE SCALAR FUNCTION PROCESSING

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • One usage of COALESCE (or VALUE) function is to replace NULL
    value with a meaningful value to the users
    in the result set. When the first argument of COALESCE is known
    to not producing any NULL value, the arguments
    following this argument could be ignore. Subsequently, DB2 can
    rewrite the expression of the COALESCE function
    to the first argument.
    
    When an ORDER BY clause consists of expressions, DB2 processes
    the ORDER BY clause after the expressions are evaluated
    by sorting the expressions in the SELECT clause and the
    expressions of the ORDER BY clause that are not in the SELECT
    clause.
    When the width of the summation of the aforementioned
    expressions and/or the number of expressions exceeds the maximum
    length of a row and/or maximum number of columns given in the
    DB2 documentation titled "SQL and XQuery limits," DB2 returns
    a SQL1585N error.
    

Local fix

  • The rewrite of COALESCE (or VALUE) function to its first
    argument when DB2 can deduce that the first argument could not
    return a NULL value could benefit the query processing in many
    ways. One of the benefit, when rewriting COALESCE
    function in an ORDER BY clause, is to avoid SQL1585N error. This
    is because DB2 has a choice to group columns in an ORDER BY
    clause to small sets of columns from the same tables and then
    process the ORDER BY for each smaller set of columns at the
    processing of individual tables.
    

Problem summary

  • Users affected:
    All Users on V95 FP1 and FP2
    
    Problem Description:
    One usage of COALESCE (or VALUE) function is to replace NULL
    value with a meaningful value to the users
    in the result set. When the first argument of COALESCE is known
    to not producing any NULL value, the arguments
    following this argument could be ignore. Subsequently, DB2 can
    rewrite the expression of the COALESCE function
    to the first argument.
    
    When an ORDER BY clause consists of expressions, DB2 processes
    the ORDER BY clause after the expressions are evaluated
    by sorting the expressions in the SELECT clause and the
    expressions of the ORDER BY clause that are not in the SELECT
    clause.
    When the width of the summation of the aforementioned
    expressions and/or the number of expressions exceeds the maximum
    length of a row and/or maximum number of columns given in the
    DB2 documentation titled "SQL and XQuery limits," DB2 returns
    a SQL1585N error.
    
    LOCAL FIX:
    The rewrite of COALESCE (or VALUE) function to its first
    argument when DB2 can deduce that the first argument could not
    return a NULL value could benefit the query processing in many
    ways. One of the benefit, when rewriting COALESCE
    function in an ORDER BY clause, is to avoid SQL1585N error. This
    is because DB2 has a choice to group columns in an ORDER BY
    clause to small sets of columns from the same tables and then
    process the ORDER BY for each smaller set of columns at the
    processing of individual tables.
    

Problem conclusion

  • First fixed in DB2 UDB Version V95, FixPak 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR29427

  • Reported component name

    DB2 UDB WSE WIN

  • Reported component ID

    5765F3501

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-05-29

  • Closed date

    2009-03-09

  • Last modified date

    2009-03-09

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

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

Fix information

  • Fixed component name

    DB2 UDB WSE WIN

  • Fixed component ID

    5765F3501

Applicable component levels

  • R950 PSY

       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:
09 March 2009