- Read this article in our interactive digital edition format!
- Subscribe to IBM Data Management magazine

In the past, if you wanted to move an existing table from one table space to another with possibly different storage characteristics—say, because you wanted to change the extent size being used with a particular table, or you wanted to change from system managed space (SMS) to database managed space (DMS) table spaces—you had to export the table's data to an external file, drop and re-create the table in the desired table space, and then populate the newly created table using the data stored in the external file. You used a similar process if you wanted to change where index and/or long data for a table was stored. Consequently, such operations were not performed very often, if at all, because of the downtime and resources required.

But with DB2 9.7, tables, indexes, long data, and large object (LOB) data can easily be moved from one table space to another using the ADMIN_MOVE_TABLE() procedure. In this column, I'll introduce you to the ADMIN_MOVE_TABLE() procedure and I'll show you how this procedure can be used to move a table from one table space to another. I'll also demonstrate how to use this procedure to alter the structure of an existing table during a move operation.

The ADMIN_MOVE_TABLE() procedure

The ADMIN_MOVE_TABLE() procedure is designed to move data stored in an existing table to a new table object that has the same name but that may have been defined in a different table space. It can also be used to change the multidimensional clustering specification for a table, modify a table's partitioning keys, change the range partitioning specification for a table, add or remove columns from a table, change a column's data type (provided the new data type is compatible with the data type being used), or create a new compression dictionary for a table that has deep compression enabled. Furthermore, the table can remain online and accessible while these operations are performed.

When the ADMIN_MOVE_TABLE() procedure is invoked, a shadow copy of the table to be moved is created in the table space specified—provided a target table doesn't already exist. (It's possible to create the target table first and then move data into it.) A staging table is also created, and any data changes made during the move operation are written to this table by a set of triggers that the procedure defines on the source table. Data is then copied from the source to the target using either an INSERT FROM CURSOR (the default) or a LOAD FROM CURSOR operation. Once the data has been copied, changes captured in the staging table are replayed against the target to bring it up to date. Following that, the source table is briefly taken offline, and its name is assigned to the target table. The target table is then brought online, replacing the source. By default, the source table is then dropped; however, it can be kept and renamed if that behavior is desired.

Along with moving a table, the ADMIN_MOVE_TABLE() procedure will move any indexes, triggers, and views that have been defined on the table being moved. Currently, however, this procedure does not support the movement of foreign keys. Therefore, if the table to be moved was the parent or child in a referential integrity constraint, you will need to capture any foreign key definitions that exist before starting a move and recreate them after the move is complete. It's also important to note that this procedure requires additional storage for the target and staging tables, as well as for any log entries that are generated when the triggers are fired. Moreover, this procedure will increase transactional overhead whenever a move operation is in progress.

Back to top

Invoking the ADMIN_MOVE_TABLE() procedure

As with any stored procedure, the ADMIN_MOVE_TABLE( ) procedure is invoked by executing a CALL statement with the name of the procedure and any required input parameter values specified. Therefore, the syntax used to invoke the ADMIN_MOVE_TABLE( ) procedure looks like this:

                
CALL SYSPROC.ADMIN_MOVE_TABLE( 
[SourceSchema],
[SourceTable],
[TgtData_TS],
[TgtIndex_TS],
[TgtLong_TS],
[MDC_Cols],
 [PartKey_Cols],
[DataPartitions],
[ColDefs],
[Option,...],
[Operation])
 
or
  
CALL SYSPROC.ADMIN_MOVE_TABLE( 
[SourceTable],
[TargetTable],
[Option,...],
[Operation])
                

where:

• SourceSchema is the name of the schema containing the table to be moved.
• SourceTable is the name of the table to be moved.
• TgtData_TS is the table space that the table is to be moved to.
• TgtIndex_TS is the table space that indexes associated with the table are to be moved to.
• TgtLong_TS is the table space that long data values associated with the table are to be moved to.
• MDC_Cols is the multidimensional column (MDC) specification to be used with the target table.
• PartKey_Cols is the partitioning key column specification to be used with the target table.
• DataPartitions is the data partitioning specification to be used with the target table.
• ColDefs identifies a new column definition for the target table. This parameter is used to add columns, remove columns, or change column data types.
• TargetTable is the name of the table that is the target for the move operation.
• Option identifies one or more options that can be used to control the behavior of this procedure. Valid values for this parameter are KEEP, COPY_USE_LOAD, COPY_WITH_INDEXES, FORCE, NO_STATS, COPY_STATS, NO_AUTO_REVAL, REORG, NO_TARGET_LOCKSIZE_TABLE, CLUSTER, NON_CLUSTER, and LOAD_MSGPATH [Path]. (Refer to the DB2 Information Center at publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp for more information about these options.)
• Operation identifies the specific operation that this procedure is to perform when invoked. Valid values for this parameter are MOVE, INIT, COPY, REPLAY, VERIFY, SWAP, CLEANUP, and CANCEL. If you want to have more control over the MOVE process (for example, to manage when the target table is taken offline), you can call the procedure multiple times—once for each of the following operations: INIT, COPY, REPLAY, and SWAP.

There are two ways the ADMIN_MOVE_TABLE() procedure can be used:one relies on the procedure to create the necessary target table while the other requires you to create the target table in advance. The first approach is typically used if you only want to move a table, its indexes, and/or its long data from one set of table spaces to another. The second is often used when you want to make significant changes to the target table's definition.

Thus, a statement to move a table named EMPLOYEES (which resides in a schema named HR) along with its indexes and long data to a table space named USERSPACE2 would look like this:

                
CALL SYSPROC.ADMIN_MOVE_TABLE (
'HR',
'EMPLOYEES',
'USERSPACE2',
'USERSPACE2',
'USERSPACE2',
'',
'',
'',
'',
'',
'MOVE')
             

To verify the table was moved as expected, you could execute a query against the system catalog that looks like this:

                 
SELECT 
CHAR(tabschema, 16) AS tabschema, 
CHAR(tabname, 16) AS tabname, 
CHAR(tbspace, 16) AS tbspace 
FROM syscat.tables 
WHERE tabname = 'EMPLOYEES'
              

On the other hand, to alter the definition of a table as part of a move (say by dropping one column and adding another), you can either provide the desired column definition in the ColDefs input parameter or you can create a target table yourself in advance. For example, suppose you have a table named EMPLOYEES that was created using the following statement:

                
CREATE TABLE hr.employees 
(empid INT NOT NULL PRIMARY KEY, 
salary DECIMAL(8,2), 
sex CHAR) IN userspace1
               

To drop one column and add another as part of a move operation, you could first create the desired target table in the desired table space. For example:

                
CREATE TABLE hr.employees_tgt 
(empid INT NOT NULL PRIMARY KEY,
salary DECIMAL(10,2),
bonus DECIMAL(6,2) DEFAULT 0.0) 
IN userspace2
            

Then, you could move the original table into the target as follows:

                
CALL SYSPROC.ADMIN_MOVE_TABLE (
'HR',
'EMPLOYEES',
'EMPLOYEES_TGT',
'',
'MOVE')
            

Once the move is complete, you can query the system catalog to verify the target table has replaced the source, and you can query the new source to verify that the column definition was changed.

Back to top

Conclusion

With the introduction of the ADMIN_MOVE_TABLE() procedure, tasks such as moving a table from one table space to another or altering a table's definition can be performed without the need for an outage window. In addition, this procedure enables perfect compression dictionaries to be created and entire tables to be compressed without having to perform a costly offline REORG operation.

So take a closer look at the ADMIN_MOVE_TABLE() procedure. Chances are it will allow you to make changes to your database that you've been putting off because of the amount of overhead required.

Resources

Learn

• Get more information about table movement in DB2 for Linux, UNIX, and Windows in the Information Center.
  
  
• Deepen your DB2 skills with Best practices for DB2 for Linux, UNIX, and Windows.
  
  
• Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills.
  
  
• Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
  
  

Get products and technologies

• Download a free trial version of DB2 for Linux, UNIX, and Windows.
  
  
• Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• The ADMIN_MOVE_TABLE() procedure
• Invoking the ADMIN_MOVE_TABLE() procedure
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers