Overview

The accidental deletion of tables or table spaces is one of the most painful experiences in the work of a database administrator. Although most larger IT shops running mission-critical database applications have procedures or tools to deal with such situations, there are still installations without special tools support, or, even worse, there is only little experience or practice with these tools.

This article describes a specific situation in a DB2 for z/OS environment, where the customer is happy using the pureXML features of DB2 9 for z/OS and storing XML documents within XML tables. To be safe, regular image copies of the database objects are taken in the production environment. Unfortunately, one of these XML tables was dropped by a DB2 SYSADM in the production environment instead of the table with identical name in the test environment.

No additional tools such as IBM's DB2 Log Analysis Tool for z/OS or DB2 Object Restore for z/OS were in place so it was necessary to restore the dropped object manually. Under normal circumstances, this is nothing new for an experienced DBA, because DB2 for z/OS ships with the DSN1COPY utility program. But in this special case, the best practices for using DSN1COPY could not be applied as expected. There are some things to consider if an XML table must be restored with DSN1COPY.

The following sections describe the potential challenges in restoring XML tables manually and how to deal with these challenges. You probably will not need this procedure if your installation has recovery tools available. But even if you have recovery tools, you may find it useful to know what makes this kind of recovery so special.

First, the following is a short introduction of the DSN1COPY utility program and the characteristics of XML tables and related table spaces.

Prerequisites

To benefit from this article, you should have some experience with DSN1COPY and should have a basic understanding of the different types of table spaces that DB2 for z/OS provides. To exercise such a recovery scenario in your own DB2 environment, Version 9 or 10 of DB2 for z/OS is required. You may also need some SQL scripts or programs to create your own XML data. See the Resources section at the end of this article for further information.

Back to top

The DSN1COPY Utility program

Experienced DB2 for z/OS DBAs should be quite familiar with the DSN1COPY utility program. It is shipped as an offline utility with DB2 and allows you to copy VSAM data sets. By using DSN1COPY, it is possible to clone DB2 table spaces and indexes, and to replace the VSAM data sets of a DB2 object with data sets of an image copy.

DSN1COPY does not check whether the structure of the data in the VSAM data sets is consistent with the definition of the DB2 object in the catalog. So you have to be careful when using DSN1COPY for restoring DB2 objects. You also have to consider the physical characteristics of the table space when coding the JCL for DSN1COPY.

DSN1COPY is suitable as a tool for restoring dropped objects because standard DB2 recovery procedures do not apply in this case. Once an object is dropped, all the information about the image copies is removed from the DB2 catalog and directory. So even the fact that image copies still exist does not help for standard recovery. The DB2 objects must be recreated with SQL DDL first and then the underlying VSAM data sets are replaced with the image copy.

As you can see, this procedure is similar to a point-in-time recovery of the table space back to this image copy. This may be acceptable in some cases. But if it is required to have the data as current as possible, you definitely need additional tools, such as IBM Object Restore for DB2. These tools are capable of reading the DB2 log files and generate SQL redo statements to bring the data in your DB2 table to the current state.

In the following sections, it is assumed that it is sufficient to restore the accidentally dropped XML table by DSN1COPY with the most recent image copy.

Back to top

Characteristics of an XML table space

The storage structure for XML is similar to LOB data. There is at least one additional table space (the XML table space) which stores the actual XML documents (exactly one table space per XML column). Depending on the characteristics of the table space containing the base table, DB2 creates the following kinds of the table spaces:

• Universal table spaces (UTS), partitioned by growth (PBG) if the base table is placed in a simple or segmented table space or in a partitioned by growth UTS
• UTS, partitioned by range (PBR) if the base table is placed in a classic partitioned table space or a partitioned by range UTS

In any case, DB2 creates the table space automatically. It is always a UTS and its page size is always 16K. Similar to LOBs, only few alterations of its characteristics are possible, once it is created by DB2.

The base table contains an additional column to identify each XML document. It is a BIGINT identity column (GENERATED ALWAYS) named DB2_GENERATED_DOCID_FOR_XML.

In our recovery scenario, a very simple table containing one XML column is restored. It is defined as shown in Listing 1.

CREATE DATABASE XMLTSTDB CCSID UNICODE STOGROUP 
  SYSDEFLT BUFFERPOOL BP1 INDEXBP BP2;

CREATE TABLESPACE TS1 IN XMLTSTDB SEGSIZE 64 ;

CREATE TABLE XMLTST.TAB1 
(ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ,
 MESSAGE_ID VARCHAR(80) ,
 XML_FILE XML )
 IN XMLTSTDB.TS1 ;
 
CREATE UNIQUE INDEX XMLTST.IX1 ON XMLTST.TAB1 (ID ASC);

DB2 creates the additional XML table space XTAB0000 as partitioned by growth UTS because the base table space is not partitioned.

The following information in Listing 2 is from the DB2 Administration Tool that shows a P in column T (Type):

 DB2 Admin ------------------ DBT2 Table Spaces --------------- Row 1 to 2 of 2 
 Command ===>                                                  Scroll ===> CSR  
                                                                                
 Commands: GRANT  MIG  DIS  STA  STO  ALL                                       
 Line commands:                                                                 
  T - Tables  D - Database  A - Auth  G - Storage group  ICS - Image copy status
  DIS - Display table space  STA - Start table space  STO - Stop table space    
  ? - Show all line commands                                                    
                                                                                
 Select Name     DB Name   Parts Bpool  L E S I C Tables  Act. pages  Segsz T L 
        *        *             * *      * * * * *      *           *      * * * 
 ------ -------- -------- ------ ------ - - - - - ------ ----------- ------ - - 
        TS1      XMLTSTDB      0 BP1    A N A N Y      1          -1     64   Y 
        XTAB0000 XMLTSTDB      1 BP16K0 X N A Y Y      1          -1      4 P Y 
 ******************************* END OF DB2 DATA *******************************

If you do not have DB2 Administration Tool available, you will get the same results with a simple DB2 catalog query, as shown in Listing 3.

SELECT NAME, TYPE, BPOOL, SEGSIZE,                                              
PARTITIONS AS PART, MAXPARTITIONS AS MAXPART, LOCKRULE                          
FROM SYSIBM.SYSTABLESPACE WHERE DBNAME='XMLTSTDB'                               
---------+---------+---------+---------+---------+---------+---------+------
NAME                      TYPE  BPOOL     SEGSIZE    PART  MAXPART  LOCKRULE    
---------+---------+---------+---------+---------+---------+---------+------
TS1                             BP1            64       0        0  A           
XTAB0000                  P     BP16K0          4       1      256  X           
DSNE610I NUMBER OF ROWS DISPLAYED IS 2                                          
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                     

You can also see that DB2 provided a value of 256 for MAXPARTITIONS.

XTAB0000 contains the implicitly created table which stores the actual XML data, XTAB1 in our case. SYSIBM.SYSTABLES shows a P in column TYPE to indicate an XML table, as shown in Listing 4.

SELECT TSNAME, TYPE, NAME                                                       
FROM SYSIBM.SYSTABLES WHERE DBNAME='XMLTSTDB'                                   
---------+---------+---------+---------+---------+---------+---------
TSNAME                    TYPE  NAME                                            
---------+---------+---------+---------+---------+---------+---------
TS1                       T     TAB1                                            
XTAB0000                  P     XTAB1                                           
DSNE610I NUMBER OF ROWS DISPLAYED IS 2                                          
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                     

In Listing 5, SYSIBM.SYSINDEXES shows that there are two indexes created implicitly.

SELECT SUBSTR(NAME, 1, 20) AS IXNAME,                                           
SUBSTR(CREATOR, 1, 20) AS CREATOR,                                              
SUBSTR(TBNAME, 1, 20) AS TBNAME, UNIQUERULE                                     
FROM SYSIBM.SYSINDEXES WHERE DBNAME='XMLTSTDB'                                  
---------+---------+---------+---------+---------+---------+---------
IXNAME                CREATOR               TBNAME                UNIQUERULE    
---------+---------+---------+---------+---------+---------+---------
I_DOCIDTAB1           XMLTST                TAB1                  X             
IX1                   XMLTST                TAB1                  U             
I_NODEIDXTAB1         XMLTST                XTAB1                 N             
DSNE610I NUMBER OF ROWS DISPLAYED IS 3                                          

Index IX1 was created on table TAB1 to enforce uniqueness of column ID. Whereas I_NODEIDXTAB1 is used for XML table XTAB1, I_DOCIDTAB1 is defined on the additional column DB2_GENERATED_DOCID_FOR_XML. See this column in the DB2 catalog query shown in Listing 6. It is used internally but, from the recovery perspective, may become important as you will see in a later example.

SELECT COLNO, COLTYPE, NAME FROM SYSIBM.SYSCOLUMNS                              
WHERE TBNAME='TAB1' AND TBCREATOR='XMLTST'                                      
ORDER BY COLNO                                                                  
---------+---------+---------+---------+---------+---------+---------
 COLNO  COLTYPE   NAME                                                          
---------+---------+---------+---------+---------+---------+---------
     1  INTEGER   ID                                                            
     2  VARCHAR   MESSAGE_ID                                                    
     3  XML       XML_FILE                                                      
     4  BIGINT    DB2_GENERATED_DOCID_FOR_XML                                   
DSNE610I NUMBER OF ROWS DISPLAYED IS 4

Be aware that this storage structure requires the XML table space to be included in your backup strategy. There is no implicit image copy of XML table spaces if you run the copy utility on the base table space. You should also ensure a common point of consistency for the base table space and the XML table space if a point in time recovery should be required.

As shown in Listing 7, after the objects have been created, an SQL script or program is used to populate the table with XML documents. Image copies with a subsequent QUIESCE are taken to ensure a common point of consistency:

//DSNUPROC.SYSIN DD *                                             
  LISTDEF LIST1                                                   
  INCLUDE TABLESPACES TABLESPACE XMLTSTDB.TS1                     
  INCLUDE TABLESPACES TABLESPACE XMLTSTDB.XTAB0000                
                                                                  
  TEMPLATE TEMPLC UNIT VTS01 DSN &SS..&IC.IC.&DB..&TS..D&DT..T&TI.
  DISP(MOD,CATLG,CATLG) DATACLAS VTS01 RETPD 10  STACK(YES)      
                                                                  
  COPY LIST LIST1 COPYDDN(TEMPLC) SHRLEVEL REFERENCE              
  QUIESCE LIST LIST1

These image copies will be used in the following recovery scenario.

Back to top

Things to consider when restoring an XML table space with DSN1COPY

When the base table, TAB1 in our example, is dropped, the corresponding XML table and its table space are dropped as well. This is different from LOB table spaces. LOB table spaces that were created with CURRENT RULES='DB2' still exist even if the base and its corresponding auxiliary table are dropped.

From a DB2 perspective, table space TS1 still looks recoverable as the table space has not been dropped. However, a standard DB2 recovery would not work for the XML table space as all recovery information in the DB2 catalog and directory is lost. So both objects, base and XML table, need to be recovered using DSN1COPY.

For the base table, this is the usual process. For the XML table, some pitfalls are possible.

The first step is recreating the objects with the SQL script shown previously in Listing 1. To run DSN1COPY properly, it is required to specify the correct object IDs for database, table space (page set) and table(s). DSN1COPY allows to translate object IDs stored in the image copy into object IDs for the newly created objects. The target object IDs for the newly created objects can easily be retrieved from the DB2 catalog, as shown in Listing 8.

SELECT DBID, PSID, NAME FROM SYSIBM.SYSTABLESPACE            
WHERE DBNAME='XMLTSTDB';                                     
---------+---------+---------+---------+---------+---------+-
  DBID    PSID  NAME                                         
---------+---------+---------+---------+---------+---------+-
   393       2  TS1                                          
   393       5  XTAB0000                                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 2                       
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100  
---------+---------+---------+---------+---------+---------+-
SELECT OBID, NAME FROM SYSIBM.SYSTABLES                      
WHERE DBNAME='XMLTSTDB';                                     
---------+---------+---------+---------+---------+---------+-
  OBID  NAME                                                 
---------+---------+---------+---------+---------+---------+-
     3  TAB1                                                 
     6  XTAB1   

For base table space TS1 and its table TAB1, the DB2 catalog indicates DBID 393, PSID 2 and OBID 3. The XML table space has the same DBID but different PSID (5) and OBID(6).

The corresponding object IDs for the dropped tables are no more available in the DB2 catalog. They have to be extracted from the image copy data set itself. We can use the DSN1PRNT utility program for this.

Back to top

Obtaining the correct object IDs

The DB2 Administration Guide (see the Resources section of this article) gives detailed instructions on how to use DSN1PRNT for extracting object IDs from image copies. As shown in Listing 9, for the base table, DSN1PRNT is invoked with the image copy data set as input to SYSUT1:

//PRT   EXEC PGM=DSN1PRNT,                                   
//           PARM='PAGESIZE(4K),FULLCOPY,FORMAT,NODATA'      
//STEPLIB  DD DSN=DB2.DBT2.SDSNLOAD,DISP=SHR                 
//SYSPRINT DD SYSOUT=*                                       
//SYSUT1   DD DSN=DBT2.FIC.XMLTSTDB.TS1.D2011209.T145537,    
//         DISP=SHR  

Be sure to specify the correct PAGESIZE for the base table (4K in our case). As shown in Listing 10, DBID and PSID can be located in the HPGOBID field in the base table's header page.

PAGE: # 00000000 -------------------------------------------------------
HEADER PAGE:  PGCOMB='10'X  PGLOGRBA='032269910A04'X  PGNUM='00000000'X 
              HPGOBID='01890002'X  HPGHPREF='000000B4'X  HPGCATRL='00'X 

In our case, DBID is X'0189' (or 393 in decimal) and PSID is X'0002' (2 in decimal). TAB1s OBID is located in the data pages (field PGSOBD), as shown in Listing 11.

  PGSLTH='0064'X  PGSOBD='0003'X  PGSBID='01'X 
  PGSLTH='0064'X  PGSOBD='0003'X  PGSBID='02'X 
  PGSLTH='0064'X  PGSOBD='0003'X  PGSBID='03'X 
  PGSLTH='0064'X  PGSOBD='0003'X  PGSBID='04'X 

Four records for OBID = X'0003' are in our example.

Note: This example shows a segmented table space. Use field HPGROID in the header page instead of PGSOBD if your base table is stored in a UTS.

For XML table spaces there is a slightly different procedure. You have to specify PAGESIZE(16K) for XML table spaces. There is no other choice. Other parameters are not affected. Take the name of the image copy data set as input data set, as shown in Listing 12.

//PRT   EXEC PGM=DSN1PRNT,                                    
//           PARM='PAGESIZE(16K),FULLCOPY,FORMAT,NODATA'      
//STEPLIB  DD DSN=DB2.DBT2.SDSNLOAD,DISP=SHR                  
//SYSPRINT DD SYSOUT=*                                        
//SYSUT1   DD DSN=DBT2.FIC.XMLTSTDB.XTAB0000.D2011209.T145537,
//         DISP=SHR                                           

Do not specify LOB as a parameter for DSN1PRNT. Although there are similarities between XML and LOB table spaces, this does not work for XML table spaces.

DBID and PSID can be extracted the same way as for the base table (field HPGOBID in the header page). The XML table's OBID is also located in the header page. As shown in Listing 13, you can see in field HPGROID, it contains the OBID in hexadecimal format.

HEADER PAGE:  PGCOMB='10'X  PGLOGRBA='03226993AD31'X  PGNUM='00000000'X  PGFLAGS='38'X
              HPGOBID='01890005'X  HPGHPREF='0000002D'X  HPGCATRL='00'X
              HPGCATV='00'X  HPGTORBA='000000000000'X  HPGTSTMP='201107281651195
              HPGSSNM='DBT2'  HPGFOID='0004'X  HPGPGSZ='4000'X  HPGSGSZ='0004'X 
              HPGZ3PNO='000000'X  HPGZNUMP='00'X  HPGTBLC='0001'X  HPGROID='0006'
              ...

In this example, this is X'0189' (393) for DBID, X'0005' (5) for PSID and X'0006' (6) for OBID. Don't feel concerned if you don't find any hint in the DB2 documentation for XML table spaces in conjunction with DSN1PRNT. The same rules as for LOB table spaces apply in this case.

Now all necessary information should be in place to run the DSN1COPY utility.

The example shown in Table 1 shows that it is not required to let DSN1COPY change the object IDs during the copy process if target and source IDs are identical.

Back to top

The copy process

The actual copy process is performed using DSN1COPY. It has to be run twice; one copy for the base, and the second one for the XML table space. There are no special considerations for the base table space. Make sure you specify the correct PAGESIZE and (if necessary) the correct value for NUMPARTS, and do not forget that the copy is done from an image copy, as shown in Listing 14.

//RE1   EXEC PGM=DSN1COPY,PARM='PAGESIZE(4K),FULLCOPY,OBIDXLAT,RESET'
//STEPLIB  DD DSN=DB2.DBT2.SDSNLOAD,DISP=SHR                         
//SYSPRINT DD SYSOUT=*                                               
//SYSUT1   DD DSN=DBT2.FIC.XMLTSTDB.TS1.D2011209.T145537,            
//         DISP=SHR                                                  
//SYSUT2   DD DSN=DBT2.DSNDBC.XMLTSTDB.TS1.I0001.A001,               
//         DISP=SHR   
//SYSXLAT  DD * 
 393,393        
 2,2            
 3,3 

The XML table space requires some special attention. As shown in Listing 15, for DSN1PRNT, a PAGESIZE(16K) has to be specified for XML. Note that there is no special parameter to indicate that an XML table space should be copied.

			

//RE1   EXEC PGM=DSN1COPY,                                           
//           PARM='PAGESIZE(16K),FULLCOPY,OBIDXLAT,RESET'            
//STEPLIB  DD DSN=DB2.DBT2.SDSNLOAD,DISP=SHR                         
//SYSPRINT DD SYSOUT=*                                               
//SYSUT1   DD DSN=DBT2.FIC.XMLTSTDB.XTAB0000.D2011209.T145537,       
//         DISP=SHR                                                  
//SYSUT2   DD DSN=DBT2.DSNDBC.XMLTSTDB.XTAB0000.I0001.A001,          
//         DISP=OLD                                                  
//SYSXLAT  DD *                                                      
 393,393   
 5,5       
 6,6       

Do not forget the index rebuilds. These are necessary for both table spaces as there is the DOCID index on the base table and the additional NODEID index on the XML table. This example looks quite simple. You may think that no special attention is required for XML table spaces, but the following examples will show that there still exist some issues to consider.

Back to top

How to deal with several partitions of an XML table space

As already mentioned, if the base table is not partitioned, then DB2 creates a partitioned by growth (PBG) table space for the XML table. As soon as one partition becomes full, DB2 adds another partition to the table space. All partitions have to be respected in backup and recovery procedures.

If you store larger XML documents it is very likely that the XML table space will grow to two or more partitions. The base table can remain very small and occupy only a few megabytes of data, whereas the XML table space may span several partitions.

The actual number of partitions is reflected in column PARTITIONS in SYSIBM.SYSTABLESPACE. You can also see additional VSAM data sets in the ISPF data set list utility.

In this test case, 477 rows were inserted into table XMLTST.TAB1. 418 of them had XML documents. These files took about 5 GB of space so DB2 created a second partition for the XML table space. It can be verified with the data set list utility (ISPF option 3.4), as shown in Listing 16.

			
DBT2.DSNDBC.XMLTSTDB.XTAB0000.I0001.A001                       *VSAM* 
DBT2.DSNDBC.XMLTSTDB.XTAB0000.I0001.A002                       *VSAM* 
DBT2.DSNDBD.XMLTSTDB.XTAB0000.I0001.A001                       D29003+
DBT2.DSNDBD.XMLTSTDB.XTAB0000.I0001.A002                       D29125 

A full image copy of the XML table space will create an image copy data set that contains data for partitions 1 and 2.

Now what happens if an accident occurs and the base table space TS1 is dropped? Fortunately, image copies were taken and can be used as input for DSN1COPY as in the last example. After having checked the object IDs, and analyzing the image copy data sets with DSN1PRNT, then DSN1COPY is run for both table spaces.

The copy for the base table space should end with no errors but for XML table space XTAB0000 the copy finishes with RC=08 and the following error message, as shown in Listing 17.

				
DSN1989I DSN1COPY IS PROCESSED WITH THE FOLLOWING OPTIONS:                      
         NO CHECK/NO PRINT/16K/FULLCOPY    /NON-SEGMENT/NUMPARTS=   0/   OBIDXLAT
          DSSIZE=   /PIECESIZ=    /       /                                     
DSN1998I INPUT  DSNAME = DBT2.FIC.XMLTSTDB.XTAB0000.D2011210.T154845 , SEQ      
DSN1997I OUTPUT DSNAME = DBT2.DSNDBC.XMLTSTDB.XTAB0000.I0001.A001    , VSAM     
DSN1992I VSAM PUT ERROR, RPLERREG = 008, RPLERRCD =  028                        
                                                                                
DSN1993I DSN1COPY TERMINATED,  00102196 PAGES PROCESSED

IBM informational APAR II13617 gives a hint what is happening in this case. DSN1COPY could not handle an object with more than 4GB. It is recommended to use extended attribute (EA) data sets instead. II13617 as well as the DB2 Utility Guide gives a detailed description for a work around if EA data sets cannot be allocated.

As shown in Listing 18, it's also a useful hint because it indicates that at least a second partition for the PBG is required. So it is necessary to allocate the data set for the second partition manually.

			

//IDCAMS EXEC PGM=IDCAMS                                          
//SYSPRINT DD SYSOUT=*                                            
//SYSIN    DD *                                                   
 DEFINE CLUSTER -                                                 
   (NAME('DBT2.DSNDBC.XMLTSTDB.XTAB0000.I0001.A002') -            
    LINEAR -                                                      
    REUSE -                                                      
    SHAREOPTIONS(3 3)  -                                          
    RECORDS(1000 1000) -                                          
    VOLUMES(D29125)) -                                            
  DATA -                                                          
   (NAME('DBT2.DSNDBD.XMLTSTDB.XTAB0000.I0001.A002'));   

It is also important to specify NUMPARTS(2) for DSN1COPY to indicate that the image copy data set covers two partitions. You are on the right track if DSN1COPY returns messages as shown in LIsting 19.

			
DSN1989I DSN1COPY IS PROCESSED WITH THE FOLLOWING OPTIONS:                      
         NO CHECK/NO PRINT/16K/FULLCOPY    /NON-SEGMENT/NUMPARTS=0002/   OBIDXLAT
          DSSIZE=   /PIECESIZ=    /       /                                     
DSN1998I INPUT  DSNAME = DBT2.FIC.XMLTSTDB.XTAB0000.D2011210.T154845 , SEQ      
DSN1997I OUTPUT DSNAME = DBT2.DSNDBC.XMLTSTDB.XTAB0000.I0001.A001    , VSAM     
DSN1997I OUTPUT DSNAME = DBT2.DSNDBC.XMLTSTDB.XTAB0000.I0001.A002    , VSAM     
                                                                               
DSN1994I DSN1COPY COMPLETED SUCCESSFULLY,  00369789 PAGES PROCESSED  

If NUMPARTS(2) is not specified, you will confuse DSN1COPY because it does not expect data for more than one partition in the image copy data set. You will get the following output shown in Listing 20.

				
DSN1989I DSN1COPY IS PROCESSED WITH THE FOLLOWING OPTIONS:                      
         NO CHECK/NO PRINT/16K/FULLCOPY    /NON-SEGMENT/NUMPARTS=   0/   OBIDXLAT
          DSSIZE=   /PIECESIZ=    /       /                                     
DSN1998I INPUT  DSNAME = DBT2.FIC.XMLTSTDB.XTAB0000.D2011210.T154845 , SEQ      
DSN1997I OUTPUT DSNAME = DBT2.DSNDBC.XMLTSTDB.XTAB0000.I0001.A001    , VSAM     
DSN1961I - PIECE NUMBER 0002 IS INVALID.                     
                                                                                
DSN1993I DSN1COPY TERMINATED,  00262080 PAGES PROCESSED   

Compare the number of pages of the successful run with the number of pages of the last run. It seems that about 100,000 pages are missing. These are stored in the second partition.

Now you can run REBUILD INDEX for the two table spaces and everything seems to look fine. But there is still a big issue remaining. For DB2, the XML table space is a PBG table space with only one partition. In DB2 Version 9 for z/OS there is no option when creating a PBG table space to force DB2 to create two or more partitions. The fact that a second partition was created manually and filled with DSN1COPY is not sufficient. DB2 only recognizes the rows in the first partition. You can verify this by running the CHECK DATA utility against the base table space. CHECK DATA reports inconsistencies between base and XML table space. See the following report shown in Listing 21 as an example:

				
DSNU050I    210 21:20:24.31 DSNUGUTC -  CHECK DATA table space XMLTSTDB.TS1 SCOPE ALL
LOBERROR REPORT                                                                 
DSNU730I    210 21:20:24.42 DSNUKDST - CHECKING TABLE XMLTST.TAB1               
DSNU3340I   210 21:20:24.44 DSNUGSOR - UTILITY PERFORMS DYNAMIC ALLOCATION OF SORT
DSNU042I    210 21:20:24.57 DSNUGSOR - SORT PHASE STATISTICS -                  
                      NUMBER OF RECORDS=477                                     
                      ELAPSED TIME=00:00:00                                     
DSNU3340I   210 21:20:24.75 DSNUGSOR - UTILITY PERFORMS DYNAMIC ALLOCATION OF SORT
DSNU042I    210 21:20:24.87 DSNUGSOR - SORT PHASE STATISTICS -                  
                      NUMBER OF RECORDS=166                                     
                      ELAPSED TIME=00:00:00                                     
DSNU822I    210 21:20:24.88 DSNUKERK - XML COLUMN XML_FILE IN TABLE XMLTST.TAB1 
                        MISSING IN INDEX XMLTST.I_NODEIDXTAB1.                  
                        DOCID X'008000000000000127'                             
DSNU822I    210 21:20:24.88 DSNUKERK - XML COLUMN XML_FILE IN TABLE XMLTST.TAB1 
                        MISSING IN INDEX XMLTST.I_NODEIDXTAB1.                  
                        DOCID X'008000000000000128'                            
...

So the next challenge would be to find a way to let DB2 spread the XML data over more than one partition. One way to achieve this is to reorganize the XML table space in such a way that DB2 will allocate a second partition for the shadow data sets. Before the REORG, the DBA would significantly increase PCTFREE (here from 5 to 25) or FREEPAGE. In this example, DB2 REORG respected the increased value for PCTFREE and allocated a second partition, as intended.

Please note: DSN1COPY and REBUILD INDEX for the XML table space have to be rerun once more. The previous REORG only regarded the data of the first partition. The data that was stored in the second VSAM cluster was not visible for DB2.

Now DB2 becomes aware of the second partition. CHECK DATA should run successfully and the XML documents should be restored completely.

Important notice: This is an example for an environment running version 9 of DB2 for z/OS. Version 10 allows adding new partitions to PBG table spaces. So it is much easier to create the correct number of partitions before running DSN1COPY.

Back to top

Observe the correct order of columns for the base table

The internal structure of the base table may cause another issue for the recovery process.

Remember: DB2 creates internal DOCID columns with data type BIGINT (DB2_GENERATED_DOCID_FOR_XML) in the base table to store a reference to the corresponding row in the XML table space. This is similar to tables with LOB columns where DB2 creates ROWID columns in the base table (DB2_GENERATED_ROWID_FOR_LOBS).

When the XML or LOB table is created, or the XML or LOB column is added with an ALTER TABLE ADD COLUMN statement, these DOCID columns are always placed as the last column of the table. It is not relevant on which position in your DDL the XML or LOB appears. If you add more columns to you XML or LOB table, the DOCID column will remain at its position. For the next example, two more columns are added to table TAB1.

Remember the DDL1 for TAB1 shown previously in Listing 1. A base table with four columns is created, as shown in Table 2.

Two more columns are added:

				
ALTER TABLE XMLTST.TAB1 ADD CREATED_TS TIMESTAMP ;
ALTER TABLE XMLTST.TAB1 ADD STATUS_CODE CHAR(1) NOT NULL WITH DEFAULT;

The internal structure of the table reflects these changes, as shown in Table 3.

Now the table is dropped and recreated. Maybe, the DBA or the application developer still has the DDL available, reflecting the changes that were applied with ALTER TABLE later, as shown in Listing 22.

CREATE TABLE XMLTST.TAB1 
(ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ,
 MESSAGE_ID VARCHAR(80) ,
 XML_FILE XML,
 CREATED_TS TIMESTAMP,
 STATUS_CODE CHAR(1) NOT NULL DEFAULT  )
 IN XMLTSTDB.TS1 ;

That seems to look good and from the application point of view, the order of columns looks identical to the dropped table. The internal structure indicates what kind of problems is implicated, as shown in Table 4.

The DOCID is now placed at position 6, right after the last visible data column. This has no influence on the DSN1COPY jobs, but running the REBUILD INDEX(ALL) on table space TS1 fails, as shown in Listing 23.

		
DSNI014I  -DBT2 DSNIMOFR DATA IN USE DURING ABEND 552        
           REASON 00C90101                                   
           ERQUAL 53A2                                       
           TYPE 00000302                                     
           NAME XMLTSTDB.TS1     .X'00000002'                
           CONNECTION-ID=UTILITY                             
           CORRELATION-ID=... 
           LUW-ID=... 
DSNI014I  -DBT2 DSNIMOFR DATA IN USE DURING ABEND 553        
           REASON 00C90101                                   
           ERQUAL 53A2                                       
           TYPE 00000302                                     
           NAME XMLTSTDB.TS1     .X'00000001'                
           CONNECTION-ID=UTILITY                             
           CORRELATION-ID=...
           LUW-ID=... 

It is assumed that the DSN1COPYs were coded correctly, especially the correct object id translations were specified. But it is required that the order of columns in the recreated table is identical to the order of columns in the image copy. Be sure to create the new table in the same manner as the old one. Columns that have been added after the original creation of the table have to be added with ALTER TABLE, and must not be specified in the CREATE TABLE statement.

This rule does not only apply to base tables containing XML columns. You will get similar error messages when you do not pay attention to the exact order of columns for other kind of tables. But you cannot explicitly tell DB2 where to put the internal DOCID columns for base tables containing XML columns, and base tables containing LOB columns as well.

Important notice: The scenario assumes that the exact change history of the table is known. But even if the change history is not available, that is, you know the DDL but you are not sure which columns have been added later, you are not lost. You may use a trial and error method (create the table, add columns, run the DSN1COPY and see whether it works), or a more sophisticated method. Make sure that you create regular image copies of your DB2 catalog tables. Just take the last copy of the SYSDBASE table space of DSNDB06, unload it and load it into a copy of DSNDB06. See column CREATEDTS in SYSCOLUMNS and you will know which columns must not be included in the CREATE TABLE statement.

In our example, SYSTABLES and SYSCOLUMNS clearly indicate that columns CREATED_TS and STATUS_CODE have been added in a separate step, as shown in Listing 24.

SELECT TB.CREATEDTS AS TAB_CREATED, COL.CREATEDTS AS COL_CREATED,               
COL.NAME                                                                        
FROM SYSIBM.SYSTABLES TB                                                        
JOIN SYSIBM.SYSCOLUMNS COL                                                      
ON TB.NAME = COL.TBNAME AND TB.CREATOR=COL.TBCREATOR                            
AND TB.CREATOR='XMLTST' AND TB.NAME='TAB1';                                     
---------+---------+---------+---------+---------+---------+---------+---------+
TAB_CREATED                 COL_CREATED                 NAME                    
---------+---------+---------+---------+---------+---------+---------+---------+
2011-08-01-14.37.33.286314  2011-08-01-14.37.33.286314  ID                      
2011-08-01-14.37.33.286314  2011-08-01-14.37.33.286314  MESSAGE_ID              
2011-08-01-14.37.33.286314  2011-08-01-14.37.33.286314  XML_FILE                
2011-08-01-14.37.33.286314  2011-08-01-14.37.33.286314  DB2_GENERATED_DOCID_FOR_XML
2011-08-01-14.37.33.286314  2011-08-01-14.37.43.729698  CREATED_TS              
2011-08-01-14.37.33.286314  2011-08-01-14.37.45.246738  STATUS_CODE             
DSNE610I NUMBER OF ROWS DISPLAYED IS 6                                          
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                     

Back to top

How to deal with SQLCODE -803 after restoring the XML table space

There is still one potential issue after a successful restore of the XML table space. Even if all XML documents are recovered and can be selected with SQL statements, you may get the next and hopefully last problem if you try to insert new XML documents into the table.

In our example, table TS1 has one identity column (ID) which is declared generated by default. You have to consider that the internal counter for this identity column is still 0 after re-creation of the table, unless a start value is explicitly specified. So you have to take care to adjust this counter to a value that is higher than the maximum value in the data.

Assumed that the restored data contains 34 rows, the following DDL should be executed before inserting new rows:

ALTER TABLE XMLTST.TAB1 ALTER COLUMN ID RESTART WITH 35;

This is usual technique when a user defined identity column is involved. It is also applicable if the column was defined with generated always. However, the insertion of a new XML document still fails with an SQLCODE -803, as shown in Listing 25.

---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO XMLTST.TAB1 (MESSAGE_ID, XML_FILE, STATUS_CODE)                     
SELECT MESSAGE_ID, XML_FILE, 'A' FROM MYSCHEMA.TAB1 WHERE ID=40 ;                   
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -803, ERROR:  AN INSERTED OR UPDATED VALUE IS INVALID        
         BECAUSE INDEX IN INDEX SPACE IRDO1IH8 CONSTRAINS COLUMNS OF THE TABLE  
         SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS.          
         RID OF EXISTING ROW IS X'0000000201'.                                  
DSNT418I SQLSTATE   = 23505 SQLSTATE RETURN CODE                                
DSNT415I SQLERRP    = DSNXRUID SQL PROCEDURE DETECTING ERROR                    
DSNT416I SQLERRD    = -150 13172739  0  13817814  -490143744  0 SQL DIAGNOSTIC  
         INFORMATION                                                            
DSNT416I SQLERRD    = X'FFFFFF6A'  X'00C90003'  X'00000000'  X'00D2D7D6'         
         X'E2C90000'  X'00000000' SQL DIAGNOSTIC INFORMATION                    
---------+---------+---------+---------+---------+---------+---------+---------+

A quick look in the DB2 catalog shows that the unique index on column DB2_GENERATED_DOCID_FOR_XML (the DOCID) is violated in this case. Remember that DB2 keeps a unique DOCID in the base table for every XML document. This DOCID is declared as BIGINT GENERATED ALWAYS AS IDENTITY. Its internal counter is 0 after table creation. The first insert tries to create DOCID #1 but fails as it is already assigned to a document. You can verify by simply selecting DB2_GENERATED_DOCID_FOR_XML from the base table.

So the usual DDL is executed to adjust the counter of this identity column, as shown in Listing 26.

ALTER TABLE XMLTST.TAB1 ALTER COLUMN DB2_GENERATED_DOCID_FOR_XML                
RESTART WITH 35;                                                                
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -350, ERROR:  DB2_GENERATED_DOCID_FOR_XML WAS IMPLICITLY OR  
         EXPLICITLY REFERENCED IN A CONTEXT IN WHICH IT CANNOT BE USED          
DSNT418I SQLSTATE   = 42962 SQLSTATE RETURN CODE                                
DSNT415I SQLERRP    = DSNXIALC SQL PROCEDURE DETECTING ERROR                    
DSNT416I SQLERRD    = 133 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION             
DSNT416I SQLERRD    = X'00000085'  X'00000000'  X'00000000'  X'FFFFFFFF'        
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION      

The SQLCODE indicates that this column cannot be changed by the user as its values are maintained by DB2. So it is not an option to solve this issue with ALTER statements. It is necessary to repeat the inserts until the row can be inserted successfully. In this case (34 rows in the table), it has to be done 34 times. Every insert statement, regardless whether successful or not, increments the internal counter for DOCID in the DB2 catalog. So the 35th insert will succeed in this example.

It is not much effort to run these statements for that small amount of data but it may take some time if thousands or millions of XML documents are involved.

Important notice: DB2 10 will provide the possibility to generate the DOCID in random order. This requires the PTF for APAR PM31487 to be applied, once it is available, and the new ZPARM XML_RANDOMIZE_DOCID to be set to YES.

Back to top

Check list for a successful restore

As the last chapter indicates, there are several issues to consider when XML tables are involved in a recovery of a dropped table. If you do not have any tools that facilitate the recovery of dropped objects you have to be even more careful. Ensure that you do the following:

• Take frequent image copies of your tables containing XML columns. Do not forget the corresponding XML table spaces and try to keep both image copies (for the base and the XML table space) consistent.
• Define your objects WITH RESTRICT ON DROP
• Take frequent image copies of your DB2 catalog. It can be very useful in gathering the following information:
  • What were the object IDs of the dropped objects?
  • How many partitions did a PBG XML table space probably have?
  • What was the change history of the table, such as which columns were added after the initial creation of the table?

See the following check list when you are in the situation to restore an XML table from a copy.

• Identify which image copy data sets will be the input for the copy process.
• Reconstruct the DDL of the dropped table and be sure to separate columns that were added after the initial creation of the table.
• Recreate the table space, the base table and indexes. DB2 will create the XML table space, the XML table and DOCID- and NODEID indexes.
• Determine the object IDs of both base and XML table spaces and tables for the dropped and the recreated objects.
• Run DSN1COPY for the base table space and the XML table space (PAGESIZE 16K).
• Rebuild all indexes for base and XML table space.
• If more partitions for the XML table space are required, then do the following:
  • Define partitions manually
  • Rerun DSN1COPY with the correct NUMPARTS option
  • Reorganize the base (and implicitly XML) table space so that DB2 becomes aware of the additional partitions
  • Run DSN1COPY for the XML table space once more
  • Rebuild indexes on the XML table space once more
• Run CHECK DATA on the base table space to ensure both base and XML table are consistent.
• Select MAX(DB2_GENERATED_DOCID_FOR_XML) from the base table and run dummy inserts to increment the internal DOCID counter.
• If necessary, adjust the values for other identity columns.

Important notice: You should not use DSN1COPY to transfer XML objects to other DB2 subsystems (as it is basically possible for non-XML objects).

Back to top

Conclusion

This article shows a situation that hopefully does not happen very often in real DB2 environments. But when such an event happens, it may become very critical. Usually, standard recovery procedures are in place and administrators have sufficient experience to practice the DB2 recover utility and know its implications.

Drop recovery is more critical; it happens quite rarely and sometimes there are no standard procedures to handle it.

This article should create the necessary awareness for administrators for the special kind of XML table spaces. It should encourage DBAs to establish and practice procedures for drop recovery that covers not only XML, but also standard and LOB table spaces. This also applies if you rely on database tools for object recovery. These tools make the life of a DBA more convenient and give you more support in critical situations. Whether you rely on tools or not, this article has provided useful background knowledge.

Resources

Learn

• Explore the following whitepaper on developerWorks to get a comprehensive overview of pureXML on DB2 for z/OS.
  
  
• Read the new IBM Redbooks publication on XML for DB2 z/OS, which covers many aspects of pureXML on the mainframe. It is written for application developers on z/OS but also for DB2 administrators.
  
  
• Get the resources you need in the Information Management area on developerWorks, to advance your skills on a wide variety of IBM Information Management products.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Follow developerWorks on Twitter.
  
  
• Watch developerWorks on-demand demos ranging from product installation and setup demos for beginners, to advanced functionality for experienced developers.
  
  

Get products and technologies

• Check out the IBM Informational APAR II13617 on dealing with EA data sets for DB2.
  
  
• See IBM APAR PM31487 on randomized DOCIDs for XML documents in DB2 for z/OS.
  
  
• Your starting point for the current product documentation for DB2 z/OS is the DB2 Information Center on the web. For recovery of DB2 objects, see the chapter on operation and recovery. You may also search for DSN1COPY and get more information in the utilities guides.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Christoph Theisen is an IT specialist and IBM Certified Database Administrator for DB2 9 on z/OS. He works for IBM Software Group Services in Germany and has two decades of experience in application development projects both internally in IBM and for IBM customers. His main focus is on data modeling and implementation of data models, especially for DB2 for z/OS. In addition to application development, Christoph has also worked on DB2 for z/OS system-related topics in recent years.

Rate this article

Comments

Back to top

Table of contents

• Overview
• The DSN1COPY Utility program
• Characteristics of an XML table space
• Things to consider when restoring an XML table space with DSN1COPY
• Obtaining the correct object IDs
• The copy process
• How to deal with several partitions of an XML table space
• Observe the correct order of columns for the base table
• How to deal with SQLCODE -803 after restoring the XML table space
• Check list for a successful restore
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers