DB2 10.5 for Linux, UNIX, and Windows

db2convert - Convert row-organized tables into column-organized tables

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.

Required Connection

None

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