The ONLINE keyword in ALTER FRAGMENT operations

The ONLINE keyword instructs the database server to modify the storage of the table in the background, while other concurrent users can continue to access the table.

The DBA can reduce the risk of nonexclusive access errors, and can increase the availability of the fragmented table, by including the ONLINE keyword in the ALTER FRAGMENT statement. This instructs the database server to commit the work in ATTACH, DETACH, and MODIFY operations internally, if there are no errors, and to apply an intent exclusive lock to the table, rather than an exclusive lock.

In DETACH and MODIFY operations, the ONLINE keyword can reduce the risk of -710 errors when either of the following is true:
  • the AUTO_REPREPARE configuration parameter is set to 1,
  • the IFX_ AUTO_REPREPARE session environment variable is set to 1.
The following restrictions apply to the ALTER FRAGMENT ONLINE FOR TABLE statement:
  • Only the ATTACH, DETACH, and MODIFY options to ALTER FRAGMENT ONLINE are valid.
  • The FOR TABLE clause must specify a table that is fragmented by a range interval scheme.
  • The table that is being altered cannot be locked explicitly by the LOCK TABLE statement.
  • The ALTER FRAGMENT ONLINE operation must be the first statement in the transaction that modifies any database object or table.
  • No other operation that modifies an object in the database can follow the ALTER FRAGMENT ONLINE statement in the same transaction.