IBM Support

LI73609: DB2 V9.5 FP1:EXPLAIN TABLES DOES NOT STORE INLIST VALUES.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • In DB2 Database for Linux, UNIX, and Windows, when IN list
    predicates are used in SQL statements, sometimes the optimizer
    chooses to apply an IN list predicate by transforming it into a
    join. When a join is chosen to apply the IN list predicate, the
    constant values used in the IN list do not get stored in the
    explain tables in a manner that associates them with either the
    join or the "GENROW" table scan (TBSCAN) that is used to apply
    the IN list. The only predicate stored in the explain tables is
    an equality (HOW_APPLIED column keyword of "EQ")  join predicate
    (RELOPT_TYPE column keyword of "JOIN") involving a tokenized
    column name  in the EXPLAIN_PREDICATE table in the
    PREDICATE_TEXT column.
    
    This can make it difficult to tell what the IN list values are,
    or, in the case of an SQL statement with multiple IN lists,
    which IN list is being applied in a join.
    

Local fix

  • In DB2 V9.5, the Optimized SQL can be parsed to determine the IN
    list values.
    
    CHANGES AFER APPLYING THE APAR FIX:
    
    After applying this APAR fix, if an IN list is transformed into
    a join, in addition to the current JOIN predicate described in
    the ERROR DESCRIPTION for this APAR, you will see an new row in
    the EXPLAIN_ARGUMENT table with an ARGUMENT_TYPE column keyword
    of "GENRVALS" and "VALUES( comma separated IN list values)" in
    either the ARGUMENT_VALUE column (if the IN list is 1024
    characters or fewer) or the LONG_ARGUMENT_VALUE (if the IN list
    is greater than 1024 characters). If the IN list values are in
    the ARGUMENT_VALUE column, then the LONG_ARGUMENT_VALUE column
    will be NULL. If the IN list values are in the
    LONG_ARGUMENT_VALUE column, then the ARGUMENT_VALUE column will
    be NULL.
    

Problem summary

  • In DB2 Database for Linux, UNIX, and Windows, when IN list
    predicates are used in SQL statements, sometimes the optimizer
    chooses to apply an IN list predicate by transforming it into a
    join. When a join is chosen to apply the IN list predicate, the
    constant values used in the IN list do not get stored in the
    explain tables in a manner that associates them with either the
    join or the "GENROW" table scan (TBSCAN) that is used to apply
    the IN list. The only predicate stored in the explain tables is
    an equality (HOW_APPLIED column keyword of "EQ")  join predicate
    (RELOPT_TYPE column keyword of "JOIN") involving a tokenized
    column name  in the EXPLAIN_PREDICATE table in the
    PREDICATE_TEXT column.
    

Problem conclusion

  • First Fixed in DB2 v9.5 FP3
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI73609

  • 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

    2008-07-28

  • Closed date

    2009-04-16

  • Last modified date

    2009-04-16

  • 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 UDE ESE LIN

  • Fixed component ID

    5765F4104

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:
16 April 2009