A fix is available
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 number
PH41355
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2021-10-18
Closed date
2022-02-14
Last modified date
2022-03-01
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.
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"DB2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0"}]
Document Information
Modified date:
02 March 2022