The recovery procedure for dropped table/tablespace is quite different from the normal recovery procedure we do in case of a data corruption in a table.
When the table/tablespace is dropped all its corresponding information from catalogue tables is flushed out. So no information can be found in catalogue tables once a drop committed.
All image copies are recorded in SYSIBM.SYSCOPY table. If it is a full image copy the ICTYPE column will have ‘F’, if it is an incremental image copy the column will have a value ‘I’. Once the table/tablespace is dropped it deletes copy records from SYSIBM.SYSCOPY table. Notice that DB2 deletes only copy details from its catalogue table, it doesn’t delete the actual image copy dataset.
As the copy details are removed from SYSIBM.SYSCOPY, though we recreate the table/tablespace we can not restore the data using RECOVER utility. The reason is RECOVER depends on SYSIBM.SYSCOPY table for restoring the data. As there is no image copy entry in the SYSCOPY table it doesn’t do a recovery though image copy dataset really exists.
In such a scenario we need to go for a recovery using DSN1COPY.
DSN1COPY is a stand alone utility to copy VSAM datasets. We can utilize this capability of DSN1COPY to restore the dropped table/tablespace bypassing DB2.
To do the recovery a latest full image copy dataset should be available that is taken with SHRLEVEL REFERENCE. Then follow the below procedure.
1) Create the tablespace/table that is dropped. When you create an object in DB2 it is assigned new unique identifier across DB2 subsystem. Record the PSID of tablespace newly created and record the OBID of a table newly created.
To get these you can use below queries.
SELECT NAME, DBNAME, DBID, PSID FROM SYSIBM.SYSTABLESPACE
WHERE NAME=’tablespace_name’;
SELECT NAME, OBID FROM SYSIBM.SYSTABLES
WHERE NAME=’table_name’ AND CREATOR=’owner_name’;
If the tablespace/table got dropped has LOB column then the auxiliary table will also get dropped automatically. In this case an image copy of LOB tablespace should also be available. This image copy should have taken at the same point along with base tablespace, if not the recovery will not be successful.
2) Stop the tablespace.
To stop the tablespace issue below command
-STOP DB(dbname) SPACENAM(tsname)
If the tablespace/table you are recovering has LOBs in it, then the auxiliary tablespace also must be stopped.
3) Run DSN1PRNT job to print the image copy dataset. DSN1PRNT utility reads image copy and prints the pages in hexadecimal format.
DSN1PRNT outputs different pages in the tablespace.
Header page
Space map page
Dictionary page
Data page
Header page contains DBID, PSID values in hexadecimal format.
Data pages contain OBID values in hexadecimal format.
Find the field HPGOBID in the header page section of the DSN1PRNT output. This field contains the DBID and PSID for the table space.
Field HPGOBID is 4 bytes long and contains the DBID in the first 2 bytes and the PSID in the last 2 bytes.
Find the PGSOBD fields in the data page sections of the DSN1PRNT output. These fields contain the OBIDs for the tables in the table space. If you have multiple tables in the tablespace you need to search for all different values of PGSOBD fields to get all OBIDs.
If you print a LOB tablespace using DSN1PRNT look out for HPGROID field in the header page. This is the OBID of the large object (LOB).
Record all new PSID , OBIDs.
4) Run a DSN1COPY job to copy the image copy data into tablespace vsam dataset directly.
When you are copying the data you need to reset the old PSID of tablespace to new PSID and old OBIDs of tables to new OBIDs
Run DSN1COPY job with PARM=’FULLCOPY,OBIDXLAT,RESET’ option. Providing OBID conversion information in SYSXLAT DD card as shown below
SYSXLAT DD *
400,400 (old DBID, new DBID)
14,15 (old PSID, new PSID)
15,16 (old OBID, new OBID)
If you have a LOB also, run the DSN1COPY job again with LOB Image copy.
Use the PARM=’LOB,FULLCOPY,OBIDXLAT,RESET’
Both jobs should complete with return code 0.
5) Start the tablespace for read/write access.
Auxiliary tablespace also need to be started for LOBs.
To start the tablespace issue below command
-START DB(dbname) SPACENAM(tsname) ACCESS(RW)
6)Create & build all indexes on the base table and LOB table.
7) Run CHECK DATA on base tablespace if the table is participating in any referential relationship or if it contains LOB columns. This job should complete with return code 0, other than 0 and if it returns any violations correct them.
8) Run a SELECT query on the table, it will return the data. It means table is recovered.
No comments:
Post a Comment