Technical Blog Post
Abstract
Hands-on example for a table rebuild with noncorrupted pages only when restore and rollforward are 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 noncorrupted pages ONLY from the table and rebuild the table with those noncorrupted pages to make the table accessible.
Caution
1. This technote could be the poststep when a crashed database is connectable after some treatments. If your database is not connectable due to some reason, 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(for example, Referential Integrity, constraints or etc), you have to consider drop/re-create/load of all related tables by yourself.
3. This hands-on example is for an inaccessible table. If the table is accessible, you can 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, check the following link(v11).
5. The command output could be different by version and fix pack. 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 noncorrupted pages by "db2dart /DDEL"
4. Re-create the table by "DDL from step 2"
5. Load the extracted pages into the re-created 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, table space ID and table ID from the output file("In pool: 3" represents table space 3). Run this db2dart with offline database since false errors can 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 whether the corrupted table has the following column data types. If the table has the other data type(for example, 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 noncorrupted pages by "db2dart /DDEL"
db2dart <dbname> /DDEL
Note: Use this command with offline database for false error. You can apply table space and table IDs from step 1. Only noncorrupted pages are dumped in the output DEL file. This step is 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. Re-create 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 re-created 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. Especially if your database is recoverable(logarchmeth1 or logarchmeth2 is set to value other than OFF), the table space in which the table resides is placed in backup pending state by this load command. If you have multiple tables for recovery in one table space, consider to use NONRECOVERABLE option for each load and run table space or DB backup after all table loads are done. For details for load, 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":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm13285765