If a table in the database gets corrupted because of any reason (say a hardware problem), attempts to access the table through SQL may not work and db2 might drop all the connections to the database and even mark the database as BAD.
In such cases you may not be able to extract data using the EXPORT utility. If this happens, you may be able to extract data from the corrupted table(s) and rebuild it with DB2DART using the /DDEL option.
DB2DART examines databases for architectural correctness and reports any encountered errors. It should always be run without any users connected to the database.
Below is a screenshot of a corrupt table entry in the db2diag.log:
Note: Before running the DB2DART process on the corrupted table it is recommended to run runstats on the table.
1. To find the corrupted pages in the table run the following command:
db2dart <DBSID> /t /tsi <tablespace-id> /tn <table name>
To run the db2dart on the entire tablespace the following command can be used:
db2dart <DBSID> /ts /tsi <tablespace-id>
The tablespace id can be found by querying the syscat.tables table:
db2 –x “select tbspaceid, tbspace from syscat.tables where tabname=’TABNAME’”
The above command will give you the Tablespace ID and the tablespace name.
2. The reports of the db2dart utility are stored in the db2dump directory by default.
Following is the extract from the db2dart report (db2dart.rpt file) that indicates the corrupted pages in the table.
Traversing extent map for:
Object ID: 26112
DAT extent anchor: 62888
INX extent anchor: 23016
XDA extent anchor: 0
LF extent anchor: 0
BKM extent anchor: 0
Table space: 11
Traversing extent map for object type: 0
Extent Map traversal complete.
Table inspection start: SAPRC.SSCOOKIE
Data inspection phase start. Data obj: 26112 In pool: 11
Error: Attempting to access a page (16777258) which does not exist. The total number of pages in this tablespace is 1010488.
Error: Unable to read pool page 16777258, from pool 11,
Error: parent pool 11, object ID 26112, object type Data.
Error: extent map anchor page of object is 62888.
Error: Failed to determine object relative page number, for given pool page 16777258 of object ID 26112 pool 11 type (x0)
Error: Pointer row pointing to invalid pool page 16777258 for slot 8 (offset x178)
Error: in page 240878, pool page 394590, of Object 26112, in tablespace 11.
3. Once the corrupted pages have been identified by the process given above, use the following command to extract the data from the corrupted table.
To dump formatted table data in delimited ASCII format, you will require four input values: table object ID OR table name, table space ID, starting page number, and number of pages.
db2dart <DBSID> /ddel
When prompted enter the following values:
<table name/ object id> <tablespace id> <starting page number > <number of pages>
To extract all the pages, you can use 0 as the strting page number and a very high arbitrary number for the number of pages. Specifying a high arbitrary number will not cause any issues while extracting data.
Once you have entered the values for the parameters given above, you will then be presented with the column definitions for the table and will be asked to specify an output file name:
If the extraction fails using the above method, try extracting data in phases.
For example: If the table EMP has 100 pages and the page 65 is corrupted, you can use the following steps to extract the data from the table
db2dart sample /ddel
Then when prompted give the following options:
Emp 1 0 64 (emp table name , tablespaceid = 1, Starting page =0, Number of pages= 64)
After this, extract the remaining data using the following command:
db2dart sample /ddel
Then when prompted give the following options:
Emp 1 66 34
When prompted for the output filename, specify a different file name this time, as the default file name will be <DBSID>.DEL
Note: The data in the corrupted page(s) may be lost using this method. You will have to live with this data loss unless you some other way of recovering data (i.e. from a past successful tablespace backup before the corruption occurred)
4. The extracted data from table can now be imported or loaded into the table. The best way to load the data into the table would be:
a) Create a temporary table by using the DDL extracted from the corrupted table (using DB2LOOK).
b) Import/Load the data extracted using the above method into this temporary table.
c) Drop the primary key, Views and indexes on the corrupted table.
d) Rename the corrupted table to ***_OLD extension
e) Rename the temporary table to the original name and then recreate the Views and indexes.
f) Once the data in the new tables has been verified then the corrupted table can be dropped.
| Comments |
|
Powered by !JoomlaComment 3.25
