on 07-01-2015 1:26 PM
According to note 2177857 IBM will provide tool to identify affected indexes - i don't have access to APAR database but realized that both APAR were updated on 25.06:
IBM IT08816: INCORRECT RESULTS FROM SORT OPERATIONS ON DB2 VERSION 9.7.0.10 - United States
IBM IT09073: INCORRECT RESULTS FROM SORT OPERATIONS ON DB2 VERSION 10.5.0.5 - United States
http://service.sap.com/sap/support/notes/2177857
Can anyone confirm if this tool is already available ?
It's nightmare to manually identyfy all rebuilded indexes for last few months
Regards
Marcin
Hi Marcin,
unfortunately by the time being the tool is not yet available.
see APAR IT09073 for further information:
Incorrect results from sort operations might occur when all of
the following conditions are true:
- DB2 version 10.5.0.5 (or a special build containing an
incorrect fix for APAR IT01146 <https://www-304.ibm.com/support/docview.wss?uid=swg1IT01146> ) is being used.
- The sort operation includes more than one column.
- The sort operation includes one or more VARCHAR or VARCHAR FOR
BIT DATA columns beyond the first column.
- The sort operation includes more than 500 records containing
duplicate data in the columns leading up to the VARCHAR column.
In addition to potential wrong and/or incorrectly ordered
results being returned to the user based on incorrect sort
order, it is also possible that the incorrect sort could result
in an unordered index if the index was created or rebuilt using
the problematic version of DB2. This could result in the
following symptoms:
- Index errors during insert, update or delete processing.
- Incorrect results from an index scan.
- Duplicates being inserted into a unique index.
The db2dart command can be used to detect any unordered
indexes.
Local fix
*
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, to fix an affected index, execute the
command above, recycle the instance and then perform one of the
following actions:
- Drop and recreate the affected index(es).
- Execute the REORG INDEXES command with REBUILD option to
rebuild the affected index(es).
Regards,
Javier
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, IBM released tool for check bad indexes. http://www-01.ibm.com/support/docview.wss?uid=swg21964157 And SAP released new version of note about it. http://service.sap.com/sap/support/notes/2177857 B.R. Martin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Marcin,
I've started some preparations for identify problematic indexes.
db2 select VERSIONNUMBER, VERSION_TIMESTAMP, VERSIONBUILDLEVEL from SYSIBM.SYSVERSIONS order by VERSIONNUMBER
db2 "select substr(TABNAME,1,20) as TABNAME, substr(INDNAME,1,20) as INDNAME, CREATE_TIME, COLCOUNT from syscat.indexes where CREATE_TIME > '2015-05-22' order by CREATE_TIME"
db2diag -fmt "%timestamp %message" -t 2015-05-22 -gi MESSAGE:="Rebuilding index"
Just, I'm thinking if I can use this IBM workaround and turn off BINSOTR, rebuild indexes and turn on BINSORT back. Because in SAP note isn't this as workaround.
Of course I'm starting update soon as possible.
Martin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Martin,
if you are going to rebuild the indexes anyway it is best to apply V10.5 FP5 SAP2 first since this rules out the problem for the future.
If you just rebuild the indexes with BINSORT=OFF and turn it on again afterwards, DB2 may rebuild some indexes later and you may have the problem again ( although the probability to hit this defect is low ). Running with BINSORT=OFF for a longer time has a noticable performance impact.
Regards
Frank
Thanks Frank,
But whole SAP environment update will take some time. Some time for testing is necessary too before update production systems. And break times for SAP are not easy too.
So I was thinking, if will be better rebuild indexes immediately, of course this mean repeat it often until FP5SAP2or not.
Because I had also other problem with FP5 (IT06433) and I'm using special SAP build "DB2 v10.5.0.5", "special_33964".
Now I'm waiting for answer from SAP if this APART IT06433 (my older problem) is included into FP5SAP2 too. I don't know how SAP build FP, if with all patches or ...
B.R.
Martin
Hi Frank,
Once I did inplace reorg and db crashed me and crash recovery was very slow, only 100MB/hour.
SAP built me extra package with patch for it. So, I opened OSS message also for FP5SAP2.
How do you rebuild potentially corrupted indexes? I've wrote select for it which create script with reorg commands.
db2 -x "select distinct 'reorg index all for '||TRIM(TABSCHEMA)||'.'||TRIM(TABNAME)||' rebuild;' from syscat.indexes where CREATE_TIME > (select VERSION_TIMESTAMP from SYSIBM.SYSVERSIONS where VERSIONNUMBER='10050500')"
B.R. Martin
Hi Martin,
yes, this should cover all indexes that have been created after V10.5 FP5 has been applied. Please keep in mind that FP5SAP2 does have the same VERSIONNUMBER.
Leaves indexes that have been rebuild triggered by explicit REORGs, corrupted pages ... as described in the note.
Regards
Frank
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.