PH41355: AB0C4 DSNLZR00 +0CC02 FOR ACCELERATION OF QUERY WITH SELECT-LISTOUTPUT THAT INCLUDES EXPRESSION RESULT IN DECIMAL FLOATING-POINT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Db2 12 for z/OS user requested QUERY ACCELERATION for dynamic
  query with topmost SELECT list output result that includes
  an expression that results in DECIMAL FLOATING POINT (as
  determined by Db2z) where,
  1. The user table(s) is accelerated on both IBM Db2 Analytics
     Accelerator V5 and V7, and both Accelerators are active/
     STARTed on the same Db2z subsystem.
  2. SET CURRENT ACCELERATOR specifies Accel V5 as the target
     Accelerator, forcing Db2z to choose the AccelV5 instead of
     the available Accel V7.
  .
  The following abend occurred during the PREPARE for dyn query:
  ABEND0C4 RC00000004 DSNLZGLM . DSNLZR00 OFFSET0CC02
  .
  .
  The problem does NOT occur if ANY one of the following is true:
   - SET CURRENT ACCELERATOR = AccelV5 is NOT used to force Db2z
     to choose Accel V5 when Accel V7 is available, OR
   - The SELECT-list output result includes a basic DECFLOAT
     column instead of the expression.
     The SELECT-list output item must be an "expression"
     that returns the DECFLOAT datatype as determined by Db2 for
     z/OS (where Db2z uses an implicit cast to DECFLOAT).
   - OR Accel V5 is the only available / STARTed Accelerator for
     this query in this Db2z subsystem.
  .
  The problem does not occur for this case as a static query.
  .
  .
  Additional search keywords:
   IDAAV5R1/K IDAAV7R1/K IDAAV7R5/K SQLDECFLOAT
  

Local fix

•  To avoid the problem and abend, remove SET CURRENT ACCELERATOR
   so that Db2z can choose the available and active Accel V7.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS users of IBM Db2                         *
  * Analytics Accelerator for z/OS and QUERY                     *
  * ACCELERATION for dynamic query having,                       *
  *  1. an expression in the SELECT-list output                  *
  *     that results in DECFLOAT implicit-cast                   *
  *  2. SET CURRENT ACCELERATOR is used to                       *
  *     specify an Accelerator V5 as target                      *
  *  3. both Accel V5 and Accel V7 are used for                  *
  *     the query                                                *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * During execution of the application,                         *
  * the following DB2 for z/OS DDF                               *
  * application abend occurred during                            *
  * the PREPARE for a dynamic query:                             *
  *   AB0C4 RC00000004 DSNLZR00 +0CC02                           *
  * when user requested QUERY ACCELERATION                       *
  * for a dynamic query and all of the                           *
  * following conditions applied:                                *
  *  1. Top-most SELECT-list output result                       *
  *     includes an expression for which                         *
  *     Db2z applies a DECFLOAT implicit-                        *
  *     cast.                                                    *
  *  2. Query's tables are accelerated on                        *
  *     both IBM Db2 Analytics Accelerator                       *
  *     V5 and V7, and both Accelerators                         *
  *     are active/STARTed on same Db2z                          *
  *     subsystem.                                               *
  *  3. SET CURRENT ACCELERATOR specifies                        *
  *     Accelerator V5 as the preferred                          *
  *     target Accelerator for the PREPARE                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  Db2 12 for z/OS user requested QUERY ACCELERATION for a dynamic
  query having "top-most" SELECT-list output result that includes
  a string expression for which Db2z applies an internal DECFLOAT
  implicit cast. An example of such a query is the following
  where the SELECT-list specifies a SUBSTR expression resulting
  in a string expression:
    SELECT C1_INT, C2_CHAR,
         (SUBSTR(DIGITS(C3_DEC),16 , 1) + 1000) AS DECFLT_EXPR
     FROM owner-name.table-name
     WHERE C1_INT < 100
     FOR FETCH ONLY;
                                                                .
  Additionally, both of the following conditions applied:
   1. Query's tables are accelerated on both IBM Db2 Analytics
      Analytics Accelerator V5 and V7, and both Accelerators
      are active/STARTed on same Db2z subsystem, and
   2. the user specified SET CURRENT ACCELERATOR = Accelerator V5
      as the preferred target Accelerator to use during the
      PREPARE of the dynamic query, if the Accel V5 can support
      the query (otherwise, the Accel V7 should be chosen
      according to the behavior rules for CURRENT ACCELERATOR).
                                                                .
  When application was run, the following Db2z DDF application
  abend occurred during the SQL PREPARE of the dynamic query:
    ABEND0C4 RC00000004 DSNLZGLM . DSNLZR00 OFFSET0CC02
                                                                .
  Db2z does not support acceleration to Accelerator V5 for query
  with a top-most SELECT-list output expression for which Db2z
  applies a DECFLOAT implicit-cast, and Db2z should disqualify
  Accel V5 as a target for this query and instead use the AccelV7
  if applicable and available.
                                                                .
  However, the abend occurred because during the PREPARE, after
  Db2z correctly disqualified the AccelV5 for the query, Db2z
  later allowed the SET CURRENT ACCELERATOR = AccelV5 to override
  that Db2z decision. This resulted in query being incorrectly
  and partially bound for acceleration to Accel V5 instead of for
  the expected Accel V7, which then led to the reported Db2z DDF
  DSNLZR00 application abend during the PREPARE.
                                                                .
  To avoid the problem and the Db2z abend, remove SET CURRENT
  ACCELERATOR = AccelV5 or change the preferred target to
  Accel V7.
                                                                .
  This problem "only" occurs specifically for a DECFLOAT implicit
  cast in the topmost SELECT-list output and both Accel V5 &
  Accel V7 contain the user table(s), and no other case.
  It does not occur when,
   - the query's tables are only accelerated on Accel V5,
   - the DECFLOAT implicit-cast occurs in a subSELECT-list
     (this specific case is allowed for target Accel V5),
   - an explicit DECFLOAT column is in "any" SELECT-list or
     subSELECT-list output result, as DB2z correctly disqualifies
     Accel V5 for an explicit DECFLOAT column, even when CURRENT
     ACCELERATOR = AccelV5.
                                                                .
  The problem does *not* occur for this same scenario for static
  query where ACCELERATOR bind option applies instead of CURRENT
  ACCELERATOR special register (for dynamic statements only).
                                                                .
                                                                .
  

Problem conclusion

• Db2z was changed to correctly disqualify the Accel V5 for
  the query scenario with a DECFLOAT implicit-cast as previously
  described when SET CURRENT ACCELERATOR specifies Accel V5.
  .
  Search keywords:
   SQLDECFLOAT DB2DDF
   IDAAV5R1/K IDAAV7R1/K IDAAV7R5/K
  

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:

  UI79310

Modules/Macros

• DSNXONZO
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI79310

     UP22/02/23 P F202

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.