A fix is available
APAR status
Closed as program error.
Error description
ABEND0C1 occurred processing a SELECT statement when there is an IN or NOT IN statement belong to a big CASE statement. From the dump, an indication is the Failing instruction text: 0004C0F4 00000018 E340F298 R14 points to DSNXRBND+882 at PTF level UI54157. Additional Keywords: SQLCASE
Local fix
Disable zgencode can bypass the abend Please consult IBM Db2z Client Success Team for details
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 for z/OS users of queries that * * contain a very large CASE statement that * * includes a boolean expression along with * * an IN or NOT IN clause, or an IS NULL or * * IS NOT NULL clause, or a BETWEEN clause. * **************************************************************** * PROBLEM DESCRIPTION: * * An ABEND0C1 rc01 in DSNXRBND can occur * * when processing a very large CASE * * statement for a query. The CASE * * statement includes a WHEN clause that * * contains a boolean expression with an * * IN or NOT IN clause, or an IS NULL or * * IS NOT NULL clause, or a BETWEEN * * clause where the internal optimized * * machine generated code will be over * * 64K in length. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** An ABEND0C1 rc01 can occur in module DSNXRBND when processing a very large CASE statement that contains a WHEN clause with a boolean expression with an IN or NOT IN clause, or an IS NULL or IS NOT NULL clause, or a BETWEEN clause. The abend can occur if the optimized machine generated code is over 64K in length because this causes it to generate an incorrect branch instruction. Here is a simple example. Step ONE. Create a simple Table TBL1. CREATE TABLE TBL1 (C1 SMALLINT, C2 SMALLINT, C3 INT, C4 INT, C5 CHAR(4), C6 VARCHAR(15)); Step TWO. Populate Table TBL1 with four rows. INSERT INTO TBL1 VALUES (1, NULL, 3, NULL, 'A1', 'AA'); INSERT INTO TBL1 VALUES (2, 100, 2, NULL, 'A2', 'AA'); INSERT INTO TBL1 VALUES (3, 100, 1, 100, 'A3', 'AA'); INSERT INTO TBL1 VALUES (4, 100, 0, 100, 'A4', 'AA'); Step THREE. Perform the following query with a CASE statement. SELECT C5, (CASE WHEN C1 = 5 THEN 'FIVE' WHEN C4 IS NULL AND C5 = 'A2' THEN 'TWO' WHEN C4 IS NOT NULL AND C5 = 'A3' THEN 'THREE' WHEN C5 NOT IN ('A1', 'A2', 'A3') AND C1 = 4 THEN 'FOUR' WHEN C3 BETWEEN 2 AND 4 AND C5 = 'A1' THEN 'ONE' ELSE 'NONE' END) FROM TBL1; So, for a large CASE statement, some 640+ elements could be easily result which could cause the incorrect branch instruction to be generated internally. However, please note that 200+ elements could easily occur if one of the elements is a decimal. In the above simple example, there are 12 elements in total. (1st WHEN = 1, 2nd WHEN = 2, 3rd WHEN = 2, 4th WHEN = 4, and 5th WHEN = 3), 1 + 2 + 2 + 4 + 3 is 12.
Problem conclusion
Db2 has been modified to correct the logic to generate the proper branch instruction for a large CASE statement (as illustrated). Additional Keywords: SQLCASE SQLIN SQLNOTIN SQLISNULL SQLISNOTNULL SQLBETWEEN
Temporary fix
Comments
APAR Information
APAR number
PH01732
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
2018-08-14
Closed date
2018-09-10
Last modified date
2018-10-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI58388
Modules/Macros
DSNXGCAS
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI58388
UP18/09/25 P F809
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 October 2018