Attributes of detached data partitions

When you detach a data partition from a partitioned table using the DETACH PARTITION clause of the ALTER TABLE statement, it becomes a stand-alone, nonpartitioned target table. Many attributes of the new target table are inherited from the source table. Any attributes not inherited from the source table are set as if the user executing the DETACH operation is creating the target table.

The target table after DETACH will inherit all the partitioned indexes defined on the source table. These indexes includes both system-generated indexes or user-defined indexes. The index object is not physically moved during the detach operation. The index partition metadata of the datapartition being detached is removed from the SYSINDEXPARTITIONS catalog. New entries are added in SYSINDEXES for the new table. The index identifier (IID) for any given partitioned index from the source table will be the IID for the index on the target table (the IID will remain unique with respect to the table, and unchanged during the detach).

The index name for the surviving indexes on the new table are system-generated with the form: SQLyymmddhhmmssxxx. Path indexes, region indexes, and MDC block indexes are made part of the SYSIBM schema. All other indexes are made part of the schema of the new table. System-generated indexes like 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. Constraints on the source table will not be inherited by the target table after DETACH.

You can use the RENAME statement to rename the indexes not in the SYSIBM schema at another time.

You can use the ALTER TABLE ... ADD CONSTRAINT statement on the new table following the completion of the detach operation to enforce the same constraints on the new table as on the source table.

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

Attributes inherited by the target table

Attributes 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 SYSCAT.COLUMNS catalog view)
  • Logging for LOBs (LOGGED column of SYSCAT.COLUMNS catalog view)
  • Compaction for LOBs (COMPACT column of SYSCAT.COLUMNS catalog view)
  • Compression (COMPRESS column of SYSCAT.COLUMNS catalog view)
  • Type of hidden column (HIDDEN column of SYSCAT.COLUMNS catalog view)
  • Column order
• If the source table is a multidimensional clustering table (MDC), the target table is also an MDC table, defined with the same dimension columns. Access to the newly detached table is not allowed in the same unit of work as the detach when the source table is MDC.
• Block index definitions. The indexes are rebuilt on first access to the newly detached independent 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. This is because no table data is moved during a DETACH operation. In catalog terms, the TBSPACEID column of the SYSCAT.DATAPARTITIONS catalog view from the source data partition becomes the TBSPACEID column of the SYSCAT.TABLES catalog view. When translated into a table space name, it is the TBSPACE column of SYSCAT.TABLES catalog view in the target table. The PARTITIONOBJECTID column of the SYSCAT.DATAPARTITIONS catalog view from the source data partition becomes the TABLEID column of the SYSCAT.TABLES catalog view in the target table.
• The LONG_TBSPACEID column of the SYSCAT.DATAPARTITIONS catalog view from the source data partition is translated into a table space name and becomes the LONG_TBSPACE column of SYSCAT.TABLES of the target table.
• The INDEX_TBSPACEID column value in the SYSDATAPARTITIONS for the source data partition (the partition level index table space) is translated into a table space name and becomes the INDEX_TBSPACE value in SYSTABLES for the target table. The index table space specified by table level INDEX IN <table space> in the CREATE TABLE statement will not be inherited by the target table.
• Table space location
• ID of distribution map for a multi-partition database (PMAP_ID column of SYSCAT.TABLES catalog view)
• Percent free (PCTFREE column of SYSCAT.TABLES catalog view)
• Append mode (APPEND_MODE column of SYSCAT.TABLES catalog view)
• Preferred lock granularity (LOCKSIZE column of SYSCAT.TABLES catalog view)
• Data Capture (DATA_CAPTURE column of SYSCAT.TABLES catalog view)
• VOLATILE (VOLATILE column of SYSCAT.TABLES catalog view)
• DROPRULE (DROPRULE column of SYSCAT.TABLES catalog view)
• Compression (COMPRESSION column of SYSCAT.TABLES catalog view)
• Maximum free space search (MAXFREESPACESEARCH column of SYSCAT.TABLES catalog view)

Attributes not inherited from the source table

Attributes not inherited from the source table include:

• The target table type is not inherited. The target table is always a regular table.
• Privileges and Authorities
• Schema
• Generated columns, identity columns, check constraints, referential constraints. In the case where a source column is a generated column or an identity column, the corresponding target column has no explicit default value, meaning 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 will be in the same table space as the table.
• Triggers
• Primary key constraints and unique key constraints
• Statistics for nonpartitioned indexes will not be inherited.
• All other attributes not mentioned in the list of attributes explicitly inherited from the source table.