Convert row-organized tables to column-organized tables in DB2 10.5 with BLU Acceleration


DB2 9.7 introduced the ADMIN_MOVE_TABLE procedure, which is an online utility for moving a table while the data remains online and available for access. This procedure is also known as online_table_move among SAP customers. The ADMIN_MOVE_TABLE procedure can fulfill various business needs. For example, you can use it to move a table to a larger tablespace, perform online table compression, perform online reorganization for a multidimensional clustering (MDC) table, or perform an online migration to an MDC table.

DB2 10.5 introduced BLU Acceleration. This new support of column-organized tables is ideal for data warehouse and analytic workloads. To fully take advantage of the power of DB2 10.5 with BLU Acceleration, the ADMIN_MOVE_TABLE stored procedure was enhanced to move data from a row-organized table into a column-organized table online. The new db2convert command was also provided. The db2convert command internally calls the ADMIN_MOVE_TABLE procedure but has a simple interface and is easier to use.

Of course, you can also do the conversion manually. For instance, you can create a column-organized table with the same structure as the row-organized table and use the DB2 LOAD utility to populate the table.

In DB2 10.5, row-organized and column-organized tables can coexist in a database and, optionally, in the same tablespace. You can convert all or a subset of row-organized tables into column-organized tables after upgrading the database to DB2 10.5. To help you convert the tables, this article addresses the following questions:

  • How do you determine which row-organized tables to convert to column-organized tables when you have a mixed workload?
  • How do you use the command-line conversion db2convert command?
  • What happens to indices, partitions, and constraints after conversion?
  • Which process best meets your business needs?
  • Are there any considerations before or after the conversion?

After reading this article, you will know how to use three approaches to table conversion: db2convert command, the ADMIN_MOVE_TABLE procedure, and a manual approach.

The db2convert command

In DB2 10.5, the db2convert command was introduced to convert one or all row-organized tables into column-organized tables. The db2convert command connects to the database before performing the conversion. The command displays statistics about the conversion for monitoring purposes.

You can use db2convert only to convert tables to the column-organized format from the row-organized format, not the reverse. Also, you cannot convert the following table types into column-organized tables:

  • Created global temporary tables
  • Declared global temporary tables
  • Materialized query tables
  • Range-clustered tables
  • Typed tables
  • System catalog tables
  • Monitor tables
  • Explain tables
  • Existing columnar tables
  • Tables already in the process of being converted

If you use db2convert to convert an entire database, the command sequentially converts all eligible user-defined tables.

The following command converts all row-organized user-defined tables to column-organized tables within the database TESTDB:
db2convert –d TESTDB

Note: Range-partitioned tables, MDC tables, and insert time-clustering (ITC) tables are not converted by default. To convert these table types, use the -force parameter.

The following command converts the single row-organized table SCHEMA1.TAB1 to a column-organized table in the database TESTDB:

db2convert -d TESTDB -z SCHEMA1 –t TAB1

You can use the IBM Optim Query Workload Tuner tool to determine which tables to convert into column-organized tables (for details, read "Use Optim Data Tools to get the most out of BLU Acceleration"). After using the Optim Query Workload Tuner to select the tables to convert, you can choose the generated conversion script or the db2convert command to do the conversion.

Recoverability considerations

When db2convert calls the ADMIN_MOVE_TABLE procedure, it uses the COPY_USE_LOAD option by default. If the database is recoverable, you must consider recoverability during the conversion. You can use two command parameters related to recoverability: -stopBeforeSwap and -continue. These are not case-sensitive.

If you specify the -stopBeforeSwap parameter, only the INIT, COPY, and REPLAY phases of the ADMIN_MOVE_TABLE procedure are performed. After the -stopBeforeSwap parameter is finished, you can take a manual online backup of the database or tablespaces where the converted tables are located. After the backup is complete, you must rerun db2convert with the –continue parameter to finish the conversion. The -continue parameter completes the ADMIN_MOVE_TABLE procedure SWAP and CLEANUP phases. The following sample commands illustrate these steps:

db2convert -d TESTDB -z SCHEMA1 -t TAB1 –stopBeforeSwap; 

BACKUP DB TESTDB TABLESPACE targetDataTableSpace ONLINE TO destination;

db2convert -d TESTDB -z SCHEMA1 -t TAB1 –continue;

Additional examples

The following examples show how to use various parameters of the db2convert command.

  • To convert all tables in a particular schema (SCHEMA1), use the -z parameter:
    db2convert -d TESTDB -z SCHEMA1
  • To perform a check before a conversion, use the –check parameter:
    db2convert -d TESTDB -z SCHEMA1 -check

    Displayed on the screen will be a report that includes all the tables under SCHEMA1 that: cannot be converted, will be converted without warnings, or that will be converted with warnings. If you specify the –check parameter, the tables are not converted.

  • To convert all tables owned by one user, use the -u parameter:
    db2convert -d TESTDB -u NEWTON -z SCHEMA1

    Tables owned by NEWTON and in the schema SCHEMA1 are converted to column-organized tables.

  • To convert a table from an SMS or DMS tablespace to an automatic storage tablespace, use the –ts parameter:
    db2convert -d TESTDB -z SCHEMA1 -t TAB1 –ts TBSP2
  • To convert all tables in a source tablespace to a target tablespace, use the -sts and -ts parameters:
    db2convert -d TESTDB -sts TBSP1 -ts TBSP2

    Here, the -sts parameter indicates the source tablespace TBSP1, which can be an SMS or DMS tablespace. The -ts parameter indicates the target tablespace TBSP2, which must be a tablespace with automatic storage. All row-organized tables in the source tablespace TBSP1 are converted to column-organized tables in target tablespace TBSP2.

  • To convert a range-partitioned table from a row-organized table to a column-organized table, use the –force parameter:
    db2convert -d TESTDB -z SCHEMA1 -t TAB1 –force

    The converted table, TAB1, is now a regular table.

  • To convert a table by using a few ADMIN_MOVE_TABLE procedure options and the –trace parameter:
    db2convert -d TESTDB -z SCHEMA1 -t TAB1 –opt 'FORCE, 

    When you specify the –trace parameter, an ADMIN_MOVE_TABLE procedure trace is generated for diagnostic purposes.

  • To specify a database login when converting a table, use the -usr and -pw parameters:
    db2convert -d TESTDB -z SCHEMA1 -t TAB1 -usr BOSS -pw PASSWORD

    In this example, the user ID BOSS with password PASSWORD are used to log on to a remote system to convert table TAB1.

  • To convert a table and redirect the messages to a specified file, use the -o parameter:
    db2convert -d TESTDB -z SCHEMA1 -t TAB1 -o temp.out

    In this example, the messages are redirected to the temp.out file.

  • To convert a table in non-interactive mode:
    db2convert -d TESTDB -z SCHEMA1 -t TAB1 –force -o temp.out

    In this example, we combine the functionality from the previous two examples.


The ADMIN_MOVE_TABLE procedure has five phases.

Figure 1. Phases of ADMIN_MOVE_TABLE
Image shows phases of the ADMIN_MOVE_TABLE
Image shows phases of the ADMIN_MOVE_TABLE

The phases perform various actions:

  • INIT— Initializes all objects involved in the operation. The objects include the staging table, which is key for capturing all the changes during the move.
  • COPY— Creates a copy of the source table according to its current definition and its data is populated into the target table. Any insert, update, or delete operations that occur after the phase begins are stored in the staging table.
  • REPLAY— Replays the changes saved in the staging table to perform insert, update, and delete operations in the target table.
  • SWAP— Performs the swap of the source and target tables. There's a short offline phase during renaming of the tables and indices.
  • CLEANUP— Drops temporary objects from the INIT phase that are no longer needed.

There are two approaches to running the ADMIN_MOVE_TABLE procedure: the one-step approach (using the MOVE option) and the multiple-step approach. In DB2 10.5, the ORGANIZE BY COLUMN parameter was introduced to convert a row-organized table to a column-organized table by using either the one-step or multiple-step approach.

The following example shows the one-step approach to convert the table.


In the following example, the multiple-step approach is used to convert the table.

'ORGANIZE BY COLUMN','','','','','INIT');

If the whole move or one intermediate phase (INIT, COPY, REPLAY, or SWAP) fails, you can perform a CANCEL. This phase cleans up any intermediate data (indices, the staging table, the target table, and triggers on the source table) and resets the table flags. You can successfully complete the CANCEL phase only if the previous operation status is not COMPLETE or CLEANUP. You can look up the status in the SYSTOOLS.ADMIN_MOVE_TABLE protocol table. The following example shows how to specify the CANCEL parameter.

CALL ADMIN_MOVE_TABLE('DB2INST1','TAB1','','','','','','','','','CANCEL');

ADMIN_MOVE_TABLE procedure options and performance

Most of the ADMIN_MOVE_TABLE procedure options that apply to row-organized tables also apply to column-organized tables.

The ADMIN_MOVE_TABLE procedure is an online data movement process, so if the source table is online during the conversion, performance is a critical objective. Following are some recommendations when using the ADMIN_MOVE_TABLE procedure:

  • To help improve the compression and performance, use the COPY_USE_LOAD option to do the COPY operation instead of using insert statements. By default, the INSERT is used when you perform the COPY phase.
  • To avoid a long REPLAY phase, run the REDIRECT phase after COPY.
  • Run the procedure as multiple steps instead of as one step. Run the REPLAY phase and the SWAP phase when the activity against the table is lower, and run the REPLAY phase multiple times.
  • If there's no unique index defined on the row-organized source table, manually create a unique index on that table before the conversion.

You can apply all the recommendations for a row-organized table to the conversion from a row-organized table to a column-organized table. For details, see "Performance considerations of the DB2 for Linux, UNIX, and Windows stored procedure ADMIN_MOVE_TABLE."

Partitioned tables

The following considerations apply to partitioned tables:

  • If you defined the table as DISTRIBUTE BY HASH, specify the minus sign (-) for the partkey_cols variable of the procedure to force the conversion of tables in a partitioned database.
  • If you defined the table as PARTITION BY RANGE, specify the minus sign (-) for the data_part parameter of the procedure to convert the range-partitioned tables by force.
  • MDC and ITC tables are converted into column-organized tables by default if you specify the ORGANIZE BY COLUMN clause for the procedure.

In the following example, TEST.DP1 is a range-partitioned table, TEST.RP1 is a range-partitioned table, and TEST. IT1 is an ITC table.



The ADMIN_MOVE_TABLE procedure silently converts primary key and unique indices into primary key or unique constraints and ignores all non-unique indices. If the row-organized source table has a unique index defined on nullable columns, the ADMIN_MOVE_TABLE procedure fails with error SQL2103N.


To convert tables with enforced referential integrity constraints and enforced check constraints, use the NOT_ENFORCED option of the ADMIN_MOVE_TABLE procedure.


After conversion, the referential integrity constraint is set to NOT ENFORCED, so you must update the applications that depend on the constraint accordingly.

If you convert a table with enforced referential integrity constraints or enforced check constraints and do not specify the NOT_ENFORCED option, SQL1667N is returned.


If you use the COPY_USED_LOAD option for the ADMIN_MOVE_TABLE procedure and database recoverability is enabled, you must perform a manual online backup before the SWAP phase. In this case, you must run the procedure by using a multiple-step approach.

'ORGANIZE BY COLUMN','','','','','INIT');

BACKUP DB dbname TABLESPACE targetDataTableSpace, targetIndexTableSpace,
targetLongTableSpace ONLINE TO destination;


Manual approach

As mentioned, db2convert cannot convert all types of tables, so the ADMIN_MOVE_TABLE procedure cannot convert these types of tables, either. (For more details, see the DB2 10.5 Knowledge Center.) For tables that cannot be converted with the db2convert command, use the following manual approach:

  • Extract the full set of DDL statements for the source table by using the db2look command.
  • Modify the db2look script, such as by changing the table or table space names or by adding or removing columns.
  • Run the db2look script.
  • If needed, lock the source table.
  • Use the EXPORT, IMPORT, or LOAD utility to export or import the data to the target table, or use the LOAD FROM CURSOR utility to copy the data.
  • Rename the tables.
  • If you locked the source table, unlock it.
  • If necessary, drop the source table.

Advantages and disadvantages of the three approaches

One advantage of the ADMIN_MOVE_TABLE procedure is that you can perform the conversion in online or offline mode. The online mode is usually preferred because you can access and modify the table during the conversion. Also, CLI applications can call the procedure. Another advantage is that you can control when to perform the REPLAY or SWAP phase. A best practice is to use the multiple-step approach and perform the REPLAY or SWAP phases when the system is not busy.

Although the db2convert command calls the ADMIN_MOVE_TABLE procedure, db2convert will convert tables only online. However, the db2convert command provides a more robust UI than the ADMIN_MOVE_TABLE procedure. You can convert selected tables or all tables in the specified database or in the specified tablespace. The command filters out tables that are unsupported for conversion and issues warnings when other tables cannot be converted or when characteristics in the source table will be applied to the target table. The db2convert command prints statistical messages about the conversion progress and a comparison of the compression rates for the row-organized and column-organized tables. These messages are helpful if the table to be converted has a large volume of data and the ADMIN_MOVE_TABLE procedure COPY phase takes several hours.

If the system is very busy, the ADMIN_MOVE_TABLE procedure REPLAY phase might never catch up with the current transaction activity. If you run the procedure directly, you should run it by using the multiple-step approach and run the SWAP phase when the system is not busy. If you use db2convert, you should use a corresponding two-step process by specifying the -stopBeforeSwap and –continue parameters. Use -stopBeforeSwap to complete the INIT, COPY, and REPLAY phases, and –continue to complete the SWAP phase.

To use the manual approach, you must take the table offline. However, if you cannot convert a table by using the ADMIN_MOVE_TABLE procedure or the db2convert command because of a restriction, the manual approach is the only option.

Troubleshooting db2convert issues

This section covers some common issues with the db2convert command.

db2convert command failed with SQL2441N

A possible cause of the SQL2441N message is that the conversion was interrupted or did not finish yet. First, check whether conversion of the same table is being performed by another process. If so, wait for the other process to finish. Otherwise, issue the ADMIN_MOVE_TABLE procedure with the CANCEL option specified, then rerun db2convert.


ADMIN_MOVE_TABLE procedure or db2convert command failed with SQL3011C

db2convert -d sample -z db2inst1 -t act

---ADMIN_MOVE_TABLE failed for table "DB2INST1"."ACT" with error message below---
 SQL3011C  There is not enough storage to process the command.  SQLSTATE=HY00

SQL2448N  The db2convert command failed to convert any row-organized tables to column-organized tables.

If the procedure or command fails with SQL3011C, determine whether to increase the heap size.

If the SQL3011C is reported while running db2convert, cancel the move and rerun the db2convert command.

call ADMIN_MOVE_TABLE('DB2INST1','ACT','','','','','','','','','CANCEL');
db2convert –d sample –z db2inst1 –t act;

If the ADMIN_MOVE_TABLE procedure reported the SQL3011C, continue the rest of the phases, calling the ADMIN_MOVE_TABLE procedure.


db2convert command or ADMIN_MOVE_TABLE procedure does not finish

A possible cause is the system is busy, and the REPLAY phase cannot catch up when applying entries from the staging table into the target table. Check the ADMIN_MOVE_TABLE procedure status. If the status is REPLAY, as shown in the following example, the phase is not complete yet.

db2 "select substr(key,1,30), substr(value,1,30) from systools.admin_move_table 
where tabname='TAB7'"
1                              2
------------------------------ ------------------------------
LOCK                           2013-09-23-
STATUS                         REPLAY
INIT_START                     2013-09-23-
TARGET                         TAB7AABYVXt
STAGING                        TAB7AABYVXs
REPLAY_START                   2013-09-23-

Next, check whether the REPLAY phase can catch up with the changes being introduced by online transactions. The following example shows how to use the MON_GET_TABLE function to obtain the number of rows being inserted and deleted to the staging table.

db2 "select rows_inserted, rows_deleted from table(mon_get_table
-------------------- --------------------
                7702                  300
// after 2 minutes, check it again
db2 "select rows_inserted, rows_deleted from table(mon_get_table
-------------------- --------------------
                8564                  328

The number of rows inserted (8564–7702=862) is much higher than the number of rows being deleted (328–300=28), which means that the REPLAY phase might never catch up. In such a case, if the situation does not improve, you might need to kill the ADMIN_MOVE_TABLE procedure by pressing Ctrl+C. After killing the procedure, rerun it to cancel the failed move. Next, rerun the procedure starting with the INIT phase, and run the REPLAY and SWAP phases when the system is not as busy.

// Press CTRL+C to kill the in progress command.
db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN',

// Cancel the table move.
db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN',

// Restart the table move
db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN',
db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN',

// When the system is not as busy, rerun the REPLAY and SWAP phases.
db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN',
db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN',

db2convert command hangs

create table schema1.tab1(c1 int not null, c2 int not null) organize by row;
create unique index myinx on schema1.tab1(c1);

db2convert -db testdb -z schema1 -t tab1|tee cvt.out;

-- db2convert command is hanging.

The cause is that db2convert is waiting for user confirmation to continue with the conversion. To end the conversion, enter 2, then redirect the messages by using the -o parameter.

-- This is a continuation of the above example. db2convert is 
waiting for manual input.  A two is typed and enter is pressed, 
control is returned to the user.

db2convert -db testdb -z schema1 -t tab1|tee cvt.out
Conversion notes for exceptional table(s)
 Table: SCHEMA1.TAB1:
    -Secondary indexes will be dropped from the table.

Enter 1 to proceed with the conversion.
Enter 2 to quit.

-- Rerun db2convert with the –o option


This article showed how to convert row-organized tables to column-organized tables by using three approaches: the db2convert command, the ADMIN_MOVE_TABLE procedure, and the manual approach. We also provided an overview of how ADMIN_MOVE_TABLE procedure and the db2convert command work, and some corresponding best practices. You also learned some of the advantages and disadvantages of the three approaches, and possible causes and solutions for common problems.


The authors would like to acknowledge Matthias Nicola for reviewing and suggesting numerous improvements to this article.

Downloadable resources

Related topics


Sign in or register to add and subscribe to comments.

Zone=Information Management
ArticleTitle=Convert row-organized tables to column-organized tables in DB2 10.5 with BLU Acceleration