PI91268: INVALID DSN_PREDICAT_TABLE COLUMN LITERALS VALUE LEADING TO SQLCODE331 DSNXROHB AT POSITION 34 IN DSN_PREDICAT_TABLE

Obtain the fix for this APAR.

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 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

• RB10 PSY UI55131

     UP18/04/27 P F804

• RC10 PSY UI55130

     UP18/04/27 P F804

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.