IBM Support

In Db2 LUW simple steps which might help getting rid of corrupted pages

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.

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 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.

 

 

 

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13285723