Using the ONLINE keyword in ATTACH operations

Requirements for ONLINE ATTACH operations

You can use the ATTACH option to the ALTER FRAGMENT ONLINE ON TABLE statement only if the surviving table is fragmented by an interval fragmentation scheme. The consumed table must be nonfragmented.

All indexes on the surviving table must have the same fragmentation scheme as the table. (That is, any indexes must be attached.) For this reason, if there is a primary key constraint or other referential constraints on the table, it is recommended that you first create an attached index for the constraint, and then use the ALTER TABLE statement to add the constraint. (By default, system-created indexes for primary key constraints and for other referential constraints are detached.)

For each index on the surviving table, there must be a matching index on the same set of columns of the consumed table. The matching indexes on the consumed table will be recycled as index fragments on the surviving table during the ATTACH operation. Any additional indexes on the consumed table are dropped during the ATTACH operation. The indexes on the consumed table that will be recycled must each be detached in a single dbspace, and the dbspace that stores the recycled index must be same dbspace that stores the consumed table.

If the index on the surviving table is unique, the corresponding matching index on the consumed table must also be unique.

This last requirement, that rows in the consumed table span only a single interval within the range interval fragmentation scheme of the surviving table, is necessary to prevent data movement. Data movement is not allowed in ALTER FRAGMENT ATTACH operations that include the ONLINE keyword.

Only one consumed table can be specified in the ONLINE ATTACH operation.

All other restrictions that apply to the ATTACH option also apply to ONLINE ATTACH operations. For those restrictions, see General Restrictions for the ATTACH Clause and Additional Restrictions on the ATTACH Clause.

Example of ALTER FRAGMENT ONLINE ATTACH

The following SQL statements define a fragmented employee table that uses a range-interval storage distribution scheme, with a unique index employee_id_idx on the column emp_id (that is also the fragmentation key) and another index employee_dept_idx on the column dept_id.

CREATE TABLE employee 
     (emp_id INTEGER, name CHAR(32), 
      dept_id CHAR(2), mgr_id INTEGER, ssn CHAR(12))
  FRAGMENT BY RANGE (emp_id) 
    INTERVAL (100) STORE IN (dbs1, dbs2, dbs3, dbs4)
      PARTITION p0 VALUES < 200 IN dbs1,
      PARTITION p1 VALUES < 400 IN dbs2;
CREATE UNIQUE INDEX employee_id_idx ON employee(emp_id);
CREATE INDEX employee_dept_idx ON employee(dept_id);

Fragments in surviving table before ALTER FRAGMENT ONLINE:
p0     VALUES < 200                   - range fragment
p1     VALUES < 400                   - range fragment (transition fragment)
sys_p2 VALUES >= 400 AND VALUES < 500 - interval fragment
sys_p4 VALUES >= 600 AND VALUES < 700 - interval fragment

The next SQL statements define a nonfragmented employee2 table with the same column schema as the employee table, and with single-column indexes on the two corresponding columns (emp_id and dept_id) that were indexed in the employee table, but also defines a unique index employee2_ssn_idx on the column emp_ssn and another index employee_dept_idx on the column name. All four of these indexes are stored in the dbspace dbs4. The CREATE TABLE statement also specifies a check constraint ((emp_id >=500 AND emp_id <600)) that exactly matches the fragment expression for a consumed table that will be attached, and that exactly spans a single interval of the range interval fragmentation scheme for the employee table.

CREATE TABLE employee2 
     (emp_id INTEGER, name CHAR(32), 
      dept_id  CHAR(2), mgr_id INTEGER, ssn CHAR(12), 
  CHECK (emp_id >=500 AND emp_id <600)) in dbs4;
CREATE UNIQUE INDEX employee2_id_idx ON employee2(emp_id) in dbs4;
CREATE INDEX employee2_dept_idx ON employee2(dept_id) in dbs4;
CREATE UNIQUE INDEX employee2_ssn_idx ON employee2(ssn) in dbs4;
CREATE INDEX employee2_name_idx ON employee2(name) in dbs4;

The following statement returns an error because the fragment being attached is a range fragment (a fragment that stores rows with fragmentation key values below the transition value of 400 for the employee table). Only interval fragments can be attached online.

ALTER FRAGMENT ONLINE ON TABLE employee 
   ATTACH employee2 AS PARTITION p3 VALUES < 300;

The following statement runs successfully, and creates a new p3 interval fragment:

ALTER FRAGMENT ONLINE ON TABLE employee 
   ATTACH employee2 AS PARTITION p3 VALUES < 600;

Fragments in surviving table after ALTER FRAGMENT ONLINE:
p0     VALUES < 200                   - range fragment
p1     VALUES < 400                   - range fragment
sys_p2 VALUES >= 400 AND VALUES < 500 - interval fragment
sys_p3 VALUES >= 500 AND VALUES < 600 - interval fragment
sys_p4 VALUES >= 600 AND VALUES < 700 - interval fragment

Note that the successful ALTER FRAGMENT ONLINE . . . ATTACH operation above required multiple correspondences among specifications in the DDL statements that defined the surviving and consumed tables, including their columns, indexes, constraints, index storage location, and the interval fragmentation strategy of the surviving table:

• The check constraint on the consumed table spans a single interval only. The interval value is 100 for the surviving table, and the check constraint is >= 500 and < 600.
• The conditional expression being attached (< 600) is internally converted to the interval fragment expression format (>= 500 and < 600) which matches the check constraint.
• The indexes on the surviving table are attached (that is, they are fragmented by the same fragmentation scheme as the table) because no fragmentation strategy was specified explicitly in their CREATE INDEX statements.
• The indexes on the consumed table are detached in a single dbspace (dbs4), which is the same dbspace that stored the consumed table.
• For each index on the surviving table, there is a matching index on the consumed table.
• The extra indexes on the consumed table (employee2_ssn_idx and employee2_name_idx) that do not correspond to indexes on the surviving employee table are dropped during the ONLINE ATTACH operation.