Technical Blog Post
Abstract
In Db2 LUW simple steps which might help getting rid of corrupted pages
Body
Recently following simple guidelines helped customers to get rid of corrupted pages easily.
So, just putting it here in case it helps.
Customer's db2 inspect output was showing following kind of corruptions,
DATABASE: TESTDB
VERSION : SQL09053
2018-02-01-14.45.44.077383
Action: CHECK TABLE
Schema name: DB2INST1
Table name: MY_TAB
Tablespace ID: 3 Object ID: 580
Result file name: badpage.out
Table phase start (ID Signed: 580, Unsigned: 580; Tablespace ID: 3) :
Data phase start. Object: 580 Tablespace: 3
Error: In page 4543, physical page 4543 of object 580 in tablespace 3, pagesize 32768.
Error: Hexadecimal dump of page contents.
0000 *3000D07F EE160000 00040000 00000000* *0...............*
0010 *BE110000 47020002 EA9665A6 BE110000* *....G.....e.....*
0020 *02000000 80808000 80808080 00000000* *................*
Following guideline was provided with which the corrupted pages were isolated.
First of all find any disk / filesystem issue which might be the source of corruption and fix it.
Then, run a full database check to find the extent of corruption in the database.
If the db size is not that big then prefer to run db2dart during a maintenance window.
db2dart TESTDB /DB
which will show the full database check report in a TESTDB.RPT file.
Then, check the .RPT file with following to find how many pages are corrupted.
$ grep "in page" TESTDB.RPT |uniq
If the Error is reported under "Table inspection " it's data corruption.
If the Error is reported under "Index inspection" it's index corruption.
If index corruption is faced then run,
db2dart TESTDB /MI /TSI <tablesapce-id> /OI <object-id>
That will make the index invalid which will be rebuilt when next activate db is run
or, access the object is done based on how the INDEXREC db/dbm config is set.
If the corruption is in data page like the one showed above then that data is lost.
So, there is no way to get back the data unless restore from a good backup is done
To do that,first initialize the content of the corrupted page.
db2dart TESTDB /IP
hit enter and that will ask for 5 parameters :
Table ID or name, tablespace ID, first page, num of pages, password:
(suffix page number with 'p' for pool relative)
Example from the above will be,
580 3 4543 1 OMRFVJPC <- Last parameter is service password which is to be obtained from Db2 Support team.
Repeat above step for any other data page corruption.
After initialization of the corrupted pages the export of the data should work without crashing the instance.
In that case export the good data, drop the table and recreate it back from the DDL saved and then load
back the exported data.
In case a crash is still hit while trying to export data then the need is to take out all the good data from the table using
db2dart /DDEL
db2dart TESTDB /DDEL
and hit enter
that will ask for,
Table ID or name, tablespace ID, first page or logical row, num of pages or logical rows:
(may suffix page number with 'p' for pool relative if working with a pool-relative tablespace)
Example with the corruption shown above it will be,
580 3 0 99999999
That will pull out all the good data out of the table and put in TESTDB.RPT file in delimited format.
After that drop the table, recreate back the table from saved DDL and load back the data.
Repeat this for any other table which might face similar issue.
For more involved and complex corruption issues please don't hesitate to open cases with IBM Db2 Support team.
UID
ibm13285723