Distributed DBA: Table movement made easy

Putting the ADMIN_MOVE_TABLE() procedure to work

With the help of DB2 9.7, learn how to move tables, indexes, long data, and large object (LOB) data from one table space to another using the ADMIN_MOVE_TABLE() procedure. This article also demonstrates how to use the ADMIN_MOVE_TABLE() procedure to alter the structure of an existing table during a move operation.

This article was originally published in IBM Data magazine.

Roger Sanders, Senior Consultant Corporate Systems Engineer, EMC Corporation

Roger E. Sanders (roger_e_sanders@yahoo.com), a senior consultant corporate systems engineer at EMC Corporation, is the author of 21 books on DB2 for Linux, UNIX, and Windows and a recipient of the 2011 IBM Champion award. His latest book is titled From Idea to Print: How to Write a Technical Article or Book and Get It Published. Special thanks to Jens Seifert, technical lead for SAP on DB2 for Linux, UNIX, and Windows—and creator of the ADMIN_MOVE_TABLE() procedure—for providing feedback on the information presented in this article.



21 October 2011

Also available in Chinese

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.


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:

Listing 1. Syntax for invoking ADMIN_MOVE_TABLE( )
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:

Listing 2. Syntax for SYSPROC.ADMIN_MOVE_TABLE()
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:

Listing 3. Syntax to execute a query against the system catalog
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:

Listing 4. Syntax for creating a table
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:

Listing 5. Syntax for dropping one column and adding another
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:

Listing 6. Syntax for moving the original table into the target
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.


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 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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=766668
ArticleTitle=Distributed DBA: Table movement made easy
publish-date=10212011