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

Read syntax diagramSkip visual syntax diagramdb2convert-ddatabase_name -tntenant_name-cancel-stopBeforeSwap-continue-check-ucreator-zschema-ttable_name-tstarget_tablespace_name-dtsdata_tablespace_name-itsindex_tablespace_name-stssource_tablespace_name-optCOPY_USE_LOADAMT_options-trace-usruserid-pwpassword-force-ooutput_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.
-tntenant_name
Specifies the name of the tenant 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. If not specified, the default SYSTEM tenant is assumed.
-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 indexes on the column-organized tables.
-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://www.ibm.com/support/knowledgecenter/SS62YD_4.1.1/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 non-automatic storage table spaces and tables with columns of types BLOB, DBCLOB, CLOB, or XML cannot be converted into column-organized tables.

Triggers are not supported on column-organized tables. They are not used during the conversion and tables with triggers cannot be converted.

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 converted to 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:
  1. Invoke the db2convert command, specifying the -stopBeforeSwap option.
  2. Perform a manual online backup of the target table space or table spaces.
  3. 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