In Db2 LUW simple steps which might help getting rid of corrupted pages
Biswarup(Bis)Mukherjee 120000HKTY Visits (6812)
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,
Table phase start (ID Signed: 580, Unsigned: 580; Tablespace ID: 3) :
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.
db2 inspect could be run on the entire database while database is up and running.
Performance impact is possible.
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
In the file just search for Error and see what kind of pages are corrupted in what object.
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
or, data from another good source is pulled.
If no source of recovery of the corrupted data then we can only isolate the corrupted pages and try to get back good data in the tables.
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 OMRF
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 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.