IBM Support

IT04139: PERFORMANCE MAY BE POOR FOR QUERIES WITH A VALUES CLAUSE AND UNION OPERATOR

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Queries involving a VALUES clause joined to a UNION or UNION ALL
    operation may experience poor performance. Affected queries may
    required fully reading the table referenced by the UNION or
    UNION ALL operator before applying the filtering offered by the
    VALUES clause. For example in the following query can exhibit
    this behaviour.
    
    select v.*
      from (
        select c1, c2, c3
          from (
            select *
              from t1
             union
            select *
              from t2
               )
           ) v, table(
        values (?, ?),(?, ?),(?, ?),(?, ?),(?, ?)
           ) as c(c1, c2)
     where v.c1 = c.c1
       and v.c2 = c.c2;
    
    To determine if you are affected by this issue. Gather explain
    output for relevant queries and examine the db2exfmt output to
    see if the TFNUM_LINT table function is joined to the UNION
    result.
    

Local fix

  • Manually rewrite the query to push the values clause into each
    leg of the UNION operator.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 LUW version 9.7 fixpak 10.                    *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 LUW version 9.7 Fixpak 10.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT04139

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-09-02

  • Closed date

    2014-11-19

  • Last modified date

    2014-11-19

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

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

    IT04155 IT04157

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IT04139

Modified date: 19 November 2014