If you have tables with XML columns that
you created in a pre-Version 9.7 release and want to use certain new
functions, you must convert the XML storage objects to the Version
9.7 format by re-creating the tables.
About this task
The
following new functions require the XML storage object to be in the
Version 9.7 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.
Before you begin
- Ensure that you have authorization to create tables and access
to the existing tables.
Procedure
To convert XML storage objects to the Version 9.7 format:
- Generate a list of all the tables with XML
columns with XML storage object in pre-Version 9.7 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.