PM96478: NEW FUNCTION FOR DB2 V11

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• New function for DB2 V11.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 11 for z/OS who are         *
  *                 using or interested in the IBM DB2           *
  *                 Analytics Accelerator.                       *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR provides new function to      *
  *                      allow a DB2 subsystem to model          *
  *                      the existence of an accelerator to      *
  *                      evaluate the CPU and elapsed time spent *
  *                      in DB2 for static SQL queries that      *
  *                      would potentially be eligible for       *
  *                      acceleration if an accelerator were     *
  *                      active. No accelerator is required or   *
  *                      needs to be active for this modeling to *
  *                      occur.                                  *
  *                      This APAR also fixes SQLCODE -950 for   *
  *                      an INSERT with subselect involving      *
  *                      a three part name for remote tables.    *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR provides new function to allow a DB2 subsystem to
  model the existence of an accelerator to evaluate the CPU
  and elapsed time spent in DB2 for dynamic and static SQL
  queries that would be eligible for acceleration if an
  accelerator were active.  No accelerator is required or
  needs to be active for this modeling to occur.
  Additionally, EXPLAIN will populate DSN_QUERYINFO_TABLE,
  which shows if a query is eligible for offload or not.
  
  The ACCELMODEL subsystem parameter determines whether to
  enable modeling of query workload for evaluating potential
  savings for both the accumulated elapsed time and CPU time if
  the plan is executed on an accelerator. Only queries that are
  deemed eligible for execution on an accelerator by DB2 will be
  included in accelerator-related fields of accounting  trace
  IFCID 3.
  
  NO
  
  Specifies that no modeling is to be performed.  This is the
  default setting.
  
  YES
  
  Specifies that modeling is to be performed and consider
  acceleration eligibility for an SQL statement and update the
  new accounting fields accordingly
  
  To enable modeling, the IDAA special register CURRENT QUERY
  ACCELERATION and zparm QUERY_ACCELERATION (set by the CURRENT
  QUERY ACCEL field of this panel) must be set to NONE for
  accelerator modeling to be used.  Also the special register and
  zparm GET_ACCEL_ARCHIVE must be set to NO for accelerator
  modeling to be used. All other values for the
  special register and zparm will cause DB2 to attempt to
  accelerate queries instead of performing accelerator modeling.
  This means that existing queries that already execute on the
  accelerator with CURRENT QUERY ACCELERATION = ENABLE,
  ENABLE WITH FAILBACK, ELIGIBLE, or ALL will not be part of the
  accelerator-related accounting fields.
  
  DB2 does not perform accelerator modeling for SQL queries
  executed under a native stored procedure (aka SQLPL procedure).
  However, DB2 does perform accelerator modeling for SQL queries
  executed under an external stored procedure.  Accelerator
  modeling for native or SQLPL procedures is a consideration for
  the future.
  
  For SQL INSERT with a subselect statement, even though only
  the SELECT itself may be eligible for acceleration,
  the reported CPU and elapsed time spent in DB2 includes
  the INSERTs performed in DB2 as part of the SQL statement.
  The CPU and elapsed time starts from the beginning of
  the SQL statement execution and ends when the entire SQL
  statement is completed.  DB2 does not breakdown the CPU and
  eligible time for 'parts' of an SQL statement execution
  
  EXPLAIN for a statement that goes through accelerator modeling
  will have the following output:
  (1) If the query is eligible for offload,
  DSN_STATEMNT_TABLE.REASON = 'ACCELMODEL ELIGIBLE'
  
  (2) If the query is not eligible for offload,
  DSN_STATEMNT_TABLE.REASON = 'ACCELMODEL NOT ELIGIBLE'
  
  (3) DSN_QUERYINFO_TABLE will show the accelerated or not
  accelerated output (same as today). Column QINAME1 will have a
  value of 'ACCELMDL', which is normally the accelerator name
  
  (4) PLAN_TABLE and all other explain tables will show the DB2
  access path
  
  Three new accounting fields will be added to the QWAC of the
  IFCID 3. These values do not include times from SQL executed
  in a native SQL stored procedure that are eligible for
  execution on an accelerator.
  QWAC_ACCEL_ELIG_ELA  CL8   The accumulated elapsed time spent
                             processing SQL in DB2 that may be
                             eligible for execution on an
                             accelerator.
  
  QWAC_ACCEL_ELIG_CP   CL8   The accumulated CPU time spent
                             processing SQL  in DB2 that may be
                             eligible for execution on an
                             accelerator.
  
  QWAC_ACCEL_ELIG_SE DS CL8 The accumulated CPU time consumed on
                            an IBM specialty engine while
                            processing SQL in DB2 that may be
                            eligible for execution on an
                            accelerator.
  
  Field QWP4ACMO is added to the IFCID 106 trace record
  to track the ACCELMODEL parameter.
  
  This APAR also has the following fix:
  An SQLCODE -950 is incorrectly returned for an INSERT
  with subselect that has a three part name for remote
  tables when QUERY ACCELERATION=ENABLE,
  ENABLE WITH FAILBACK, ELIGIBLE or ALL.  SQLCODE -950 is
  also incorrectly returned when GET_ACCEL_ARCHIVE = YES.
  Execution of an SQL statement with a three part name on
  an accelerator is not supported, so the SQL statement
  will either execute on DB2 or return SQLCODE -4742
  depending on the values of QUERY ACCELERATION and
  GET_ACCEL_ARCHIVE.
  
  Keywords:
  IDAAV3R1/K IDAAV2R1/K SQLACCELMODEL SQLINSERT SQLCODE950
  IDAAV4R1/K
  

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:

  UI13488

Modules/Macros

• DSN@XAZP DSNB1TAD DSNDQWAC DSNDQWPZ DSNDSPRM DSNRUB01 DSNTIDXA
  DSNTIDXB DSNTIJUZ DSNTINST DSNTLRAC DSNTXAZP DSNTXSTB DSNWAACT
  DSNWACDC DSNWACDF DSNWDFIF DSNWVAPR DSNWVSMT DSNWVSR1 DSNWVZCK
  DSNWVZIT DSNWVZPS DSNWVZSA DSNWVZSS DSNWZIFB DSNXECT  DSNXEDP
  DSNXEDS1 DSNXEET  DSNXEPM  DSNXEPP  DSNXERT  DSNXERT2 DSNXESTR
  DSNXESTS DSNXODML DSNXOD4  DSNXOD5  DSNXOIN  DSNXOMPS DSNXONZO
  DSNXOOP  DSNXRCUF DSNXRRTN DSNXTAD  DSNX8SMF DSNX9WCA DSN3EOT0
  DSN6SPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI13488

     UP14/01/10 P F401

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.