Blog
DB2utor

Categories

March 24, 2009

Recovering XML Table Space

I firmly believe that DBAs should always be prepared for the worst-case scenario. With this in mind, an imagecopy backup of data is your best friend. However, if you haven't worked with tables containing XML data, you may not know that both the table space (which contains the base table data) and the auxiliary table space (which contains the XML column) must be backed up. In other words, an auxiliary table space is needed for each XML column in the table.

XML's unique characteristics complicate the task of recovering tables when the table space has been dropped. Once you drop a table space, all recovery information about that table space is no longer available to the recovery utility, even if you re-create the table space. The only way to recover the table space is to rebuild it using the original DDL; then use the DSN1COPY utility to restore the imagecopy into the new table space. For more about DSN1COPY, see the "DB2 Version 9.1 for z/OS Utility Guide and Reference."

Recovering tables with XML data requires preparation. First, you must maintain a list of all the table space, table and index information with internal object identifiers. DSN1COPY needs this information if the newly built or target VSAM files have different object identifiers. Because all auxiliary table spaces must be backed up and restored to the same point in time as the base table spaces, you also need a report listing this information. I wrote a DB2utor blog entry that explains how to get a list of base tables along with associated XML tables and table space names.

REPORTS
Next, you must generate a report showing all table space and index space internal DBID, PSID, OBID and IX_OBID. This query provides the information needed for DSN1COPY:

SELECT SUBSTR(TS.NAME,1,8) AS TSNAME      
     , TS.DBID                            
     , TS.PSID                            
     , TS.PGSIZE                          
     , SUBSTR(T.CREATOR,1,8) AS CREATOR   
     , T.OBID                             
     , SUBSTR(T.NAME,1,18) AS TBNAME      
     , SUBSTR(I.INDEXSPACE,1,8) AS IXSPACE
     , I.DBID AS IX_DBID                  
     , I.ISOBID AS IX_ISOBID              
     , I.OBID AS IX_OBID                  
     , SUBSTR(I.NAME,1,18) AS IXNAME      
     , I.PGSIZE                           
FROM SYSIBM.SYSTABLESPACE TS              
   , SYSIBM.SYSTABLES T                   
   , SYSIBM.SYSINDEXES I                  
WHERE TS.DBNAME = 'CSBPDB'                
  AND TS.NAME = T.TSNAME                  
  AND T.DBNAME = TS.DBNAME                
  AND T.DBNAME = 'CSBPDB'                 
  AND T.CREATOR = 'CSBP'                  
  AND T.NAME IN ('PRODUCT','XPRODUCT')    
  AND T.TYPE IN ('T','P')                 
  AND I.DBNAME = T.DBNAME                 
  AND I.TBCREATOR = T.CREATOR             
  AND I.TBNAME = T.NAME                   
ORDER BY T.DBNAME                         
       , T.TSNAME                         
       , T.CREATOR                        
       , T.NAME                           
WITH UR ;   

In this example I qualified the result to database CSBPDB and tables PRODUCT and XPRODUCT. For your production system, generate this report for the entire subsystem and keep it in a safe place.

TSNAME      DBID    PSID      PGSIZE      CREATOR     OBID    TBNAME 
---------------+---------+---------+-------------+----------------+---------+-------------
CSBPTS02       272          61                4       CSBP                    62    PRODUCT
XPRO0000       272         64               16       CSBP                    65    XPRODUCT

                                             IX          IX            IX
TBNAME       IXSPACE       DBID  ISOBID   OBID    IXNAME                        PGSIZE
-----------------+---------------+--------+-----------+-------+----------------------------

--+---------
PRODUCT     PROD1GEQ       272            74         73   PROD_NAME_XMLIDX         4 
PRODUCT     IRDOCIDP         272            70         69   I_DOCIDPRODUCT                 4
PRODUCT     PRODRNAM     272            72         71   PROD_NAME_PIDX                4
PRODUCT     PRODRDET       272            76         75   PROD_DETAIL_XMLIDX      4
XPRODUCT  IRNO11TN         272            68         67   I_NODEIDXPRODUCT           4

BACKUP
I usually just back up the table space, but now that you can backup indexes, I figured I would test the DSN1COPY restore for all table and index spaces related to the PRODUCT table. Before you can use the imagecopy utility on an index, COPY must be enabled on it. Use the ALTER INDEX DDL statement to do this. For example:

ALTER INDEX CSBP.I_DOCIDPRODUCT      
   COPY YES ;

DSN1COPY
Because I want to simulate what happens when a table space is dropped, I went ahead and executed DDL to drop and re-create the table space containing the PRODUCT table and all indexes. Once the table and index space are rebuilt, they must be stopped before you run the DSN1COPY utility. Use the DB2I panels option 7 DB2 commands to do this. For example:

–STOP DB(CSBPDB) SPACE(CSBPTS02)

Now run DSN1COPY to restore the table and index space with the imagecopies taken earlier.

Here's a sample to restore table space CSBPDB.CSBPTS02. Note that:

  • OBIDXLAT isn't really needed in this case because the object IDs didn't change.
  • FULLCOPY should be used when using a full imagecopy as input.
  • PAGESIZE should be taken from the report above. XPRO0000 is 16K.


//DSN1COPY EXEC PGM=DSN1COPY,                                     
//           PARM='OBIDXLAT,RESET,CHECK,FULLCOPY,PAGESIZE(4K)'    
//STEPLIB  DD DISP=SHR,DSN=DSN910.SDSNEXIT                        
//                  DD DISP=SHR,DSN=DSN910.SDSNLOAD                        
//SYSPRINT DD SYSOUT=*                                            
//SYSUT1 DD DISP=OLD,DSN=CSBP.IC.CSBPDB.CSBPTS02.D031109.T160400  
//SYSUT2 DD DISP=OLD,
//                DSN=DB2DB9G.DSNDBC.CSBPDB.CSBPTS02.I0001.A001
//*  SOURCE DBID, TARGET DBID                                     
//*  SOURCE/TARGET PSID OR ISOBID FOR INDEX                       
//*  ALL OTHER SOURCE/TARGET TABLE OBID                           
//SYSXLAT DD *                                                    
272,272                                                           
61,61                                                             
62,62        
/*
//

Once you've completed the restore of all the table and index spaces, restart the objects:

–START DB(CSBPDB) SPACE(*).

Remember: Prepare for the worst-case scenario. Practice now before you really need to use this procedure in an emergency situation.