PM60170: CODE TO SUPPORT NEW FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Code to support new function.
  IDAAV2R1/K
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 9 and DB2 10 for z/OS who   *
  *                 use IDAA (IBM DB2 Analytics Accelerator)     *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1. Queries that include data encoded by *
  *                      multi-byte character set EBCDIC         *
  *                      encoding scheme are now allowed to      *
  *                      execute on IDAA.                        *
  *                                                              *
  *                      2. DDL is added for creating the        *
  *                      DSN_QUERYINFO_TABLE explain table       *
  *                      and its containing table spaces and     *
  *                      indexes to installation job DSNTIJSG    *
  *                      and sample part DSNTESC.                *
  *                                                              *
  *                      3. Multiple accelerator query           *
  *                      offloading is added                     *
  *                                                              *
  *                      4. CPU reduction for query when a       *
  *                      table is not defined on the accelerator *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  1. Queries that include data encoded by multi-byte character set
  EBCDIC encoding scheme will now be allowed to execute on IDAA.
  However, since IDAA encodes the same data in the UTF-8 Unicode
  encoding scheme, the different collating sequences can cause
  a difference in result sets.
  The collating sequence for Unicode is numeric, uppercase
  characters, and then lower case characters (1, 2, 3, ..., A,
  B, C, ..., a, b, c, ...). In EBCDIC, the collating sequence
  is lower case, upper case, and then numeric (a, b, c, ...,
  A, B, C, ..., 1, 2, 3, ...). There are also differences in
  collating for the national characters. This affects both data
  ordering and the results from range predicates. Therefore, if
  the tables include character columns where more than one of
  these groups can be found in the column values and the SQL
  statements include range predicates or ordering on these
  columns, a query executed in DB2 may return a different
  result set than the same query executed in IDAA.
  
  2. In V10 this APAR adds DDL for creating DSN_QUERYINFO_TABLE
  explain table and its containing table spaces and indexes to
  installation job DSNTIJSG and sample part DSNTESC.  This table
  contains information about the eligibility of query blocks for
  automatic query rewrite, information about the materialized
  query tables that are considered for eligible query blocks,
  reasons why ineligible query blocks are not eligible, and
  information about acceleration of query blocks.
  
  3. If there is more than one accelerator qualified to offload a
  particular query, the first qualified accelerator will now be
  chosen.  A "qualified" accelerator is one that is active and
  contains all referenced tables in the query.
  
  4. When DB2 is enabled for IDAA offload and the DB2 dynamic
  statement cache (DSC) is active, for a query that is not
  offloaded to IDAA because the referenced tables are not defined
  on IDAA, after the initial PREPARE and insert into the DSC DB2
  will no longer automatically re-evaluate the query for
  potential IDAA offload on each PREPARE of that query before
  searching the DSC for a query match.  Instead, DB2 will
  consider this case a 'never offload to IDAA' situation, and
  after the initial PREPARE and insert into the DSC, DB2 will
  always search for a query match in the DB2 DSC first, as
  DB2 currently does for other queries that can 'never
  offload to IDAA'.  Therefore, after applying this fix, if the
  user at some point wants DB2 to re-evaluate for IDAA
  offload those cached queries whose table(s) are not defined
  on IDAA, the user must take explicit action to remove the
  cached query from the DSC.
  
  Additional Keywords:
  IDAAV2R1/K SQLEXPLAIN
  

Problem conclusion

Temporary fix

Comments

• 1. To enable the offloading to IDAA of queries that include data
  encoded by multi-byte character set EBCDIC encoding scheme,
  the new subsystem parameter QUERY_ACCEL_OPTIONS should be set
  to 1.  The default of this new subsystem parameter is NONE.
  See the HOLD data for more information on setting this
  parameter.
  
  Items 2 through 4 do not require any changes to subsystem
  parameters.
  

APAR Information

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

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

  UK79404 UK79405

Modules/Macros

• DSN@XAZP DSNDQWPZ DSNDSPRM DSNTESC  DSNTIDXA
  DSNTIDXB DSNTIJSG DSNTIJUZ DSNTINST DSNTINS3 DSNTXAZP DSNWZIFA
  DSNWZIF9 DSNXOALS DSNXOEX1 DSNXOMPS DSNXONZA DSNXONZC DSNXONZO
  DSNXONZS DSNXOOS1 DSN6SPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK79404

     UP12/06/23 P F206

• R910 PSY UK79405

     UP12/06/23 P F206

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.