IBM Support

PK64719: FIX FOR AMBIGOUS CURSOR WHICH DEFINED AS "WITH UR"

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • fix for ambigous cursor which defined as "WITH UR"
    .
    The fix also closes a loop hole for a sensitive cursor
    defined WITH UR , thus an SQLCODE243 will now be issued
    correctly during bind package .
    

Local fix

  • add 'FOR FETCH ONLY' clause to SQL
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: DB2 for z/OS users who define a READ ONLY    *
    *                 cursor using 'WITH UR', and the cursor       *
    *                 utilizes a non-correlated subquery.          *
    ****************************************************************
    * PROBLEM DESCRIPTION: When a cursor is defined using 'WITH    *
    *                      UR' but without a 'FOR FETCH ONLY'      *
    *                      clause, and the cursor utilizes a       *
    *                      non-correlated subquery, DB2 may drive  *
    *                      a subquery-to-join transformation if    *
    *                      there is an unique index for the        *
    *                      subquery.                               *
    *                      However, DB2 considers this cursor      *
    *                      as an ambiguous cursor when selecting   *
    *                      an access path, and pins the parent     *
    *                      table as the outer table.  This may     *
    *                      result in an inefficient access path.   *
    *                      For example,                            *
    *                                                              *
    *                      DECLARE CUR1 CURSOR FOR                 *
    *                      SELECT T1.C1                            *
    *                      FROM T1                                 *
    *                      WHERE T1.C2 IN                          *
    *                         (SELECT T2.C1                        *
    *                          FROM T2                             *
    *                          WHERE T2.C2 = 1)                    *
    *                      WITH UR;                                *
    *                                                              *
    *                      With the above cursor, if there is an   *
    *                      unique index on T2, it drives the       *
    *                      non-correlated subquery-to-join         *
    *                      transformation.  However, when DB2      *
    *                      selects access path, the cursor CUR1    *
    *                      is considered as an ambiguous cursor    *
    *                      due to the lack of a 'FOR FETCH ONLY'   *
    *                      clause, and pins T1 as the outer table  *
    *                      of the join.  This may result in a      *
    *                      sub-optimal access path because T2 as   *
    *                      the outer table may provide a better    *
    *                      access path.                            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    When a cursor is defined using 'WITH UR' and utilizes a
    non-correlated subquery, if there is an unique index on the
    non-correlated subquery, DB2 drives subquery-to-join
    transformation.  However, due to lack of a 'FOR FETCH ONLY'
    clause, the cursor is considered as an ambiguous cursor during
    access path selection, and it pins the parent table as the
    outer table.  This may result in a sub-optimal access path
    because the subquery of the join transformation as outer
    may provide a better access path.
    
    Additional Keywords:
    SQLSUBQUERY SQLNONCORRSUBQ SQLACCESSPATH SQLPERFORMANCE
    

Problem conclusion

  • Code is changed so that the above situation can consider
    the parent table in other join sequences as well, so that a
    better access path may be chosen.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PK64719

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    810

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-04-18

  • Closed date

    2008-05-23

  • Last modified date

    2009-08-04

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

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

    UK36742

Modules/Macros

  • DSNXOGP
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R810 PSY UK36742

       UP08/06/12 P F806

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.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
04 August 2009