Converts one or all row-organized user
tables in a specified database into column-organized tables.
The row-organized tables
remain online during command processing. For monitoring purposes,
the command displays statistics about the conversion.
Authorization
You must have SQLADM or DBADM
authority to invoke the ADMIN_MOVE_TABLE stored procedure, on which
the db2convert command depends. You must also have
the appropriate object creation authorities, including the authority
to issue the SELECT statement on the source table.
Command syntax
>>-db2convert-- -d--database_name------------------------------->
>--+-----------------------------------+--+--------------+------>
+- -cancel--------------------------+ '- -u--creator-'
'-+- -stopBeforeSwap-+--+---------+-'
'- -continue-------' '- -check-'
>--+----------------------------------+------------------------->
'- -z--schema--+-----------------+-'
'- -t--table_name-'
>--+-----------------------------------------------------------+-->
+- -ts--target_tablespace_name------------------------------+
'- -dts--data_tablespace_name-- -its--index_tablespace_name-'
>--+-------------------------------+---------------------------->
'- -sts--source_tablespace_name-'
>--+--------------------------+--+---------+-------------------->
| .-COPY_USE_LOAD-. | '- -trace-'
'- -opt--+-AMT_options---+-'
>--+-------------------------------+--+---------+--------------->
'- -usr--userid-- -pw--password-' '- -force-'
>--+-----------------------+-----------------------------------><
'- -o--output_file_name-'
Command parameters
- -d database_name
- Specifies the name of the database that contains the row-organized tables
that you want to convert into column-organized tables.
You can convert only user-defined tables into column-organized tables.
- -cancel
- Specifies that all failed conversion operations are
to be canceled. The command removes all intermediate data.
- -stopBeforeSwap
- Specifies that the utility stops before it performs the SWAP phase
of the ADMIN_MOVE_TABLE stored procedure and prompts you to complete
an online backup operation before continuing. Only the INIT, COPY,
and REPLAY phases of the stored procedure are performed.
- -continue
- Specifies that the utility performs the SWAP and CLEANUP phases
of the ADMIN_MOVE_TABLE stored procedure to complete the conversion
process. Afterward, the original table is kept or removed, as specified
by the AMT_options option.
- -check
- Specifies that only conversion notes are displayed. No tables
are converted.
- -u creator
- Specifies the creator ID for one or more tables to convert.
- -z schema
- Specifies the schema name of one or more tables to convert.
- -t table_name
- Specifies the unqualified name of the table to convert.
- -ts target_tablespace_name
- Specifies the table space in which the column-organized tables
are created.
- -dts data_tablespace_name
- Specifies the table space for the column-organized data.
- -its index_tablespace_name
- Specifies the table space for the unique indexes on the column-organized tables.
These indexes are restricted to page map indexes and unique indexes
in support of enforced primary key constraints and unique constraints.
- -sts source_tablespace_name
- Specifies that only tables in the named table space are converted.
- -opt
- Species options for the conversion operation.
- COPY_USE_LOAD
- Specifies that the ADMIN_MOVE_TABLE procedure is to copy the data
by default.
- AMT_options
- Specifies a string that contains one or more ADMIN_MOVE_TABLE
procedure options. If you specify more than one option, you must separate
the options by commas; for example, -opt
'COPY_USE_LOAD, COPY YES, COPY_STATS, KEEP'.
- -trace
- Specifies that an ADMIN_MOVE_TABLE procedure trace is generated
for diagnostic purposes.
- -usr userid
- Specifies the user ID that the db2convert command
uses to log on to a remote system.
- -pw password
- Specifies the password that the db2convert command
uses to log on to a remote system.
- -force
- Specifies that all table types are to be converted, including
range partitioned tables, multidimensional clustering (MDC) tables,
and insert time clustering (ITC) tables.
- -o output_file_name
- Specifies the file to which all messages are written.
Usage notes
IBM® InfoSphere® Optim™ Query Workload Tuner Version 4.1 includes
the Workload Table Organization Advisor, which examines all of the
tables that are referenced by the statements in a query workload.
Its recommendations lead to the best estimated performance improvement
for the query workload as a whole. The advisor presents its analysis
and rationales so that you can see the tables that are recommended
for conversion from row to column organization. For complete details
about the Workload Table Organization Advisor, see http://pic.dhe.ibm.com/infocenter/dstudio/v4r1/topic/com.ibm.datatools.qrytune.workloadtunedb2luw.doc/topics/genrecswtoa.html.
Table conversion is permanent and
cannot be undone.
Because this command calls the ADMIN_MOVE_TABLE
stored procedure, the command inherits all restrictions that apply
to the procedure.
You cannot convert the following
table types into
column-organized tables:
- Range clustered tables
- Typed tables
- Materialized query tables
- Declared global temporary tables
- Created global temporary tables
Important: Range partitioned tables, MDC tables,
and ITC tables are not converted by default. To convert these table
types, use the -force option.
Tables
in partitioned database environments, tables in nonautomatic storage
table spaces, tables that have generated columns, and tables with
columns of type BLOB, DBCLOB, CLOB, or XML cannot be converted into column-organized tables.
The
following table attributes are not used during conversion to
column-organized tables:
- Triggers
- Secondary indexes
If they are not required, drop any dependent objects that cannot
be transferred to
column-organized tables
before invoking the
db2convert command.
The
following table attributes are used as NOT ENFORCED during conversion
to
column-organized tables:
- Foreign keys
- Check constraints
The table conversion process temporarily requires space
for both the source and the target tables.
Because there is
no online process to convert column-organized tables
back to row-organized tables,
the best practice is to perform a backup before you convert the tables
to column organization.
If
the database is recoverable and you don't specify
-opt parameter,
or in
-opt parameter, you don't specify
COPY_USE_LOAD with
sub-option
COPY YES, performing the conversion in
three separate steps is strongly recommended in order to ensure recoverability:
- Invoke the db2convert command, specifying the -stopBeforeSwap option.
- Perform a manual online backup of the target table space or table
spaces.
- Invoke the db2convert command, specifying the -continue option.
If the table being converted has foreign key (referential
integrity) constraints, expect a long offline phase for the table
during conversion.
Examples
- Full database conversion
- To convert all the tables in MYDATABASE into column-organized tables,
issue the following command after you perform a full database backup:
db2convert -d mydatabase
After
an initialization period, the command output shows information about
the table size and compression ratios, as shown in the following example: Table NumRows RowsComm InitSize(MB) FinalSize(MB) CompRate(%) Progress(%)
--------------- -------- -------- ------------- -------------- ------------ ---------------
USER.TABLE1 1500000 0 105.47 0.26 99.76 0
USER.TABLE2 1500000 0 105.47 0.26 99.76 0
USER.TABLE3 1500000 0 105.47 0.26 99.76 0
Total Pre-Conversion Size (MB): 316.42
Total Post-Conversion Size (MB): 0.77
Total Compression Rate (Percent): 99.76
RowsComm represents
the number of rows that were converted so far.
- Single table conversion
- To convert a single table (TABLE1 with schema USER in MYDATABASE)
that the Workload Table Organization Advisor identified as a good
candidate for conversion, issue the following command:
db2convert -d mydatabase -z user -t table1
This
command returns the following sample output: Table NumRows RowsComm InitSize(MB) FinalSize(MB) CompRate(%) Progress(%)
--------------- -------- -------- ------------- -------------- ------------ ---------------
USER.TABLE1 1500000 0 105.47 0.26 99.76 0
Total Pre-Conversion Size (MB): 105.47
Total Post-Conversion Size (MB): 0.26
Total Compression Rate (Percent): 99.76