Avoid table locking with the ONLINE keyword

For indexes that are not defined with the IN TABLE keyword option, you can minimize the duration of an exclusive lock on the indexed table when you CREATE or DROP an index using the ONLINE keyword.

While the index is being created or dropped online, no DDL operations on the table are supported, but operations that were concurrent when the CREATE INDEX or DROP INDEX statement was issued can be completed. The specified index is not created or dropped until no other processes are concurrently accessing the table. Then locks are held briefly to write the system catalog data associated with the index. This increases the availability of the system, because the table is still readable by ongoing and new sessions. The following statement shows how to use the ONLINE keyword to avoid automatic table locking with a CREATE INDEX statement:

CREATE INDEX idx_1 ON customer (lname) ONLINE;

For in-table indexes, however, that are defined with the IN TABLE keyword option, the indexed table remains locked for the duration of the CREATE INDEX or DROP INDEX operation that includes the ONLINE keyword. Attempted access by other sessions to the locked table would fail with at least one of these errors:

107: ISAM error:  record is locked.
211: Cannot read system catalog (systables).
710: Table (table.tix) has been dropped, altered or renamed.