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

Obtain the fix for this APAR.

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 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.