PH43565: INCORROUT WITH FTB AND NON-UNIQUE INDEXES WITH GREATER THAN PREDICATE

Obtain the fix for this APAR.

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