PI10639: DB2 10 AND DB2 11 FOR Z/OS NEW FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• DB2 10 and DB2 11 for z/OS new function.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS  users of the following     *
  *                 dynamic query acceleration features:         *
  *                 (1) A DB2 'virtual' accelerator for queries  *
  *                 (2) Either the dynamic SQL statement EXPLAIN *
  *                 or the special register CURRENT EXPLAIN      *
  *                 MODE = EXPLAIN to evaluate a dynamic SQL     *
  *                 query's eligibility for acceleration         *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR provides new function that    *
  *                      allows either the dynamic EXPLAIN       *
  *                      statement or the special register       *
  *                      CURRENT EXPLAIN MODE = EXPLAIN to be    *
  *                      used with DB2 Profile Monitor to        *
  *                      evaluate a dynamic SQL query's          *
  *                      eligibility for acceleration against    *
  *                      a specific accelerator named in the DB2 *
  *                      Profile table DSN_PROFILE_ATTRIBUTES .  *
  *                      This is most useful when you want to    *
  *                      use a 'virtual' accelerator for query   *
  *                      evaluation instead of a 'real'          *
  *                      accelerator.                            *
  *                                                              *
  *                      This APAR also addresses the following  *
  *                      abends when DB2 unexpectedly executes   *
  *                      an accelerated dynamic SQL query that   *
  *                      was prepared using CURRENT EXPLAIN      *
  *                      MODE = EXPLAIN :                        *
  *                      (1) ABEND04E RC00C90101 DSNIBCTD :5007  *
  *                      (2) ABEND04E RC00E20009                 *
  *                          DSNXEDC3 . DSNSVSFB +0898           *
  *                      Other abend symptoms may be possible.   *
  *                      The abends can occur with or without    *
  *                      using a virtual accelerator.            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Currently, the DB2 dynamic SQL statement EXPLAIN or the DB2
  special register CURRENT EXPLAIN MODE = EXPLAIN can be used to
  evaluate a dynamic SQL query's eligibility for acceleration.
  However, DB2, not the user, chooses the target accelerator used
  for this acceleration evaluation.  If the user wants a 'virtual'
  accelerator to be used for the query's acceleration evaluation
  instead of a 'real' accelerator when both are 'active' for the
  DB2 subsystem, DB2 will not choose the 'virtual' accelerator.
  .
  This APAR provides new function that allows either the dynamic
  EXPLAIN statement or the special register CURRENT EXPLAIN
  MODE = EXPLAIN to be used with DB2 Profile Monitor to evaluate
  a dynamic SQL query's eligibility for acceleration against
  a specific accelerator named in the DB2 Profile table
  SYSIBM.DSN_PROFILE_ATTRIBUTES .  In this way, the user can
  direct DB2 to use instead a virtual accelerator for evaluating
  a query for acceleration.
  .
  A new function keyword, ACCEL_NAME_EXPLAIN, for
  SYSIBM.DSN_PROFILE_ATTRIBUTES has been added. The column
  ATTRIBUTE1 contains the real or virtual accelerator
  name. This profile monitoring attribute is supported
  for the following profile scopes:
    Global
    AUTHID+LOCATION
    PLANNAME+COLLID+PKGNAME
  .
  This new attribute directs a dynamic EXPLAIN statement
  to evaluate an SQL query for acceleration to the accelerator
  defined in ACCEL_NAME_EXPLAIN. When using the special register
  CURRENT EXPLAIN MODE = EXPLAIN, during the PREPARE of
  a dynamic SQL query the query is evaluated for acceleration
  to the accelerator defined in ACCEL_NAME_EXPLAIN.
  .
  The new function ACCEL_NAME_EXPLAIN will not be used for the
  following scenarios:
  (1) static SQL queries during BIND/REBIND PACKAGE EXPLAIN(YES)
      or EXPLAIN(ONLY)
  (2) dynamic SQL queries PREPAREd when using CURRENT EXPLAIN
      MODE=YES
  .
  ACCEL_NAME_EXPLAIN will not cause an SQL query to be actually
  accelerated to a specific accelerator at execution time.
  .
  Example
  -------
  The following example assumes there is an existing
  virtual accelerator with the name VACCEL.  The INSERT
  statements below create a global profile such that all dynamic
  EXPLAIN statements with CURRENT QUERY ACCELERATION=ENABLE,
  ENABLE WITH FAILBACK, ELIGIBLE or ALL will use the
  accelerator VACCEL.
  .
    INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID)
     VALUES (1);
  .
    INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES
     (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3)
     VALUES
     (1, 'ACCEL_NAME_EXPLAIN', 'VACCEL',NULL,NULL);
  .
  After executing the INSERT statements above, the
  -START PROFILE command must be issued so that the profile
  is activated. At this time all dynamic EXPLAIN statements
  will use the virtual accelerator 'VACCEL'.
  .
  SET CURRENT QUERY ACCELERATION=ENABLE;
  SET CURRENT EXPLAIN MODE=EXPLAIN;
  SELECT * FROM T1 WHERE C1>2 FOR FETCH ONLY;
  .
  The SELECT statement above will populate the EXPLAIN tables
  since CURRENT EXPLAIN MODE=EXPLAIN and will use the
  virtual accelerator 'VACCEL' as defined in the profile tables.
  ---------------------------------------------------------
  This APAR also addresses the following abends when DB2
  unexpectedly executes an accelerated dynamic SQL query that
  was prepared using CURRENT EXPLAIN MODE = EXPLAIN :
   (1) AB04E RC00C90101 DSNIBCTD :5007
   (2) AB04E RC00E20009 DSNXEDC3 . DSNSVSFB +0898
  Other abend symptoms may be possible. The abends can occur
  with or without uing a virtual accelerator.
  
  Keywords:
  IDAAV4R1/K IDAAV3R1/K ABEND04E SQLEXPLAIN ERQUAL5007
  OFFSET0898 00C90101 00E20009
  

Problem conclusion

Temporary fix

Comments

APAR Information

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

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

  UI18239 UI18240

Modules/Macros

• DSNT1RSP DSNXODML DSNXOMPS DSNXONZA
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI18239

     UP14/06/07 P F406

• RB10 PSY UI18240

     UP14/06/07 P F406

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.