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.
- Download the db2CheckIndexForSortAPAR utility to your corresponding platform:
- 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
- Run db2CheckIndexForSortAPAR utility against each of your databases.
- Proceed to Stage B.
Stage A2: Alternative Method for Obtaining Tablespace ID and Table IDs:
- 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")
- Proceed to Stage B.
- 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
- 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"
- 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).
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21964157