Creating a clone table

You can create a clone table on an existing base table at the current server by using the ALTER TABLE statement.

Before you begin

Although the 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 for the clone table will be the same as for the base table.

The base table must meet the following requirements:

  • Be a universal table space
  • Reside in a DB2®-managed table space
  • Be the only table in the table space
  • Not already have a clone table defined on it
  • Not be part of any referential constraints
  • Not be defined with any AFTER triggers
  • Not be a materialized query table
  • Not be a created global temporary table or a declared global temporary table
  • Not have any data sets that still need to be created. For example, you cannot create a clone table on a base table that resides in a table space that was created by using the DEFINE NO option and that has VSAM data sets that still need to be created.
  • Start of changeNot have any pending alterations or in-use table space versions or index versions. The values in the OLDEST_VERSION and CURRENT_VERSION columns of the SYSIBM.SYSTABLESPACE or SYSIBM.SYSINDEXES tables must be identical.End of change
  • Not have an incomplete definition

About this task

Restrictions: In addition, the following restrictions apply to clone tables:
  • A clone table uses the statistics from the base table. RUNSTATS does not collect statistics on a clone table, and Access Path Selection (APS) does not use RUNSTATS statistics when accessing a clone table. This is in contrast to real-time statistics, which keeps statistics for both the base and clone objects. Start of changeAlso, autonomic statistics are not collected on a clone table.End of change
  • Catalog and directory tables cannot be cloned.
  • Indexes cannot be created on a clone table. Indexes can be created on the base table but not on the clone table. Indexes that are created on the base table apply to both the base and clone tables.
  • BEFORE triggers can be created on the base table but not on the clone table. BEFORE triggers that are created on the base table apply to both the base and clone tables.
  • You cannot rename a base table that has a clone relationship.
  • You cannot clone an RTS table.
  • You cannot drop an AUX table or an AUX index on an object that is involved in cloning.
  • You cannot alter any table, or column attributes of a base table or clone table when the objects are involved with cloning.
  • The maximum number of partitions cannot be altered when a clone table resides in a partition-by-growth table space.

Procedure

Begin general-use programming interface information.To create a clone table:

Issue the ALTER TABLE statement with the ADD CLONE option.

Creating or dropping a clone table does not impact applications that are accessing base table data. Start of changeNo base object quiesce is necessary, and this process does not invalidate packages or the dynamic statement cache.End of change

Example

The following example shows how to create a clone table by issuing the ALTER TABLE statement with the ADD CLONE option:
ALTER TABLE base-table-name ADD CLONE clone-table-name
End general-use programming interface information.