IBM Support

IC95686: DB2 NOT GENERATING TBSCAN ON INNER OF NLJN WHEN OUTER CAN HAVE ONLY 1 ROW

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • After upgrading to DB2 v9.7, a query may have poorer performance
    when an Index Scan (IXSCAN), Fetch with an Index Scan
    (FETCH/IXSCAN), or Table Queue / Table Scan (TQ/TBSCAN) is used
    instead of a direct TBSCAN on the inner of a Nested Loop Join
    (NLJOIN), when the outer of the Nested Loop Join can generate
    only a single row.
    
    In order to determine if you are encoutering this issue, the
    Query Execution Plan needs to be examined. The QEP can be
    generating using the DB2 Explain facility (db2exfmt), or DB2
    Optim Query Tuner / Graphical Explain. Ideally, the QEP from the
    previous version of DB2 would also be available. Examin the
    explain using the following steps:
    1) Locate any Nested Loop Joins (NLJOIN)
    2) Examine the outer / left leg of the NLJOIN, to determine if
    the cardinality of that portion of the plan can generate only a
    single row.
      - Plans that use a Group By, GENROW, or a fully qualified
    primary key access are most likely to generate only a single row
    3) Examine the inner of the NLJOIN. If the inner portion of the
    plan uses an IXSCAN, FETCH/IXSCAN, or a TQ, then you may be
    experiencing this problem:
      - if the IXSCAN, FETCH/IXSCAN or TQ with any of an IXSCAN,
    FETCH/IXSCAN or TBSCAN, result in scanning the entire inner
    table, or almost the entire inner table
      - A FETCH/IXSCAN  or TQ/FETCH/IXSCAN is used, and the index is
    very poorly clustered (cr, or cf are less then approx 20% - 50%)
      - If you have access to a plan from the previous version of
    DB2, and you find a TBSCAN on the inner of this NLJOIN, and the
    outer used a TQ to send the single row to the TBSCAN.
    
    If the above conditions are met, then it is likely you are
    experiencing the issue addressed by this APAR fix. This APAR fix
    will allow the Table Scan (TBSCAN) to be generated on the inner
    of the Nested Loop Join (NLJOIN) when the outer only produces a
    single row.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 LUW DPF users                                            *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade DB2 Linux, Unix, Windows server to Version 9.7 Fix   *
    * Pack 9 or higher.                                            *
    ****************************************************************
    

Problem conclusion

  • Update the DB2 Linux, Unix, Windows server to Version 9.7 Fix
    Pack 9 or higher.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC95686

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-09-03

  • Closed date

    2013-12-16

  • Last modified date

    2013-12-16

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

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

    IC96999 IC97009

Fix information

Applicable component levels

  • R970 PSN

       UP

  • RA10 PSN

       UP

  • RA50 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC95686

Modified date: 16 December 2013