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

Obtain the fix for this APAR.

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

• ž**** PE15/10/21 FIX IN ERROR. SEE APAR PI50928  FOR DESCRIPTION
  

APAR Information

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