IBM Support

Steps to determine whether APAR IT08816 or IT09073 is affecting your DB2 indexes

Troubleshooting


Problem

If you are not using and have not used a DB2® instance running on DB2 Versions 9.7.0.10 (Fix Pack 10) or 10.5.0.5 (Fix Pack 5), no further action is required. If you have used the DB2 versions and fixpack specified above, your indexes may be affected by APAR IT08816 or IT09073

Symptom

One or more of the following can occur:
Index errors during insert, update or delete processing.
Incorrect results from an index scan.
Duplicates being inserted into a unique index.

Cause

Any indexes that were created, rebuilt, or reorg in DB2 Version 9.7.0.10 (Fix Pack 10) or 10.5.0.5 (Fix Pack 5). Incorrect results from sort operations may cause indexes to become invalid.

Environment

Usage of DB2 Version 9.7.0.10 (Fix Pack 10) or 10.5.0.5 (Fix Pack 5) presently or in the past.

Diagnosing The Problem

There are two stages for determining the tables with indexes that were affected by IT08816 or IT09073. Stage A (db2CheckIndexForSortAPAR Utility Tool) is an identification tool. It narrows down the number of affected tables with matching conditions listed in IT08816 or IT09073. If you are on DB2 Version 9.7.0.11 (Fix Pack 11) or 10.5.0.6 (Fix Pack 6), run Stage B on the tables from Stage A's output. Stage B is the used to inspect the indexes internally for corruption and can be used to verify if the indexes are not corrupted by APAR IT08816 or IT09073.

Stage A1: db2CheckIndexForSortAPAR Utility Tool:
Before using this tool, please consider the restrictions of the tool:

  • At the time of updating to the affected Fix Pack, the following command must have been run:
    • db2updv97 (or db2updv105)
  • If the rotating db2diag logs or archived db2diag logs are used, the tool may miss affected tables
  • The db2diag.log must contain logs from the time of the upgrade to the affected fixpack to the current time
  • Range partitioned tables are not supported

If the list above is not satisfied, use Stage A2 instead.

  1. Download the db2CheckIndexForSortAPAR utility to your corresponding platform:

    AIX_db2CheckIndexForSortAPAR.tar.gzAIX_db2CheckIndexForSortAPAR.tar.gz
    HPIP_db2CheckIndexForSortAPAR.tar.gzHPIP_db2CheckIndexForSortAPAR.tar.gz
    LINUX390_db2CheckIndexForSortAPAR.tar.gzLINUX390_db2CheckIndexForSortAPAR.tar.gz
    LINUXAMD_db2CheckIndexForSortAPAR.tar.gzLINUXAMD_db2CheckIndexForSortAPAR.tar.gz
    LINUXPPC_db2CheckIndexForSortAPAR.tar.gzLINUXPPC_db2CheckIndexForSortAPAR.tar.gz
    NT32_db2CheckIndexForSortAPAR.zipNT32_db2CheckIndexForSortAPAR.zip
    NTX64_db2CheckIndexForSortAPAR.zipNTX64_db2CheckIndexForSortAPAR.zip
    SUN_db2CheckIndexForSortAPAR.tar.gzSUN_db2CheckIndexForSortAPAR.tar.gz
    SUNAMD_db2CheckIndexForSortAPAR.tar.gzSUNAMD_db2CheckIndexForSortAPAR.tar.gz

  2. Copy the db2CheckIndexForSortAPAR.tar.gz file into your sqllib/bin directory. To extract the db2CheckIndexForSortAPAR utility, run the following commands if on non-windows machine:
    >> gunzip db2CheckIndexForSortAPAR.tar.gz
    >> tar -xf db2CheckIndexForSortAPAR.tar

  3. Run db2CheckIndexForSortAPAR utility against each of your databases.

  4. Proceed to Stage B.

Stage A2: Alternative Method for Obtaining Tablespace ID and Table IDs:
  1. Run the following query to retrieve a list of tablespace IDs and table IDs then proceed to Stage B:
    SELECT DISTINCT A3.TBSPACEID TableSpaceID,
           A3.TABLEID TableID
    FROM SYSCAT.COLUMNS A1, SYSCAT.INDEXCOLUSE A2,
           SYSCAT.TABLES A3, SYSCAT.INDEXES A4
    WHERE A1.TABNAME = A3.TABNAME
           and A3.TABNAME=A4.TABNAME
           and A1.COLNAME=A2.COLNAME
           and TYPENAME='VARCHAR' and COLSEQ > 1
           and A4.INDNAME=A2.INDNAME
           and A1.TABSCHEMA=A3.TABSCHEMA
           and A3.TABSCHEMA=A4.TABSCHEMA
           and A4.INDSCHEMA=A2.INDSCHEMA
    WITH UR")


  2. Proceed to Stage B.

Stage B: INSPECT INDEXDATA option (only available on fixpacks DB2 9.7.0.11 or 10.5.0.6 or later releases):
  1. Execute the following db2set command. This command tells DB2 to override the default behavior for INDEXDATA option of the INSPECT command. Note that in DB2 version 10.5, you do not need to recycle the instance to pick up this change.
    >> db2set DB2_INDEX_DEBUG=ORDER_USING_INSPECT

  2. Execute the INSPECT command with the INDEXDATA option for every potentially affected table (Output from Stage A).
    Note: If no errors are detected, then the inspect output file will not be generated.
    >>db2 "INSPECT CHECK TABLE TBSPACEID <tablespace ID> OBJECTID <object ID> EXTENTMAP NONE DATA NONE BLOCKMAP NONE INDEX NONE LONG NONE LOB NONE XML NONE INDEXDATA RESULTS inspect.<tablespace ID>.<object ID>.bin"


    Alternatively, if the table name and schema are used, execute this command.
    >> db2 "INSPECT CHECK TABLE NAME <table-name> SCHEMA <schema-name> EXTENTMAP NONE DATA NONE BLOCKMAP NONE INDEX NONE LONG NONE LOB NONE XML NONE INDEXDATA RESULTS inspect.<schema-name>.<table-name>.bin"

  3. Reset the registry variable:
    >> db2set DB2_INDEX_DEBUG=


An alternative to running the INSPECT command on every table, is to run it against the entire database. If that approach is taken the LIMIT ERROR TO ALL clause should be specified. But be aware that if you have large indexes that are exposed to this problem, this may result in a significant amount of diagnostics dumped to the db2dump directory.

Resolving The Problem

To avoid hitting the problem while on an affected built, execute
the following command and then recycle the instance:
>> db2set DB2_BINSORT=off

After applying the workaround above and/or moving to a build
with this APAR fix, execute the command above and recycle the instance. Then run the db2CheckIndexForSortAPAR or the inspect tool (with INDEXDATA option) to determine the tables potentially affected by APAR IT08816 or IT09073.

To fix the indexes after identifying the tables potentially affected, run one of the commands listed below:
- Drop and recreate the affected index(es).
- On 9.7.0.10 or later release, execute the REORG INDEXES command without the CLEANUP ONLY option to rebuild the affected index(es).
- On 10.5.0.5 or later release, execute the REORG INDEXES command with REBUILD option to rebuild the affected index(es).

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Indexes","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21964157