IBM Support

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

A fix is available

Subscribe

You can track all active APARs for this component.

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

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



Document information

More support for: DB2 for z/OS

Software version: B10

Reference #: PI91268

Modified date: 14 May 2018


Translate this page: