Table movement made easy
Putting the ADMIN_MOVE_TABLE() procedure to work
This content is part # of # in the series: Distributed DBA
This content is part of the series:Distributed DBA
Stay tuned for additional content in this series.
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
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.
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
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
Along with moving a table, the
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
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
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])
- 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
MOVEprocess (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.
With the introduction of the
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
So take a closer look at the
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.
- 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.
- 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.
- 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.