PM89966: INCORRECT OUTPUT WITH UNIQUE WHERE NOT NULL INDEX THAT HAS AN INCLUDE COLUMN

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • CREATE TABLE EMP2 (EMPNO CHAR(6) NOT NULL,
    NUM DECIMAL(8) WITH DEFAULT NULL)
    IN DATABASE DSNDB04;
    COMMIT;
    INSERT INTO EMP2 VALUES('000001',1);
    INSERT INTO EMP2 VALUES('000002',2);
    INSERT INTO EMP2 VALUES('000003',3);
    INSERT INTO EMP2 VALUES('000004',4);
    INSERT INTO EMP2 VALUES('000005',5);
    INSERT INTO EMP2 VALUES('000006',6);
    INSERT INTO EMP2 VALUES('000007',7);
    INSERT INTO EMP2 VALUES('000008',7);
    INSERT INTO EMP2 VALUES('000009',NULL);
    INSERT INTO EMP2 VALUES('000011',NULL);
    INSERT INTO EMP2 VALUES('000010',10);
    
    Query without the include index returns the correct result:
    SELECT EMPNO FROM EMP2
      WHERE EMPNO = '000003'
    
    EMPNO
    --------
    000003
    
    CREATE UNIQUE WHERE NOT NULL INDEX IXEMP21
      ON EMP2 (EMPNO) INCLUDE(NUM) ;
    
    with index, there is no row found..
    DSNE610I NUMBER OF ROWS DISPLAYED IS 0
    
    
    
    Keyword
    DB2INCORR/K SQLINCORR SQLINCORROUT
    

Local fix

  • defect
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All V10, V11 DB2 for z/OS users using        *
    *                 UNIQUE WHERE NOT NULL indexes when           *
    *                 INCLUDE columns are present.                 *
    ****************************************************************
    * PROBLEM DESCRIPTION: Incorrect output was returned by        *
    *                      DB2 when access of an index with        *
    *                      an INCLUDE column was performed.        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The incorrect output was returned by DB2 when access of a
    UNIQUE WHERE NOT NULL index (that contained at least 1
    INCLUDE column) was performed.
    
    Additional Keywords: DB2INCORR/K, INCORROUT, SQLINCORROUT
    

Problem conclusion

  • DB2 was incorrectly scanning UNIQUE WHERE NOT NULL indexes
    when at least 1 INCLUDE column was present.  Code within
    DB2 has been corrected to account for all columns of a
    UNIQUE WHERE NOT NULL index when an INCLUDE column is present.
    

Temporary fix

  • *********
    * HIPER *
    *********
    

Comments

APAR Information

  • APAR number

    PM89966

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-05-29

  • Closed date

    2014-09-26

  • Last modified date

    2014-11-04

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UI21748 UI21749

Modules/Macros

  •    DSNIKESE DSNIOST2 DSNIRIDR DSNKBULD DSNKCEST
    DSNKDLE  DSNKFLRR DSNKFLST DSNKFPRV DSNKFTCH DSNKFTRR DSNKINSL
    DSNKLOD2 DSNKMDEL DSNKNXT2 DSNKPOSI DSNKREST DSNXOTL
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI21748

       UP14/10/13 P F410

  • RB10 PSY UI21749

       UP14/10/13 P F410

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM89966

Modified date:

2014-11-04

Translate my page

Machine Translation

Content navigation