PH09995: THE USE OF EXPLAIN AND MODELING FOR IDAA QUERIES CAN NOT RESTRICTED TO SPECIFIC IDAA VERSION IN COEXISTING ENVIRONMENT.

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• The use of EXPLAIN and MODELING for IDAA queries can not
  restricted to specific IDAA version in coexisting environment.
  
  Using EXPLAIN and MODELING ( ACCEL_MODEL zparm ) in an
  environment where IDAA V5 and IDAA V7 coexist will produce
  entries in DSN_QUERYINFO_TABLE for both versions and it is hard
  to determine which entry belong to which accelerator version.
  
  It is not possible today to restrict the EXPLAIN and MODELING
  for IDAA to a certain version of IDAA.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 11/12 for z/OS users who use IBM                     *
  * Db2 Analytics Accelerator V7.                                *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * In our current implementation, if a                          *
  * Db2 system is paired only to the new                         *
  * accelerator type ( IBM Db2 Analytics                         *
  * Accelerator V7 or later), the user                           *
  * gets IBM Db2 Analytics Accelerator                           *
  * V5 error messages, EXPLAIN output,                           *
  * and Modeling output. This extraneous                         *
  * V5 information is confusing and                              *
  * misleading to users who never                                *
  * paired to a V5 accelerator at all.                           *
  * To best support users who only want                          *
  * to pair to the new accelerator type                          *
  * ( IBM Db2 Analytics Accelerator V7 or                        *
  * later), a new option (11 USE ONLY                            *
  * NEW ACCELERATOR_TYPE ) is                                    *
  * being added to the existing                                  *
  * QUERY_ACCEL_OPTIONS                                          *
  * subsystem parameter.                                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available.                         *
  ****************************************************************
  In the current implementation, when a Db2 system is paired only
  to the new accelerator type (IBM Db2 Analytics Accelerator V7
  or later), the user still gets IBM Db2 Analytics Accelerator V5
  error messages/EXPLAIN output/Modeling output. To avoid
  users getting this extra V5 information and to best support
  users who only want to work with the new accelerator type
  (IBM Db2 Analytics Accelerator V7 or later), a new option
  (11: USE ONLY NEW ACCELERATOR_TYPE ) is being added to the
  existing QUERY_ACCEL_OPTIONS subsystem parameter.
  
  This PTF adds a new USE ONLY NEW ACCELERATOR_TYPE option to
  the subsystem parameter QUERY_ACCEL_OPTIONS to Db2 in DSN6SPRM:
  
  - 11   : Specifies that in a Db2 environment, only IBM Db2
  Analytics Accelerator V7 or later will be recognized.
  When there exists an accelerator server lower than V7,
  Db2 will not exploit it. This includes EXPLAIN,
  accelerator modeling and query execution.
  
  When option 11 is ON, if there is no V7 accelerator
  started, but there are some V5 accelerators started,
  queries are not able to be offloaded.
  
  When option 11 is ON, for an EXPLAIN query statement,
  only V7 explain output will be populated to
  DSN_QUERYINFO_TABLE;
  
  When option 11 is ON, for modeling, only V7 behavior
  will be populated to DSN_QUERYINFO_TABLE;
  
  Note: When this option is specified, the following
  options will be ignored when also specified:
  -  8: FAVOR NEW ACCELERATOR_TYPE
  - 10: BALANCE WORKLOAD BETWEEN VERSIONS
  This is because when 11 is specified, Db2 doesn't
  exploit V5 at all, so there won't be any V5
  accelerator chosen to do the workload balancing
  even if there are active V5 accelerators available.
  
  
  PH09995 introduces 3 new reason codes for -4742:
  36
  The USE ONLY NEW ACCELERATOR_TYPE subsystem parameter
  is set to YES; however, either an active V7 or later
  accelerator was not found or the table was not enabled
  for query offloading in the V7 or later accelerator
  when the statement was executed.
  
  37
  The USE ONLY NEW ACCELERATOR_TYPE subsystem parameter
  is set to YES, but the V7 or later accelerator that
  contains the tables of the query is not started.
  
  38
  The USE ONLY NEW ACCELERATOR_TYPE subsystem parameter
  is set to YES, but the V7 or later accelerator is not at
  the correct level.
  
  Additional Keywords:
  IDAAV7R1/K, IDAAV5R1/K, SQLCODE4742
  

Problem conclusion

Temporary fix

Comments

• Accelerator behavior is being changed so that users are
  able to exploit only the new accelerator type (IBM Db2
  Analytics Accelerator V7 or above). To take advantage of this
  new improvement, users need to turn on the USE ONLY NEW
  ACCELERATOR_TYPE option to the subsystem parameter
  QUERY_ACCEL_OPTIONS.
  

APAR Information

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

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

  UI65989 UI66009

Modules/Macros

• DSNXONZB DSNXOBZO DSNWZIFB DSNWZIFC DSNXOVD  HDBBB10J DSNTIP8A
  DSNXOD5  DSNXOEX1 DSNXODML DSNXONZO DSN6SPRM DSNXIEPL DSNXOBZA
  DSNXOBZB DSNDQWPZ DSNXOOS1 DSNXOIN  DSNTINST DSNDSPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI66009

     UP19/10/30 P F910

• RC10 PSY UI65989

     UP19/10/30 P F910

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.