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
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
procedure was enhanced to move data from a row-organized table into a
column-organized table online. The new
was also provided. The
db2convert command internally calls the
ADMIN_MOVE_TABLE procedure but has a simple interface and is easier
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
- 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
In DB2 10.5, the
db2convert command was introduced to convert one or all
row-organized tables into column-organized tables. The
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:
Note: Range-partitioned tables,
MDC tables, and insert time-clustering (ITC) tables are not converted by default.
To convert these table types, use the
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.
db2convert calls the
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
-continue. These are not case-sensitive.
If you specify the
-stopBeforeSwap parameter, only the
REPLAY phases of the
ADMIN_MOVE_TABLE procedure are performed. After
-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
procedure SWAP and CLEANUP phases. The following sample commands illustrate these
db2convert -d TESTDB -z SCHEMA1 -t TAB1 –stopBeforeSwap; BACKUP DB TESTDB ONLINE TO destination ; BACKUP DB TESTDB TABLESPACE targetDataTableSpace ONLINE TO destination; db2convert -d TESTDB -z SCHEMA1 -t TAB1 –continue;
The following examples show how to use various parameters of the
- To convert all tables in a particular schema (SCHEMA1), use the
db2convert -d TESTDB -z SCHEMA1
- To perform a check before a conversion, use the
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
–checkparameter, the tables are not converted.
- To convert all tables owned by one user, use the
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
db2convert -d TESTDB -z SCHEMA1 -t TAB1 –ts TBSP2
- To convert all tables in a source tablespace to a target tablespace, use the
db2convert -d TESTDB -sts TBSP1 -ts TBSP2
-stsparameter indicates the source tablespace TBSP1, which can be an SMS or DMS tablespace. The
-tsparameter 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
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_TABLEprocedure options and the
db2convert -d TESTDB -z SCHEMA1 -t TAB1 –opt 'FORCE, COPY_OVER_INDEX, COPY_USE_LOAD, NO_STATS, REVAL_ALL_OBJECTS'
When you specify the
ADMIN_MOVE_TABLEprocedure trace is generated for diagnostic purposes.
- To specify a database login when converting a table, use the
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
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.
ADMIN_MOVE_TABLE procedure has five phases.
Figure 1. Phases of
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
INITphase 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
ORGANIZE BY COLUMN parameter was introduced to convert a
row-organized table to a column-organized table by using either the one-step or
The following example shows the one-step approach to convert the table.
CALL ADMIN_MOVE_TABLE('DB2INST1','TAB1','TS1','TS1','TS1', 'ORGANIZE BY COLUMN','','','','COPY_USE_LOAD','MOVE');
In the following example, the multiple-step approach is used to convert the table.
CALL ADMIN_MOVE_TABLE('DB2INST1','TAB1','TS1','TS1','TS1', 'ORGANIZE BY COLUMN','','','','','INIT'); CALL ADMIN_MOVE_TABLE('DB2INST1','TAB1','','','', 'ORGANIZE BY COLUMN','','','','COPY_USE_LOAD','COPY'); CALL ADMIN_MOVE_TABLE('DB2INST1','TAB1','','','', 'ORGANIZE BY COLUMN','','','','COPY_USE_LOAD','REPLAY'); CALL ADMIN_MOVE_TABLE('DB2INST1','TAB1','','','', 'ORGANIZE BY COLUMN','','','','COPY_USE_LOAD','SWAP');
If the whole move or one intermediate phase (
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
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.
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
- To help improve the compression and performance, use the
COPY_USE_LOADoption to do the
COPYoperation instead of using insert statements. By default, the
INSERTis used when you perform the
- To avoid a long
REPLAYphase, run the
- Run the procedure as multiple steps instead of as one step. Run the
REPLAYphase and the
SWAPphase when the activity against the table is lower, and run the
REPLAYphase 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."
The following considerations apply to partitioned tables:
- If you defined the table as
DISTRIBUTE BY HASH, specify the minus sign (
-) for the
partkey_colsvariable 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_partparameter 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
ORGANIZE BY COLUMNclause 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.
CALL ADMIN_MOVE_TABLE('TEST','DP1','','','','ORGANIZE BY COLUMN','-','','' ,'','MOVE'); CALL ADMIN_MOVE_TABLE('TEST','RP1','','','','ORGANIZE BY COLUMN','','-','' ,'','MOVE'); CALL ADMIN_MOVE_TABLE('TEST','IT1','','','','ORGANIZE BY COLUMN','','','' ,'','MOVE');
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
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
CALL ADMIN_MOVE_TABLE('TEST','T_STUDENT','','','', 'ORGANIZE BY COLUMN','','','','COPY_USE_LOAD, NOT_ENFORCED','MOVE');
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
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.
CALL ADMIN_MOVE_TABLE ('TEST','DP1','','','', 'ORGANIZE BY COLUMN','','','','','INIT'); CALL ADMIN_MOVE_TABLE ('TEST','DP1','','','', 'ORGANIZE BY COLUMN','','','','COPY_USE_LOAD','COPY'); CALL ADMIN_MOVE_TABLE ('TEST','DP1','','','', 'ORGANIZE BY COLUMN','','','','COPY_USE_LOAD','REPLAY'); BACKUP DB dbname TABLESPACE targetDataTableSpace, targetIndexTableSpace, targetLongTableSpace ONLINE TO destination; CALL ADMIN_MOVE_TABLE ('TEST','DP1','','','', 'ORGANIZE BY COLUMN','','','','COPY_USE_LOAD','SWAP');
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
db2convert command, use the following manual
- Extract the full set of DDL statements for the source table by using the
- Modify the
db2lookscript, such as by changing the table or table space names or by adding or removing columns.
- Run the
- If needed, lock the source table.
- Use the
LOADutility to export or import the data to the target table, or use the
LOAD FROM CURSORutility 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
SWAP phase. A best practice is to use the
multiple-step approach and perform the
SWAP phases when the system is not
db2convert command calls the
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
COPY phase takes several hours.
If the system is very busy, the
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 to complete the
REPLAY phases, and
–continue to complete the
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.
This section covers some common issues with the
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
db2 "call SYSPROC.ADMIN_MOVE_TABLE ('PSHIVANN','TAB2','','','CANCEL')"
ADMIN_MOVE_TABLE procedure or
db2convert command failed
db2convert -d sample -z db2inst1 -t act ---ADMIN_MOVE_TABLE failed for table "DB2INST1"."ACT" with error message below--- CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','ACT','','','', 'ORGANIZE BY COLUMN','','-','','COPY_USE_LOAD,NOT_ENFORCED','COPY') 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
call ADMIN_MOVE_TABLE('DB2INST1','ACT','','','','','','','','','CANCEL'); db2convert –d sample –z db2inst1 –t act;
ADMIN_MOVE_TABLE procedure reported the SQL3011C, continue the rest of the
phases, calling the
CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','ACT','','','', 'ORGANIZE BY COLUMN','','-','','COPY_USE_LOAD,NOT_ENFORCED','COPY'); CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','ACT','','','', 'ORGANIZE BY COLUMN','','-','','COPY_USE_LOAD,NOT_ENFORCED','REPLAY'); CALL SYSPROC.ADMIN_MOVE_TABLE('DB2INST1','ACT','','','', 'ORGANIZE BY COLUMN','','-','','COPY_USE_LOAD,NOT_ENFORCED','SWAP');
db2convert command or ADMIN_MOVE_TABLE procedure does not
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
db2 "select substr(key,1,30), substr(value,1,30) from systools.admin_move_table where tabname='TAB7'" 1 2 ------------------------------ ------------------------------ LOCK 2013-09-23-03.52.18.622432 STATUS REPLAY …… INIT_START 2013-09-23-03.42.20.376430 TABNAME_IN_CATALOG TAB7 …… TARGET TAB7AABYVXt STAGING TAB7AABYVXs …… REPLAY_START 2013-09-23-03.50.01.773743
Next, check whether the
REPLAY phase can catch up with the changes being
introduced by online transactions. The following example shows how to use
MON_GET_TABLE function to obtain the number of rows being inserted and deleted to the staging
db2 "select rows_inserted, rows_deleted from table(mon_get_table ('TEST','TAB7AABYVXs',-1))" ROWS_INSERTED ROWS_DELETED -------------------- -------------------- 7702 300 // after 2 minutes, check it again db2 "select rows_inserted, rows_deleted from table(mon_get_table ('TEST','TAB7AABYVXs',-1))" ROWS_INSERTED ROWS_DELETED -------------------- -------------------- 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
ADMIN_MOVE_TABLE procedure by pressing Ctrl+C. After killing the
procedure, rerun it to cancel the failed move. Next, rerun the procedure starting
INIT phase, and run the
SWAP phases when the system is not as
// Press CTRL+C to kill the in progress command. db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN', '','','','COPY_USE_LOAD','replay')"; // Cancel the table move. db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN', '','','','COPY_USE_LOAD','cancel')"; // Restart the table move db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN', '','','','COPY_USE_LOAD','INIT')"; db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN', '','','','COPY_USE_LOAD','COPY')"; // When the system is not as busy, rerun the REPLAY and SWAP phases. db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN', '','','','COPY_USE_LOAD','replay')"; db2 "call admin_move_table('TEST','TAB7','','','','ORGANIZE BY COLUMN', '','','','COPY_USE_LOAD','swap')";
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
-- 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 2 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
procedure, and the manual approach. We also provided an overview of how
ADMIN_MOVE_TABLE procedure and the
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.
- "Super Analytics, Super Easy" (IBM data magazine, 2013): Discusses DB2 10.5 with BLU Acceleration.
- View the video Deep Dive on BLU Acceleration in DB2 10.5, Super Analytics, Super Easy.
- "Performance considerations of the DB2 for Linux, UNIX, and Windows stored procedure ADMIN_MOVE_TABLE" (developerWorks, 2013): Examines tables with the characteristics of typical SAP systems.
- The DB2 10.5 Knowledge Center has more about "ADMIN_MOVE_TABLE procedure – Move tables online" and "db2convert – Convert row-organized tables into column-organized tables."
- Follow developerWorks on Twitter.
- Visit the developerWorks Information Management zone to find more resources for DB2 developers and administrators.
Get products and technologies
- Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment.
- Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.