If you have tables with XML columns that you created in DB2® Version 9.5 release
and want to use certain new functions, you must convert the XML storage
objects to the DB2 Version 10.1 format by re-creating the tables.
Before you begin
- Ensure that you have authorization to create tables and access
to the existing tables.
About this task
The following functions require the XML storage object
to be in the
DB2 Version 10.1 format:
- Row compression on tables with XML columns
- Collection of statistics to estimate the inline length for XML
columns
- Upgrade from a single-partition database environment to a multi-partition
database environment
- Redistributing data using theREDISTRIBUTE DATABASE PARTITION
GROUP command.
Procedure
To convert XML storage objects to the DB2 Version 10.1 format:
- Generate a list of all the tables with XML columns with
XML storage object in pre-DB2 Version 10.1 format by issuing the following query:
SELECT
TABSCHEMA, TABNAME FROM SYSIBMADM.ADMINTABINFO WHERE XML_RECORD_TYPE=1
GROUP BY (TABSCHEMA, TABNAME)
Use the GROUP BY clause
for partitioned database environments.
- Re-create the tables in the list that generated in the
previous step by using one of the following methods:
Re-create tables using the ADMIN_MOVE_TABLE
procedure. This method is best suited for a small
number of tables and re-creates tables while the data remains online.
- Perform an offline procedure to re-create the tables and objects
dependent on those tables. This method is best suited for a large
number of tables.
For example, you can have a procedure that uses
the db2move command with the -co COPY action.
See Examples of schema copy using the db2move
utility.
Another
example of an offline procedure involves creating a copy of the table
definition using the db2look command and then performing
a load from cursor.