Db2: V13 Access Path regression: Tablespace scan regresses to nonmatching index scan. After turning this in as a problem, we were told that it was working as designed. Our suggestion is that the design is flawed.

We feel the design is flawed and strongly feel this is a bug. 

The only time a non-matching index scan should be considered versus the tablespace scan is when it can at least filter the result somewhat. There are ZERO columns in the index that match any of the columns in the WHERE predicate.

Here's the index definition:

CREATE UNIQUE INDEX D761BB.IZPKG1

ON D761BB.TZPKG

(TBL_PTN_NR ASC,

PKG_TCK_NR ASC,

CNY_CD ASC,

PSL_CD ASC,

DAT_TYP_CD ASC)

USING STOGROUP G761SG1

PRIQTY 7200 SECQTY 7200

ERASE NO

FREEPAGE 5 PCTFREE 10

GBPCACHE CHANGED

CLUSTER

COMPRESS NO

INCLUDE NULL KEYS

BUFFERPOOL BP4

CLOSE NO

COPY NO

DEFER NO

DEFINE YES

PIECESIZE 2 G;

COMMIT;

Here's the query:

SELECT TBL_PTN_NR , PKG_TCK_NR , CNY_CD , PSL_CD , DAT_TYP_CD ,

REC_INS_TS , REC_UDT_TS , REC_PRG_IR , PKG_CLA_LST_PU_DT ,

PKG_DEL_ATP_NTF_CD , DEL_NTC_NR , CNS_NA , STP_POL_DIV_2_NA ,

STP_POL_DIV_3_NA , STP_BDG_FLR_NR , STP_RM_SUT_NR , STP_STR_NA ,

STP_STR_NR , STP_STR_PFX_NA , STP_STR_SUF_NA , STP_STR_TYP_CD ,

STP_PSL_1_CD , STP_PSL_2_CD , SUC_CAL_IR , LOC_ID_CGY_CD ,

LOC_ID_VLU_TE , RMK_TE

FROM D761BB.TZPKG

WHERE REC_PRG_IR <> 'Y' -- not in index

AND PKG_DEL_ATP_NTF_CD NOT IN ( ' ' , '97' ) -- not in index

AND REC_UDT_TS >= CURRENT TIMESTAMP - :H HOURS -- not in index

AND SUC_CAL_IR IN ( 'N' , ' ' ) -- not in index

AND RMK_TE <> ' ' WITH UR -- not in index

How can the non-matching index path even be considered since it would need to go to the entire tablespace scan to satisfy the WHERE predicates?  Awaiting your feedback, please. Thank you.

Steven Di Spigna