Previous Topic: Volatile TablesNext Topic: Table Designs for Special Situations

Designing Tables and Indexes for Performance › Special Tables Used for Performance › Clone Tables

Clone Tables

In DB2 9, you can create a clone table on an existing base table at the current server by using the ALTER TABLE statement. Although ALTER TABLE syntax is used to create a clone table, the authorization that is granted as part of the clone creation process is the same as you would get during regular CREATE TABLE processing. The schema (creator) for the clone table is the same as the base table. You can create a clone table only if the base table is in a universal table space.

To create a clone table, issue an ALTER TABLE statement with the ADD CLONE option. For example:

ALTER TABLE base-table-name ADD CLONE clone-table-name

The creation or drop of a clone table does not affect the applications that are accessing base table data. No base object quiesce is necessary and this process does not invalidate plans, packages, or the dynamic statement cache.

You can exchange the base and clone data by using the EXCHANGE statement. To exchange table and index data between the base table and clone table, issue an EXCHANGE statement with the DATA BETWEEN TABLE table-name1 AND table-name2 syntax.

Note: This is a method of performing an online load replace.

After a data exchange, the base and clone table names remain the same as they were before the data exchange. No data movement actually occurs. The instance numbers in the underlying VSAM data sets for the objects (tables and indexes) change, which changes the data that appears in the base and clone tables and their indexes.

Example

A base table that exists with the data set name *I0001.*. The table is cloned and the clone's data set is initially named *.I0002.*. After an exchange, the base objects are named *.I0002.* and the clones are named *I0001.*. Each time that an exchange happens, the instance numbers that represent the base and the clone objects change, which immediately changes the data contained in the base and clone tables and indexes. When the clone is dropped and an uneven number of EXCHANGE statements have been executed, the base table will have an *I0002.* data set name. This could be confusing.