IBM Support

IT04155: 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:                                              *
    * DB2 LUW                                                      *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 LUW version 10.1 fixpak 5.                    *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 LUW version 10.1 fixpak 5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT04155

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-09-03

  • Closed date

    2015-07-13

  • Last modified date

    2015-07-13

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

    IT04139

  • 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

  • RA10 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 10.1

Reference #: IT04155

Modified date: 13 July 2015