IBM Support

Hands-on example for a table rebuild with non-corrupted pages only when restore and rollforward is not available

Technical Blog Post


Abstract

Hands-on example for a table rebuild with non-corrupted pages only when restore and rollforward is not available

Body

When some pages of a table are corrupted in file system or disk level and the table is not inaccessible, you can recover the corrupted pages by restore of your backup and rollforward. However, if restore and rollforward are not allowed and you decide to give up the corrupted pages, you can follow this blog to extract non-corrupted pages ONLY from the table and rebuild the table with those non-corrupted pages to make the table accessible. 
 
 
Caution
1. This could be the post-step when a crashed database is connectable after some treatments. If your database is not connectable due to some reason, please contact db2 support team first to make your database connectable.
2. A single table is only considered in this example. If your table is under special conditions(e.g. Referential Integrity, constraints or etc), you have to consider drop/recreate/load of all related tables by yourself.
3. This hands-on example is for an inaccessible table. If the table is accessible, you may try export command instead of "db2dart /DDEL".
4. The db2dart /DDEL parameter supports only the following column data types.
: SMALLINT, FLOAT, REAL, INTEGER, TIME, DECIMAL, CHAR(), VARCHAR(), DATE, TIMESTAMP, BIGINT
For detail of db2dart, please check the following link(v11).
5. The command output could be different by version and fixpack. This hands-on example was conducted on the V11.1M3FP3 with RHEL.
 
 
Step outline
--------------------------------------------------------------------
1. confirm which table is corrupted by "db2dart"
2. extract the table DDL by "db2look"
3. extract the non-corrupted pages by "db2dart /DDEL"
4. recreate the table by "DDL from step 2"
5. load the extracted pages into the recreated table by "DEL from step 3"
--------------------------------------------------------------------
 
 
Procedure
1. confirm which table is corrupted by "db2dart"
db2dart <dbname> /DB /RPTN db2dart.<dbname>.DB.out
 
Note: You will see similar errors like as follows with the table name, tablespace ID and table ID from the output file("In pool: 3" represents tablespace 3). Please run this db2dart with offlined database since false errors may be reported.
 
Hands-on example:
--------------------------------------------------------------------
      Table inspection start: V111_331.TSTTBL1
 
         Data inspection phase start. Data obj: 4  In pool: 3
         Error: BPS check read error for pool page 9, from object ID 4, pool 3,
         Error: BPS Header Page Number incorrect. Expecting 1, Found 3803012444.
         Error: BPS Header pool relative page number incorrect. Expecting 9, Found 3511618692.
         Error: BPS Header object ID incorrect. Expecting 4, Found 58784.
         Error: Object tablespace id 3. BPS Header tablespace seed incorrect. Expecting 3, Found 3882080937.
         Error: BPS Header object type incorrect. Expecting x0, Found x9A.
         Error: BPS Header problems found
         Error: in page 1, pool page 9, of Object 4, in tablespace 3.
         Error: Page data will be dumped to report.
         Error: BPS Header revision number 0x96F1 invalid, use version 0x500 for expected BEGOFF value.
         Error: BPS Header BEGOFF value 0xC072 invalid, expected value 0x30
 
           000       *72C0 F9AC 5C5D ADE2 F196 9A2A A9DA 63E7*   *r.............c.*
           010       *840C 4FD1 A0E5 10CB 43F2 ED2A 4D01 9319*   *..O.....C...M...*
           020       *F77E 5A49 0F5A 585E 013C 2190 5600 1460*   *..ZI.ZX.....V...*
           030       *                                           **
           030  000  *3DB9 E0A4 C270 F35F 1334 54F5 75B7 FD4C*   *.....p...4T.u..L*
 
...
      Table inspection end.
--------------------------------------------------------------------
 
 
2. extract the table DDL by "db2look"
db2look -d <dbname> -e -z <schemaname> -t <tablename> -o <db2look_outputfile>
 

Note: You can check if the corrupted table has the following column data types. If the table has the other data type(e.g. LOB), it cannot be recovered.

: SMALLINT, FLOAT, REAL, INTEGER, TIME, DECIMAL, CHAR(), VARCHAR(), DATE, TIMESTAMP, BIGINT

 

Hands-on example:
--------------------------------------------------------------------
$ db2look -d tst111 -e -z V111_331 -t TSTTBL1 -o db2look.tst111.V111_331.TSTTBL1.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: V111_331
-- Specified SCHEMA is: V111_331
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
 
-- Schema name is ignored for the Federated Section
-- Output is sent to file: db2look.tst111.V111_331.TSTTBL1.ddl
...
 
$ cat db2look.tst111.V111_331.TSTTBL1.ddl
...
CREATE TABLE "V111_331"."TSTTBL1"  (
                  "C1" INTEGER ,
                  "C2" CHAR(5 OCTETS) )
                 IN "TSTTBS1"
                 ORGANIZE BY ROW;
...
--------------------------------------------------------------------
 
 
3. extract the non-corrupted pages by "db2dart /DDEL"
db2dart <dbname> /DDEL
 
Note: Please use this command with offline database for false error. You can apply tablespace and table IDs from step 1. Only non-corrupted pages will be dumped in the output DEL file. This step is exactly the same as the following link.
 
Hands-on example:
--------------------------------------------------------------------
$ db2dart tst111 /DDEL
 
 
   Table object data formatting start.
   Please enter
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)
4,3,0,9999999999999
 
   2 of 2 columns in the table will be dumped.
   Column numbers and datatypes of the columns dumped:
         0  INTEGER
         1  CHAR() -FIXED LENGTH CHARACTER STRING
   Default filename for output data file is TST111_TS3T4.DEL,
do you wish to change filename used? y/n
n
 
   Filename used for output data file is TST111_TS3T4.DEL. If the file exists, the data will be appended to it.
 
   Formatted data being dumped ...
   Dumping Page 0 ....
   Dumping Page 1 ....
   Dumping Page 2 ....
   Dumping Page 3 ....
   Dumping Page 4 ....
   Dumping Page 5 ....
   Dumping Page 6 ....
   Table object data formatting end.
 
                  DB2DART Processing completed with warning(s)!
                    DB2DART Processing completed with error!
                  Complete DB2DART report found in: TST111.RPT
--------------------------------------------------------------------
 
 
4. recreate the table by "DDL from step 2"
db2 connect to <dbname>
db2 drop table <schemaname>.<tablename>
db2 -tvf <db2look_outputfile>
 
Hands-on example:
--------------------------------------------------------------------
$ db2 connect to tst111
 
   Database Connection Information
 
Database server        = DB2/LINUXX8664 11.1.3.3
SQL authorization ID   = V111_331
Local database alias   = TST111
 
$ db2 drop table V111_331.tsttbl1
DB20000I  The SQL command completed successfully.
$ db2 -tvf db2look.tst111.V111_331.TSTTBL1.ddl
CONNECT TO TST111
 
   Database Connection Information
 
Database server        = DB2/LINUXX8664 11.1.3.3
SQL authorization ID   = V111_331
Local database alias   = TST111
 
 
CREATE TABLE "V111_331"."TSTTBL1"  ( "C1" INTEGER , "C2" CHAR(64 OCTETS) ) IN "TSTTBS1" ORGANIZE BY ROW
DB20000I  The SQL command completed successfully.
 
COMMIT WORK
DB20000I  The SQL command completed successfully.
 
CONNECT RESET
DB20000I  The SQL command completed successfully.
 
TERMINATE
DB20000I  The TERMINATE command completed successfully.
--------------------------------------------------------------------
 
 
5. load the extracted pages into the recreated table by "DEL from step 3"
db2 "load from <DEL from step 3> of del insert into <schemaname>.<tablename>"
 
Note: This load command could be different based on your environment. For the other options for load, please check the following link.
 
Hands-on example:
--------------------------------------------------------------------
$ db2 connect to tst111
 
   Database Connection Information
 
Database server        = DB2/LINUXX8664 11.1.3.3
SQL authorization ID   = V111_331
Local database alias   = TST111
 
$ db2 "load from TST111_TS3T4.DEL of del insert into V111_331.tsttbl1"
SQL3501W  The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.
 
SQL3109N  The utility is beginning to load data from file
"/home/v111_331/v111_331/NODE0000/TST111/T0000003/TST111_TS3T4.DEL".
 
SQL3500W  The utility is beginning the "LOAD" phase at time "01/30/2019
20:42:49.662377".
 
SQL3519W  Begin Load Consistency Point. Input record count = "0".
 
SQL3520W  Load Consistency Point was successful.
 
SQL3110N  The utility has completed processing.  "250" rows were read from the
input file.
 
SQL3519W  Begin Load Consistency Point. Input record count = "250".
 
SQL3520W  Load Consistency Point was successful.
 
SQL3515W  The utility has finished the "LOAD" phase at time "01/30/2019
20:42:49.770513".
 
 
Number of rows read         = 250
Number of rows skipped      = 0
Number of rows loaded       = 250
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 250
--------------------------------------------------------------------

[{"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

ibm13285765