PM75021: SQLCODE219 DURING BIND / REBIND PACKAGE EXPLAIN(YES) WHEN INDEX WITH KEY-EXPRESSION CREATED ON PLAN_TABLE

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • With Index on expression  on PLAN_TABLE, customer gets sqlcode
    -219
    and PLAN_TABLE (BINDER.PLAN_TABLE POPULATED VS. OWNER.PLAN_TABLE
    )
    
    SQLCODE219  DSNXOD0Z RDS CODE=-120
    

Local fix

  • dropping 'index on expression' on plan_table
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 for z/OS users of BIND/REBIND     *
    *                 PACKAGE and bind options EXPLAIN(YES) or     *
    *                 EXPLAIN(ONLY) where an index with key        *
    *                 expression is defined on                     *
    *                 the package-owner.PLAN_TABLE                 *
    ****************************************************************
    * PROBLEM DESCRIPTION: SQLCODE -219 occurred during BIND /     *
    *                      REBIND PACKAGE EXPLAIN(YES|ONLY) as DB2 *
    *                      attempted to update binder.PLAN_TABLE   *
    *                      instead of the expected                 *
    *                      package-owner.PLAN_TABLE, when          *
    *                      an index with a key-expression is       *
    *                      defined on  package-owner.PLAN_TABLE    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    During BIND / REBIND PACKAGE EXPLAIN ( YES ) a SQLCODE -219
    occurred with message tokens showing that DB2 attempted to
    update binder.PLAN_TABLE for an explainable static SQL
    statement, instead of updating the expected
    package-owner.PLAN_TABLE. DB2 csect DSNXOD0Z issued SQLCODE219
    indicating that the
    "REQUIRED EXPLANATION TABLE binder.PLAN_TABLE DOES NOT EXIST",
    however, the SQLCODE219 was invalidly issued by DB2 because
    DB2 should have used package-owner.PLAN_TABLE instead.
    The user had created package-owner.PLAN_TABLE and defined
    an index with key-expression (aka index on expression ) on
    the PLAN_TABLE.
                                                                   .
    The SQLCODE219 occurred for the second or later explainable
    static SQL statement being bound during the bind package
    process, following DB2's invocation of the PLAN_TABLE index key
    expression when DB2 correctly updated package-owner.PLAN_TABLE
    for the first static SQL statement. When DB2 executed the
    PLAN_TABLE index key-expression, the authorization ID used was
    validly changed to the primary auth ID value -- this is normal,
    expected behavior when executing an index key expression.
    In this SQLCODE219 case, the primary auth ID was the binder
    (user auth ID performing the BIND/REBIND PACKAGE), and after
    executing the index key-expression DB2 did not restore
    the auth ID back to the package owner, leaving the binder as
    the auth ID for the remainder of the bind package process.
    Package owner is the standard auth ID used for the bind package
    process and is the auth ID / qualifier that DB2 uses for
    the PLAN_TABLE when 'explaining' static SQL statements during
    the bind package.
                                                                   .
    As a result of DB2's failure to restore the auth ID to package
    owner, for the second and later explainable static SQL
    statements DB2 attempted to update binder.PLAN_TABLE instead of
    package-owner.PLAN_TABLE. This resulted in invalid SQLCODE219
    during the bind package process for those subsequent static SQL
    statements.
                                                                   .
    The problem can also occur when bind option EXPLAIN( ONLY ) is
    used.
                                                                   .
    To temporarily avoid the SQLCODE219 on the BIND/REBIND PACKAGE,
    drop the INDEX on the package-owner.PLAN_TABLE.
                                                                   .
    

Problem conclusion

  • DB2 was changed to, after completing execution of the index key-
    expression, restore the original authorization in use 'before'
    the index with key-expression was invoked.
    This applies for an index with key-expression used during
    a BIND / REBIND PACKAGE or also another DB2 operation.
                                                                   .
    Additional search keywords: SQLEXPLAIN
                                                                   .
    

Temporary fix

  • AM75021
    

Comments

APAR Information

  • APAR number

    PM75021

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-15

  • Closed date

    2012-11-17

  • Last modified date

    2013-01-02

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

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

    UK83642

Modules/Macros

  •    DSNIKESR DSNKEDLE DSNKEINS DSNKSDLE DSNKSINS
    DSNKSKYG DSNUCINT DSNUCLGR DSNUCRUL DSNUKINT DSNUKIUL DSNURFBR
    DSNURFIT DSNURLXL DSNURPUI DSNURSCN DSNURWBF DSNURWIK DSNUSTBL
    DSNUULXA
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK83642

       UP12/12/04 P F212

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

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM75021

Modified date:

2013-01-02

Translate my page

Machine Translation

Content navigation