This APAR applies only to indexes which have been created with
the new RANDOM keyword for the index keypart order. The RANDOM
keyword was introduced in the V10.5.1 product.
If an ALTER TABLE statement with the DROP COLUMN option is run
on the table with the index with random ordering, then the
packed descriptor for the index in the system catalog might
become corrupted.
The index packed descriptor corruption could surface in various
ways, or go unnoticed for some time. The packed descriptor
corruption might lead to index corruption, which could then lead
to a trap or incorrect results. It might be difficult to detect
if incorrectly returned results.
Because of the various errors that might occur because of this
index corruption, it is recommended that any user using an index
with random ordering move up to a level with the fix and run the
db2cat command to see if they are affected by the index
corruption issue (see the local fix section of the APAR for more
details).
One example of an error that could surface as a result of the
corruption is the following probe point which might be seen in
the db2diag.log file if a trap occurs:
2013-11-17-05.13.04.134876-300 I744884390A549 LEVEL:
Severe
PID : 21889084 TID : 79920 PROC :
db2sysc 1
INSTANCE: db2usr1 NODE : 001 DB :
SAMPLEDB
APPHDL : 1-58998 APPID:
9.26.97.181.41845.131117184210
AUTHID : DB2USR1 HOSTNAME: host1.torolab.ibm.com
EDUID : 79920 EDUNAME: db2agent (SAMPLEDB) 1
FUNCTION: DB2 UDB, index manager, procLeaf2Del, probe:2
DATA #1 : Hexdump, 4 bytes
0x070000005F3F65C4 : 8709 002C
A stack traceback such as the following could be produced in
this case:
0x09000000005A5B50 pthread_kill + 0xB0
0x09000000099F7150 sqloDumpEDU + 0xD0
0x090000000AC5B5A4
@159@sqlischdDumpData__FP7SQLI_CBP11SQLI_SDGLOBi + 0x2DC
0x0900000009222DC4 sqlischd__FP7SQLI_CBP11SQLI_SDGLOBUsUi +
0x8F4
0x09000000091DE754 sqlischd__FP7SQLI_CBP11SQLI_SDGLOBUsUi +
0x730
0x09000000091DDA34
sqlidelk__FP8sqeAgentP9SQLD_IXCBP10SQLI_IXPCRP8SQLD_KEY8SQLZ_RID
UlP16SQLB_OBJECT_DESC + 0x404
0x09000000091DC614
sqldKeyDelete__FP13SQLD_DFM_WORKP8SQLD_TCBP13SQLD_TDATARECP15SQL
D_TDATAREC328SQLZ_RIDiUl + 0x7C0
0x09000000091D8E00 sqldRowDelete__FP8sqeAgentP8SQLD_CCBUlPPv +
0x11A8
0x09000000091D992C sqlridel__FP8sqlrr_cb + 0x15C
0x090000000910FEAC sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm +
0x74
0x09000000093880B8
sqlrr_execimmd__FP14db2UCinterfaceP16db2UCprepareInfo + 0x11E8
0x0900000009386FB8
sqlrr_execimmd__FP14db2UCinterfaceP16db2UCprepareInfo + 0xE8
0x09000000086CAB78
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
interface + 0xC124
0x0900000008718A84 @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
+ 0x9A3C
0x0900000008718A84 @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
+ 0x9A3C
0x09000000086ED2DC @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
+ 0x1DB4
0x09000000081654F8
@72@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA8
0x0900000008165FE4
@72@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x5E0
0x0900000009BF475C RunEDU__8sqeAgentFv + 0x3EA28
0x0900000009C44C04 RunEDU__8sqeAgentFv + 0xDC
0x0900000008C9A7BC EDUDriver__9sqzEDUObjFv + 0x124
0x090000000993CB10 sqloEDUEntry + 0x38C
If a table does have indexes with RANDOM ordered columns
defined, then the db2cat command should be run against the table
error to identify whether this is indeed the error. Prior to
applying the fix for this APAR, the db2cat command will not
automatically report the corruption. However, manual inspection
can be done as follows to verify if this APAR is the cause of
your index corruption.
For indexes with RANDOM order the db2cat output will contain two
lists of the keyparts of the index. The second list of keyparts
will have one or more additional keyparts with Keypart ID =
65534. The corresponding columns in each of the keypart lists
should be examined to ensure that their Keypart ID's are the
same in each list. If they are not then this APAR fix should be
applied. In order to compare keyparts between the lists, start
with the keyparts in the first list and compare them one at a
time to the keyparts in the second list. If you encounter a
Keypart ID of 65534 in the second list, just skip past it and
compare to the next keypart. For example, as seen in the output
below, the Keypart ID of keypart 1 from the first list should be
compared to Keypart ID of keypart 2 from the second list because
we need to skip over keypart 1 in the second list. Next, the
Keypart ID of keypart 2 from the first list should be compared
to the Keypart ID of keypart 3 from the second list. In the
sample output below, the Keypart ID for Keypart 2 is 10 in the
first set of keyparts but the Keypart ID value is 9 for the
corresponding Keypart 3 in the Extend set of keyparts. The
Keypart ID's are evidence of index corruption. Also, the Keypart
ID's for Keypart 4 in the first set of keyparts and Keypart 5 in
the second set are also different. Both are evidence of the
corruption which can occur from the problem described in this
APAR.
For example. assume that the the following table and index
definitions have been corrupted:
db2 "create table t1 (c1 float, c2 char(176), c3 decimal
(5,0), c4 smallint)"
db2 "create index i1 on t1 (c1 random, c2, c3 desc, c4)"
Running the following db2cat command such
as:
db2cat -d sampledb -s db2usr1 -n t1 -o <output file>
will result in output like the following written to the output
file. In this sample output, the Keypart ID for Keypart 2 is 10
in the first set of keyparts but the Keypart ID value is 9 for
the corresponding Keypart 3 in the Extend Index Info set of
keyparts. The Keypart ID's for Keypart 4 in the first set of
keyparts and Keypart 5 in the second set are also different.
This is evidence of the corruption.
No. of keys : 4
No. of Unique keys : 4
Keypart 1
Keypart ID : 5
Keypart flags : x10
Codepage : 0
Description of keypart
Type , Length : FLOAT/DOUBLE , 8
Nullable : 1
Keypart 2
Keypart ID : 10
Keypart flags : x0
Codepage : 1208
Description of keypart
Type , Length : CHAR , 176
Nullable : 2
Keypart 3
Keypart ID : 15
Keypart flags : x1
Codepage : 0
Description of keypart
Type , Length : DECIMAL , 5.0
Nullable : 1
Keypart 4
Keypart ID : 9
Keypart flags : x0
Codepage : 0
Description of keypart
Type , Length : SMALL INTEGER , 2
Nullable : 1
...
Extend Index Info
...
No. of keys : 5
No. of Unique keys : 5
Keypart 1
Keypart ID : 65534
Keypart flags : x0
Codepage : 0
Description of keypart
Type , Length : SMALL INTEGER , 2
Nullable : 2
Keypart 2
Keypart ID : 5
Keypart flags : x0
Codepage : 0
Description of keypart
Type , Length : FLOAT/DOUBLE , 8
Nullable : 1
Keypart 3
Keypart ID : 9
Keypart flags : x0
Codepage : 1208
Description of keypart
Type , Length : CHAR , 176
Nullable : 2
Keypart 4
Keypart ID : 15
Keypart flags : x1
Codepage : 0
Description of keypart
Type , Length : DECIMAL , 5.0
Nullable : 1
Keypart 5
Keypart ID : 10
Keypart flags : x0
Codepage : 0
Description of keypart
Type , Length : SMALL INTEGER , 2
Nullable : 1 |
