IBM Books

XML Extender Administration and Programming

Enabling XML columns

To store an XML document in a DB2 database, you must enable a column for XML. Enabling a column prepares it for indexing so that it can be searched quickly. You can enable a column by using the XML Extender administration wizard or using the DB2 command shell. The column must be of XML type.

When the XML Extender enables an XML column, it:

After you enable the XML column, you can

Before you begin

Create an XML table by creating or altering a DB2 table with a column of XML UDT.

Using the administration wizard

Use the following steps to enable XML columns:

  1. Set up and start the administration wizard. See Starting the administration wizard for details.
  2. Click Work with XML Columns from the LaunchPad window to view the XML Extender column related tasks. The Select a Task window opens.
  3. Click Enable a Column and then Next to enable an existing table column in the database.
  4. Select the table that contains the XML column from the Table name field. For example
    SALES_TAB
    
  5. Select the column being enabled from the Column name field. For example:
    ORDER
    
    The column must exist and be of XML type.
  6. Type the DAD path and file name into the DAD file name field, or click ... to browse for an existing DAD file. For example:
    c:\dxx\samples\dad\getstart.dad
    
  7. Optionally, type the name of an existing table space in the Table space field.

    The table space contains side tables that the XML Extender created. If you specify a table space, the side tables are created in the specified table space. If you do not specify a table space, the side tables are created in the default table space.

  8. Optionally, type the name of the default view in the Default view field.

    When specified, the default view is automatically created when the column is enabled and joins the XML table and all of the related side tables.

  9. Optionally, type the column name of the primary key in the application table in the Root ID field. This is recommended.

    The XML Extender uses the value of ROOT ID as a unique identifier to associate all side tables with the application table. If not specified, the XML Extender adds the DXXROOT_ID column to the application table and generates an identifier.

  10. Click Finish to enable the XML column, create the side tables, and return to the LaunchPad window.

From the DB2 command shell

To enable an XML column, enter the following command:

Syntax:
dxxadm enable_column
>>-dxxadm---enable_column---dbName---tbName---colName----------->
 
>----DAD_file----+-----------------+---+-------------------+---->
                 '--t--tablespace--'   '--v--default_view--'
 
>-----+--------------+-----------------------------------------><
      '--r--root_id--'
 

Parameters:

dbName
The name of the database.

tbName
The name of the table that contains the column that is to be enabled.

colName
The name of the XML column that is being enabled.

DAD_file
The name of the file that contains the document access definition (DAD).

tablespace
A previously created table space that contains side tables that the XML Extender created. If not specified, the default table space is used.

default_view
Optional. The name of the default view that the XML Extender created to join an application table and all of the related side tables.

root_id
Optional. The column name of the primary key in the application table and a unique identifier that associates all side tables with the application table. The XML Extender uses the value of root_id as a unique identifier to associate all side tables with the application table. Specifying the ROOT ID is recommended. If the ROOT ID is not specified, the XML Extender adds the DXXROOT_ID column to the application table and generates an identifier.

Restriction: If the application table has a column name of DXXROOT_ID, but this column does not contain the value for root_id, you must specify the root_id parameter; otherwise, an error occurs.

Example: The following example enables a column using the DB2 command shell. The DAD file and XML document can be found in Appendix B, Samples.

dxxadm enable_column SALES_DB sales_tab order getstart.dad
            -v sales_order_view -r invoice_num
 
 

In this example, the column ORDER is enabled in the table SALES_DB.SALES_TAB. The DAD file is getstart.dad, the default view is sales_order_view, and the ROOT ID is INVOICE_NUM.

Using this example, the SALES_TAB table has the following schema:
Column name INVOICE_NUM SALES_PERSON ORDER
Data type CHAR(6) VARCHAR(20) XMLVARCHAR

The following side tables are created based on the DAD specification:

ORDER_SIDE_TAB:
Column name ORDER_KEY CUSTOMER INVOICE_NUM
Data type INTEGER VARCHAR(50) CHAR(6)
Path expression /Order/@key /Order/Customer/Name N/A

PART_SIDE_TAB:
Column name PART_KEY PRICE INVOICE_NUM
Data type INTEGER DOUBLE CHAR(6)
Path expression /Order/Part/@key /Order/Part/ExtendedPrice N/A

SHIP_SIDE_TAB:
Column name DATE INVOICE_NUM
Data type DATE CHAR(6)
Path expression /Order/Part/Shipment/ShipDate N/A

All the side tables have the column INVOICE_NUM of the same type, because the ROOT ID is specified by the primary key INVOICE_NUM in the application table. After the column is enabled, the value of the INVOICE_NUM is inserted into the side tables. Specifying the default_view parameter when enabling the XML column, ORDER, creates a default view, sales_order_view. The view joins the above tables using the following statement:

CREATE VIEW sales_order_view(invoice_num, sales_person, order,
                             order_key, customer, part_key, price, date) 
     AS
     SELECT sales_tab.invoice_num, sales_tab.sales_person, sales_tab.order,
            order_tab.order_key, order_tab.customer, 
            part_tab.part_key, part_tab.price, 
            ship_tab.date
     FROM sales_tab, order_tab, part_tab, ship_tab
     WHERE sales_tab.invoice_num = order_tab.invoice_num 
                AND sales_tab.invoice_num = part_tab.invoice_num
                AND sales_tab.invoice_num = ship_tab.invoice_num
If the table space is specified in the enable_column command, the side tables are created in the specified table space. If the table space is not specified, the side tables are created in the default table space.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]