A fix is available
APAR status
Closed as program error.
Error description
After activating FL508 and using the new zparm function - FTB_NON_UNIQUE_INDEX=YES for a non-unique data partitioned secondary index (DPSI) with 10 partitions on a UTS PBR table INCORROUT was seen for a query using a greater than predicate SELECT * FROM BKREVA.RVANOTACION WHERE EMPRESA = '0128' AND CENTRO = '0903' AND CUENTA = '0010000865' AND MERCADO = '055' AND ISIN = 'ES0113900J37' AND TIPO_ANOTACION = 'E' AND SITUACION <>'B' AND IMP20_TTL_REF > 0 Additional keywords: DB2FTB/K Additional symptoms: DSNKFTIN 5069 ERQUAL5069 which may also cause DSNILGCL 5001 ERQUAL5001. Additional Symptom: SELECT sql enters into a loop of DSNKTRAV and DSNKNXT2 on index lear pages. BBTR: 1 Relp Unl DC475D56B61DB1BC 01C9.22D1.0000.00000006 00007100 80040000 DSNKNXT2+0001C4F0 2 Getp S-L DC475D56B61DB478 01C9.22D1.0000.00000005 00007100 80040000 DSNKTRAV+00009606 3 Getp S-L DC475D56B61DB788 01C9.22D1.0000.00000006 00007100 80040000 DSNKNXT2+00017D7C ... ... 498 Getp S-L DC475D56B621A856 01C9.22D1.0000.00000005 00007100 80040000 DSNKTRAV+00009606 499 Getp S-L DC475D56B621AB6C 01C9.22D1.0000.00000006 00007100 80040000 DSNKNXT2+00017D7C 500 Relp Unl DC475D56B61DB04C 01C9.22D1.0000.00000005 00007100 80040000 DSNKNXT2+0001C4F0
Local fix
Change the query and WHERE condition to AND IMP20_TTL_REF <> 0 to solve the INCORROUT
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 V12 for z/OS users with in-memory * * index optimization function switched on * * (Index Fast Traverse Block - FTB) and with * * Function Level 508 (FL508) or higher. * **************************************************************** * PROBLEM DESCRIPTION: * * After activating FL508 and using the * * new zparm function - * * FTB_NON_UNIQUE_INDEX=YES for a * * non-unique index INCORROUT can happen * * for a query using a greater than * * predicate. For example, * * SELECT * FROM T1 * * WHERE C1 = 'INDEXKEY' AND C2 > 0; * * Additional keywords: DB2FTB/K * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** After activating FL508 and using the new zparm function - FTB_NON_UNIQUE_INDEX=YES for a non-unique index INCORROUT can happen for a query using a greater than predicate. For example, SELECT * FROM T1 WHERE C1 = 'INDEXKEY' AND C2 > 0; Additional keywords: DB2FTB/K
Problem conclusion
DB2 has been modified to return the correct result for queries with greater than predicates when using FTBs for non-unique indexes.
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PH43565
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2022-01-24
Closed date
2022-02-14
Last modified date
2023-01-09
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI79317
Modules/Macros
DSNFIDIR DSNK1CNE DSNKFTDL DSNKDPG DSNFCDIR DSNKTRAV DSNKFTOM DSNKIXDB DSNKFTBU DSNKFTOB DSNKFTIN DSNKINSN
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI79317
UP22/02/23 P F202 ¢
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"DB2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
09 January 2023