PM90188: WRONG ACCESSPATH IS USED WHEN THERE IS EXISTS PREDICATE IN THE SELECT LIST AND PREDICATE TRANSITIVE CLOSURE IS TRIGGERED

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • In V10, EXITS predicate is allowed to be used in CASE statement,
    when there is EXITS predicate in the select list and
    predicate transitive closure is triggered, wrong accesspath may
    be generated.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 and 11 for z/OS user of SQL       *
    *                 statement containing EXISTS predicate in     *
    *                 WHEN clause of CASE expression.              *
    ****************************************************************
    * PROBLEM DESCRIPTION: A non-optimal access path may be        *
    *                      selected when an SQL statement          *
    *                      satisfies all of the following          *
    *                      conditions:                             *
    *                                                              *
    *                      1. EXISTS predicate is used in WHEN     *
    *                         clause of CASE expression;           *
    *                      2. multiple tables are referenced in    *
    *                         FROM clause.                         *
    *                      3. predicate transitive closure happens *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    A non-optimal access path may be selected when an SQL statement
    satisfies all of the following conditions:
    
    1. EXISTS predicate is used in WHEN clause of CASE expression;
    2. multiple tables are referenced in FROM clause.
    3. predicate transitive closure happens.
    
    The following example helps to illustrate problem:
    SELECT CASE
             WHEN EXISTS (SELECT 1 FROM SYSIBM.SYSDUMMY1)
               THEN 1
             ELSE 0
           END AS COL1
    FROM TAB1 AS A,
         TAB2 AS B,
         TAB3 AS C
    WHERE A.C1 = B.C1
          AND B.C2. = C.C2
          AND A.C1 = 11
          AND B.C2 = 22;
    
    The join sequence or join method may not be optimal selected
    when EXISTS predicate appears in WHEN clause of CASE expression.
    

Problem conclusion

  • Code in DB2 has been correctly modified to handle EXISTS
    predicate in WHEN clause of CASE expression.
    
    Additional keyword: SQLCASE SQLEXISTS SQLACCESSPATH
                        SQLPERFORMANCE
    

Temporary fix

  • * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM90188

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-05-31

  • Closed date

    2013-08-06

  • Last modified date

    2013-09-03

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

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

    UK96469 UK96470

Modules/Macros

  • DSNXOEXT DSNXOTCP
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK96469

       UP13/08/22 P F308 ½

  • RB10 PSY UK96470

       UP13/08/22 P F308 ½

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM90188

Modified date:

2013-09-03

Translate my page

Machine Translation

Content navigation