Attributes of detached data partitions

When you detach a data partition from a partitioned table by using the DETACH PARTITION clause of the ALTER TABLE statement, the detached data partition becomes a stand-alone, nonpartitioned table.

Many attributes of the target table are inherited from the source table. Any attributes that are not inherited from the source table are set by using default values on the CREATE TABLE statement. If there is a partitioned index on the source table, that index is carried over to the target table. If the source table has a partitioned index with expression-based key parts, the system-generated statistical view and package are created and associated with the index in the target table.

The target table inherits all the partitioned indexes that are defined on the source table. These indexes include both system-generated indexes and user-defined indexes. The index object is not physically moved during the detach operation. The index partition metadata of the detached data partition is removed from the SYSINDEXPARTITIONS catalog table. Entries for the new table are added to the SYSINDEXES catalog table. The index identifier (IID) for a particular partitioned index from the source table is used as the IID for the index on the target table. The IID remains unique with respect to the table.

The index names for the surviving indexes on the new table are system generated in the form SQLyymmddhhmmssxxx. Path indexes, region indexes, and MDC or ITC indexes are made part of the SYSIBM schema. All other indexes are made part of the schema of the new table. System-generated indexes such as those to enforce unique and primary key constraints are made part of the schema of the new table because the indexes are carried over to the new table. You can use the RENAME statement to rename the indexes that are not in the SYSIBM schema.

Constraints on the source table are not inherited by the target table. After the detach operation, you can use the ALTER TABLE ... ADD CONSTRAINT statement on the new table to enforce the same constraints that are on the source table.

The table space location that is specified by the table-level INDEX IN clause on the source table is not inherited by the target table. Rather, the table space location that is specified by the partition-level INDEX IN clause or the default index table space for the new table is used as the index table space location for the new table.

Attributes that are inherited by the target table

Attributes that are inherited by the target table include:

  • The following column definitions:
    • Column name
    • Data type (includes length and precision for types that have length and precision, such as CHAR and DECIMAL)
    • Nullability
    • Column default values
    • INLINE LENGTH
    • Code page (CODEPAGE column of the SYSCAT.COLUMNS catalog view)
    • Logging for LOBs (LOGGED column of the SYSCAT.COLUMNS catalog view)
    • Compaction for LOBs (COMPACT column of the SYSCAT.COLUMNS catalog view)
    • Compression (COMPRESS column of the SYSCAT.COLUMNS catalog view)
    • Type of hidden column (HIDDEN column of the SYSCAT.COLUMNS catalog view)
    • Column order
  • If the source table is a multidimensional clustering (MDC) or insert time clustering (ITC) table, the target table is also an MDC or ITC table that is defined with the same dimension columns.
  • Block index definitions. The indexes are rebuilt on first access to the target table after the detach operation is committed.
  • The table space ID and table object ID are inherited from the data partition, not from the source table, because no table data is moved during a detach operation. The TBSPACEID column value in the SYSCAT.DATAPARTITIONS catalog view becomes the TBSPACEID column value in the SYSCAT.TABLES catalog view. The PARTITIONOBJECTID column value in the SYSCAT.DATAPARTITIONS catalog view becomes the TABLEID column value in the SYSCAT.TABLES catalog view.
  • The LONG_TBSPACEID column value in the SYSCAT.DATAPARTITIONS catalog view is translated into a table space name and becomes the LONG_TBSPACE column value in the SYSCAT.TABLES catalog view.
  • The INDEX_TBSPACEID column value in the SYSCAT.DATAPARTITIONS catalog view (the partition-level index table space) is translated into a table space name and becomes the INDEX_TBSPACE value in the SYSCAT.TABLES catalog view. The target table does not inherit the index table space that is specified by the table-level INDEX IN table space clause in the CREATE TABLE statement.
  • Table space location
  • ID of the distribution map for a multipartition database (PMAP_ID column of the SYSCAT.TABLES catalog view)
  • Percent free (PCTFREE column of the SYSCAT.TABLES catalog view)
  • Append mode (APPEND_MODE column of the SYSCAT.TABLES catalog view)
  • Preferred lock granularity (LOCKSIZE column of the SYSCAT.TABLES catalog view)
  • Data capture (DATA_CAPTURE column of the SYSCAT.TABLES catalog view)
  • VOLATILE (VOLATILE column of the SYSCAT.TABLES catalog view)
  • DROPRULE (DROPRULE column of the SYSCAT.TABLES catalog view)
  • Compression (COMPRESSION column of the SYSCAT.TABLES catalog view)
  • Maximum free space search (MAXFREESPACESEARCH column of the SYSCAT.TABLES catalog view)

Partitioned hierarchical or temporary tables, range-clustered tables, and partitioned views are not supported.

Attributes that are not inherited from the source table

Attributes that are not inherited from the source table include:

  • Target table type. The target table is always a regular table.
  • Privileges and authorities.
  • Schema.
  • Generated columns, identity columns, check constraints, and referential constraints. If a source column is a generated column or an identity column, the corresponding target column has no explicit default value, meaning that it has a default value of NULL.
  • Table-level index table space (INDEX_TBSPACE column of the SYSCAT.TABLES catalog view). Indexes for the table resulting from the detach operation are in the same table space as the table.
  • Triggers.
  • Primary key constraints and unique key constraints.
  • Statistics for nonpartitioned indexes.
  • All other attributes that are not mentioned in the list of attributes that are explicitly inherited from the source table.