A fix is available
APAR status
Closed as program error.
Error description
After BIND or EXPLAIN DSN_PREDICAT_TABLE is populated with some invalid chars in column LITERALS, which gives SQLCODE -331 when selected directly. SELECT HEX(LITERALS) FROM DSN_PREDICAT_TABLE; +------------------------ | HEX(LITERALS) +------------------------ 1_| 2D000000000000000000F4 << this is invalid value 2_| 2D32313338363233323139 << this is valid literal predicate Additional symptoms: ABEND04E RC00E70005 AT DSNXECK DSNXEFDA M500 (caller DSNX0D2) on BIND with EXPLAIN(YES) or EXPLAIN ALL of a query. Keywords: SQLEXPLAIN SQLCODE331
Local fix
remove invalid rows from DSN_PREDICATE_TABLE
Problem summary
**************************************************************** * USERS AFFECTED: All users of Db2 11 and Db2 12 for z/OS * * with predicates including a partitioning * * key, and the data type of the partitioning * * key is SMALLINT, INTEGER or BIGINT. * **************************************************************** * PROBLEM DESCRIPTION: Db2 may return SQLCODE -331 when doing * * SELECT LITERALS FROM DSN_PREDICAT_TABLE * * if the Explained query includes a * * predicate referencing a partitioning * * key, and the partitioning key is * * SMALLINT, INTEGER or BIGINT. * * Also, ABEND04E RC00E70005 in * * DSNXEFDA:M500 called from DSNXOD2 can * * occur during BIND with EXPLAIN(YES). * **************************************************************** * RECOMMENDATION: * **************************************************************** If a query contains a predicate including a partitioning key, Db2 may build an additional page range predicate. When the partitioning key is SMALLINT, INTEGER or BIGINT data type, Db2 does not convert the LITERALS in the page range predicate to a valid UNICODE value. After explaining the query, Db2 populates the invalid LITERALS value to DSN_PREDICAT_TABLE, which results in getting SQLCODE -331 when doing SELECT LITERALS FROM DSN_PREDICAT_TABLE. Also, ABEND04E RC00E70005 in DSNXEFDA:M500 called from DSNXOD2 can occur during BIND with EXPLAIN(YES). Example: CREATE TABLE T1 ( C1 INTEGER NOT NULL WITH DEFAULT, C2 CHAR(4) NOT NULL WITH DEFAULT) PARTITION BY (C1 ASC) (PARTITION 1 ENDING AT (100) ,PARTITION 2 ENDING AT (200) ); EXPLAIN PLAN SET QUERYNO=1 FOR SELECT * FROM T1 WHERE C1=50; Db2 may build a page range predicate as C1<=100, but Db2 does not convert 100 to a valid UNICODE value, so Db2 may return SQLCODE -331 when SELECT LITERALS FROM DSN_PREDICAT_TABLE. Another possible symptom is that the invalid value could cause ABEND04E RC00E70005 in DSNXEFDA:M500. Additional Keywords: SQLCODE331 PAGERANGE ADMININFOSQL SQLEXPLAIN ABEND04E RC00E70005 DSNXEFDA M500 DSNXOD2
Problem conclusion
Code was updated to avoid SQLCODE -331 and avoid the abend.
Temporary fix
Comments
APAR Information
APAR number
PI91268
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2017-12-08
Closed date
2018-04-09
Last modified date
2018-05-14
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI55130 UI55131
Modules/Macros
DSNXOPG
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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":"11.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":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
14 May 2018