DB2 Version 9.7 for Linux, UNIX, and Windows

Attaching data partitions

Table partitioning allows for the efficient roll-in and roll-out of table data. The ALTER TABLE statement with the ATTACH PARTITION clause makes data roll-in very easy. It takes an existing table (source table) and attaches it to the target table as a new data partition. The newly attached data partition is unavailable to queries initially after the attach statement is complete, while the rest of the table remains online after attach. SET INTEGRITY is required to perform range checking, constraint checking, and maintenance of the nonpartitioned indexes before bringing the partition online.

About this task

Restrictions and usage guidelines

The following conditions must be met before you can attach a data partition:

The table to which you want to attach the new data partition (that is, the target table) must be an existing partitioned table.
The source table must be an existing nonpartitioned table or a partitioned table with a single data partition and no attached data partitions or detached data partitions. To attach multiple data partitions, it is necessary to issue multiple ATTACH statements.
The source table cannot be a typed table.
The source table cannot be a range-clustered table.
The source and target table definitions must match.
The number, type, and ordering of source and target columns must match.
Source and target table columns must match in terms of whether they contain default values.
Source and target table columns must match in terms of whether they allow null values.
Source and target table compression specifications, including the VALUE COMPRESSION and COMPRESS SYSTEM DEFAULT clauses, must match.
Source and target table specifications for the DATA CAPTURE, ACTIVATE NOT LOGGED INITIALLY, and APPEND options must match.
Attaching a data partition is allowed even when a target column is a generated column and the corresponding source column is not a generated column. The following statement generates the values for the generated column of the attached rows:
```
SET INTEGRITY FOR table-name
  ALLOW WRITE ACCESS
  IMMEDIATE CHECKED FORCE GENERATED
```
The source table column matching a generated column must match in type and nullability; however, a default value is not required. The recommended approach is to guarantee that the source table for the attach operation has the correct generated value in the generated column. If this is the case, you are not required to use the FORCE GENERATED option, and the following statements can be used.
```
SET INTEGRITY FOR table-name
  GENERATED COLUMN
  IMMEDIATE UNCHECKED
```
This statement indicates that checking of the generated column is to be bypassed.
```
SET INTEGRITY FOR table-name
  ALLOW WRITE ACCESS
  IMMEDIATE CHECKED
  FOR EXCEPTION IN table-name USE table-name
```
This statement performs integrity checking of the attached data partition but does not check the generated column.
Attaching a data partition is allowed even when the target column is an identity column and the source column is not an identity column. The statement SET INTEGRITY IMMEDIATE CHECKED does not generate identity values for the attached rows. The statement SET INTEGRITY FOR T GENERATE IDENTITY ALLOW WRITE ACCESS IMMEDIATE CHECKED fills in the identity values for the attached rows. The column matching an identity column must match in type and nullability. There is no requirement on the default values of this column. The recommended approach is for you to fill in the correct identity values at the staging table. Then after the ATTACH, there is no requirement to use the GENERATE IDENTITY option because the identity values are already guaranteed in the source table.
For tables whose data is distributed across database partitions, the source table must also be distributed, in the same database partition group using the same distribution key and the same distribution map.
The source table must be droppable (that is, it cannot have RESTRICT DROP set).
If a DATAPARTITIONNAME is specified, it must not already exist in the target table.
If the target table is an multidimensional clustering (MDC) table, the source table must also be an MDC table.
Using a nonpartitioned table, the data table space for the source table must match the data table spaces for the target table in type (that is, DMS or SMS), pageSize, extentSize and database partition group. A warning is returned to the user if the prefetchSize does not match. The index table space for the source table must match the index table spaces used by the partitioned indexes for the target table in type, database partition group and pageSize, extentSize. The large table space for the source table must match the large table spaces for the target table in type, database partition group and pageSize. Using a partitioned table, the data table space for the source table must match the data table spaces for the target table in type, pageSize, extentSize and database partition group.
When you issue the ALTER TABLE ATTACH statement to a partitioned table with any structured, XML, or LOB columns, the INLINE LENGTH of any structured, XML, or LOB columns on the source table must match with the INLINE LENGTH of the corresponding structured, XML, or LOB columns on the target table.
Partitioned indexes over XML data cannot be unique.
When the REQUIRE MATCHING INDEXES clause is used with the ATTACH PARTITION clause, if there are any partitioned indexes on the target table that do not have a match on the source table, SQL20307N is returned.
Attaching a source table that does not have a matching index for each partitioned unique index on the target table causes the attach operation to fail with error SQL20307N, reason code 17.
When a table has a deferred index cleanup operation in progress as the result of an MDC rollout, since MDC rollout using the deferred index cleanup mechanism is not supported for partitioned indexes, the attach operation will not be allowed if there are any RID indexes on the source table that will be kept during the attach operation, not rebuilt, and are pending asynchronous index cleanup of the rolled-out blocks.
Attaching a source table with an XML data format that is different from that of the target table will not be supported.
If a table contains XML columns that use the Version 9.5 or earlier XML record format, attaching the table to a partitioned table that contains XML columns that use the Version 9.7 or later record format is not supported.
Before attaching the table, you must update the XML record format of the table to match the record format of the target partitioned table. Either of the following two methods update the XML record format of a table:
- Perform an online table move on the table using the ADMIN_MOVE_TABLE procedure.
- Perform the following steps:
  1. Use the EXPORT command to create a copy of the table data.
  2. Use the TRUNCATE statement to delete all the rows from the table and release the storage allocated to the table.
  3. Use the LOAD command to add the data into the table.
After the XML record format of the table has been updated, attach the table to the target partitioned table.

Before running the attach operation, create indexes on the source table that match each of the partitioned indexes in the target table. Matching the partitioned indexes makes the roll-in operation more efficient and less active log space is needed. If the indexes on the source table are not properly prepared, the database manager will be require to maintain them for you. To ensure your roll-in does not incur any additional cost to maintain the partitioned indexes you can specify REQUIRE MATCHING INDEXES on the attach partition operation. This will ensure the attach operation fails in the event that source table does not have indexes to match the partitioned indexes on the target. You may then take the corrective action and reissue the attach operation.

In addition, drop any extra indexes on the source table before running the attach operation. Extra indexes are those on the source table that either do not have a match on the target table, or that match nonpartitioned indexes on the target table. Dropping extra indexes before running the attach operation makes it run faster.

For example, assume that a partitioned table called ORDERS has 12 data partitions (one for each month of the year). At the end of each month, a separate table called NEWORDERS is attached to the partitioned ORDERS table.

Create partitioned indexes on the ORDERS table.

CREATE INDEX idx_delivery_date ON orders(delivery) PARTITIONED
CREATE INDEX idx_order_price ON orders(price) PARTITIONED

Prepare for the attach operation by creating the corresponding indexes on the NEWORDERS table.

CREATE INDEX idx_delivery_date_for_attach ON neworders(delivery)
CREATE INDEX idx_order_price_for_attach ON neworders(price)

There are two steps to the attach operation:
1. ATTACH. The indexes on the NEWORDERS table that match the partitioned indexes on the ORDERS table are kept.
```
ALTER TABLE orders ATTACH PARTITION part_jan2009
   STARTING FROM ('01/01/2009')
   ENDING AT ('01/31/2009') FROM TABLE neworders
```
  The ORDERS table is automatically placed into the Set Integrity Pending state. Both the idx_delivery_date_for_attach and idx_order_price_for _attach become part of the ORDERS table after the completion of the attach operation. No data movement occurs during this operation.
2. SET INTEGRITY. A range check is done on the newly attached partition. Any constraints that exist are enforced. Upon completion, the newly attached data becomes visible within the database.
```
SET INTEGRITY FOR orders IMMEDIATE CHECKED
```

When nonpartitioned indexes exist on the target table, SET INTEGRITY will have to perform the index maintenance work along with other tasks such as range validation and constraint check on the data from the newly attached partition. The nonpartitioned index maintenance requires large amount of active log space which is proportional to the data volumes in the newly attached partition, the key size of each nonpartitioned index and the number of the nonpartitioned indexes.

Each partitioned index on the new data partition is given an entry in SYSINDEXPARTITIONS using the table space identifier and object identifier from the source table. The identifier information is taken from either SYSINDEXES (if the table is nonpartitioned) or SYSINDEXPARTITIONS (if the table is partitioned). The index identifier is taken from the partitioned index of the matching target table.

When the source table is partitioned, those partitioned indexes on the source table that match the partitioned indexes on the target table are kept as part of the attach operation. SYSINDEXPARTITIONS index partition entries are updated to show that they are index partitions on the new target table with new index identifiers.

When attaching data partitions, some statistics for indexes as well as data are carried over from the source table to the target table for the new partition. Specifically, all fields in SYSDATAPARTITIONS and SYSINDEXPARTITIONS for the new partition on the target are populated from the source. When the source table is nonpartitioned, these statistics come from SYSTABLES and SYSINDEXES. When the source table is a single-partition partitioned table, these statistics come from SYSDATAPARTITIONS and SYSINDEXPARTITIONS of the single source partition.

Note: Execute a runstats operation after the completion of an attach operation, because the statistics that are carried over will not affect the aggregated statistics in SYSINDEXES and SYSTABLES.

Before you begin

To alter a table to attach a data partition, the privileges held by the authorization ID of the statement must include at least one of the following authorities or privileges on the source table:

SELECT privilege on the table and DROPIN privilege on the schema of the table
CONTROL privilege on the table
DATAACCESS authority