March 17, 2009

Finding Implicit XML Table and Table Space

DBAs must be concerned with the name of the table space in which a table is stored. This is because the DB2 utilities operate at the database table space level.

So if you need to collect statistics for a table to help the optimizer choose a better access path, execute the runstats utility on the table space in which that table is stored. Likewise, to backup a table, run the image-copy utility against the appropriate table space. Or, to recover a table, run the recovery utility. This is why DBAs usually restrict table creation in a default database to an implicitly created table space. However, with the introduction of XML data type, DBAs no longer can control the table space name that's implicitly created to store the XML column.

When you issue the CREATE TABLE statement and include a column with an XML data type, DB2 implicitly creates a new table and table space in which to store the XML document. In this example I create a table space (CSBPTS02) in database CSBPDB. Then I create the PRODUCT table to be stored in CSBPDB.CSBPTS02:

  USING STOGROUP CSBPSG                
    PRIQTY            -1                
    SECQTY           -1                
  PCTFREE          20                  
  SEGSIZE           32                  
  BUFFERPOOL     BP0                   
  LOCKSIZE       PAGE ;                 

CREATE TABLE CSBP.PRODUCT                               
  ( PID                          VARCHAR(10)   NOT NULL PRIMARY KEY 
   ,NAME                     VARCHAR(128)                       
   ,PRICE                     DECIMAL(30, 2)                     
   ,PROMOPRICE       DECIMAL(30, 2)                     
   ,PROMOSTART       DATE                               
   ,PROMOEND           DATE                               
   ,DESCRIPTION       XML )                              
  IN CSBPDB.CSBPTS02                                    

Because the PRODUCT table has an XML column (DESCRIPTION), DB2 implicitly creates a new table and accompanying new table space in which to store the XML data. Of course, DBAs must make sure to simultaneously backup the base table and XML table space. But how do you determine which XML tables are related to a given base table? There are two ways to find this information.

One way is to run the following SQL against the DB2 catalog. This retrieves a list of tables in a given database, along with any XML tables and table spaces. For this example I want to retrieve everything in database CSBPDB.

  SELECT SUBSTR(                                                               
         STRIP(T.CREATOR) CONCAT '.'                                           
         STRIP(T.NAME),1,25) AS TBNAME                                         
--     , SUBSTR(T.DBNAME,1,8)  AS DBNAME                                       
       , SUBSTR(T.TSNAME,1,8)  AS TSNAME                                       
       , SUBSTR(XR.COLNAME,1,18) AS COLNAME                                    
       , SUBSTR(XR.XMLTBOWNER,1,8) AS XMLTBOWNER                               
       , SUBSTR(XR.XMLTBNAME,1,18) AS XMLTBNAME                                
       , SUBSTR(VALUE(XT.TSNAME,' '),1,8) AS XMLTSNAME                         
  FROM (((SELECT A.CREATOR, A.NAME, A.DBNAME, A.TSNAME                         
         FROM SYSIBM.SYSTABLES A                                               
         WHERE A.DBNAME = 'CSBPDB'                                             
           AND A.TYPE = 'T'                                                    
         ) AS T                                                                
  LEFT OUTER JOIN                                                              
       SYSIBM.SYSXMLRELS XR                                                    
  ON T.CREATOR = XR.TBOWNER                                                    
    AND T.NAME = XR.TBNAME )                                                   
  LEFT OUTER JOIN                                                              
       SYSIBM.SYSTABLES  XT                                                    
    ON  XR.XMLTBOWNER = XT.CREATOR                                             
    AND XR.XMLTBNAME  = XT.NAME)                                               
  ORDER BY T.DBNAME, T.TSNAME                                                  

TBNAME                           TSNAME       COLNAME  
CSBP.EMP                         CSBPTS01     --------------------

                                         XMLTBOWNER     XMLTBNAME           XMLTSNAME
                                         CSBP                         XMYCUSTOMER      XMYC0000 
                                         ------------------------   -----------------------       -------------------           
                                         CSBP                         XPRODUCT                XPRO0000  

The other option is to run the REPORT utility using the TABLESPACESET option. This retrieves all objects related to a given database and table space. In this example I'm looking for all objects related to the table stored in CSBPDB.CSBPTS02:

The following JCL
//          UID='REPORTTS',UTPROC='',            
//          SYSTEM='DB9G',                       
//          COND=(0,NE)                          
//DSNUPROC.SYSIN DD *                            
//SYSTSPRT DD SYSOUT=X                           
//SYSPRINT DD SYSOUT=X                           

The output below shows that table CSBP.PRODUCT is stored in CSBPDB.CSBPTS02. I also have two indexes, CSBP.I_DOCIDPRODUCT and CSBP.PROD_NAME_PIDX:

TABLESPACE SET REPORT:                                                 

TABLESPACE         : CSBPDB.CSBPTS02                                   
               DSN : DB2DB9G.DSNDBC.CSBPDB.CSBPTS02.I0001.A001         
  TABLE            : CSBP.PRODUCT                                      
  INDEXSPACE       : CSBPDB.IRDOCIDP                                   
               DSN : DB2DB9G.DSNDBC.CSBPDB.IRDOCIDP.I0001.A001         
  INDEX            : CSBP.I_DOCIDPRODUCT                               

  INDEXSPACE       : CSBPDB.PRODRNAM                                   
               DSN : DB2DB9G.DSNDBC.CSBPDB.PRODRNAM.I0001.A001         
  INDEX            : CSBP.PROD_NAME_PIDX                               

Within the CSBP.PRODUCT table, I have an XML column DESCRIPTION. The DESCRIPTION column is stored in CSBP.XPRODUCT, in table space CSBPDB.XPRO0000.

XML TABLESPACE SET REPORT:                                             

TABLESPACE                 : CSBPDB.CSBPTS02                           

  BASE TABLE               : CSBP.PRODUCT                              
  COLUMN                   : DESCRIPTION                               
    XML TABLESPACE         : CSBPDB.XPRO0000                           
      PART: 0001       DSN : DB2DB9G.DSNDBC.CSBPDB.XPRO0000.I0001.A001 
      XML TABLE            : CSBP.XPRODUCT                             
      XML NODEID INDEXSPACE: CSBPDB.IRNO1ARQ                           
                       DSN : DB2DB9G.DSNDBC.CSBPDB.IRNO1ARQ.I0001.A001 
      XML NODEID INDEX     : CSBP.I_NODEIDXPRODUCT                     
      XML INDEXSPACE       : CSBPDB.PROD18FW                           
                       DSN : DB2DB9G.DSNDBC.CSBPDB.PROD18FW.I0001.A001 
      XML INDEX            : CSBP.PROD_NAME_XMLIDX                     

      XML INDEXSPACE       : CSBPDB.PRODRDET                           
                       DSN : DB2DB9G.DSNDBC.CSBPDB.PRODRDET.I0001.A001 
      XML INDEX            : CSBP.PROD_DETAIL_XMLIDX      

It took me awhile to find this information. Hopefully sharing it here will give you a jump start on managing tables containing XML data.