Using the ONLINE keyword in MODIFY operations

The ONLINE keyword instructs the database server to commit the ALTER FRAGMENT . . . MODIFY work internally, if there are no errors, and to apply an intent exclusive lock to the table, rather than an exclusive lock.

Requirements for ONLINE MODIFY operations

You can use the MODIFY option to the ALTER FRAGMENT ONLINE ON TABLE statement only for a table that is fragmented by a range interval fragmentation scheme.

Only the transition value (the starting value for interval fragments) can be modified ONLINE. All other restrictions that apply to the MODIFY option also apply to ONLINE MODIFY operations. For those restrictions, see General Restrictions for the ATTACH Clause and Restrictions on the MODIFY clause for range interval fragments.

Example of ALTER FRAGMENT ONLINE . . . MODIFY

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);

INSERT INTO employee VALUES (401, "Susan", "DV", 101, "123-45-6789");
INSERT INTO employee VALUES (601, "David", "QA", 104, "987-65-4321");

The last two statements insert rows with fragment key values above the upper limit of the transition fragment, causing the database server to generate two new interval fragments, so that the fragment list consists of four fragments:

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 following statement returns an error because a transition value can only be increased. This is also a restriction for offline ALTER FRAGMENT . . . MODIFY operations.

ALTER FRAGMENT ONLINE ON TABLE employee 
   MODIFY INTERVAL TRANSITION TO 300;

The following statement runs successfully:

ALTER FRAGMENT ONLINE ON TABLE employee MODIFY INTERVAL TRANSITION TO 600;

Fragments in surviving table after ALTER FRAGMENT ONLINE:
p0       VALUES < 200                   - range fragment
p1       VALUES < 400                   - range fragment 
sys_p2rg VALUES < 600                   - range fragment (new transition fragment)
sys_p3   VALUES >= 600 AND VALUES < 700 - interval fragment

The following examples are also valid:

ALTER FRAGMENT ONLINE ON TABLE employee MODIFY INTERVAL TRANSITION TO 700;
ALTER FRAGMENT ONLINE ON TABLE employee MODIFY INTERVAL TRANSITION TO 900;