UTILITY MODE pragma

The UTILITY MODE pragma determines whether transaction logging is effective in the SQL statement in which this pragma is specified. Transaction logging makes it possible to roll a transaction back to a consistent state.

The UTILITY MODE pragma is only effective in the SQL statement ALTER TABLE:

It only works if the ALTER TABLE statement adds, changes or deletes columns in a base table. In an ALTER TABLE statement which adds or deletes integrity constraints, the UTILITY MODE pragma has no effect.

---

UTILITY MODE [ON | OFF]

---

ON

Transaction logging is deactivated during the execution of the SQL statement. The associated ALTER TABLE statement does not open a transaction. No save data for the ALTER TABLE statement is stored. If an error occurs which results in an interruption of the statement, the transaction cannot be rolled back to a consistent state. When an error occurs, the space containing the base table is damaged and must be repaired using the RECOVER utility statement (see the “ SQL Reference Manual Part 2: Utilities”).

OFF

The pragma has no effect. The transaction logging remains active.

An ALTER TABLE statement, for which the UTILITY MODE pragma is switched ON and is effective, is aborted with an error message in the following cases:

• when a transaction is active

• when the ALTER TABLE statement deletes a column, i.e. using DROP COLUMN column CASCADE

• when the ALTER TABLE statement deletes a column and an index for this column is still defined

• when the ALTER TABLE statement adds a column with an index definition for this column

If no UTILITY MODE pragma is specified for an ALTER TABLE statement then the default setting, UTILITY MODE OFF, is effective.

CAUTION! If you use the UTILITY MODE ON pragma then, after an error or consistency check, the space containing the base table to be changed is defective. To avoid data loss, you should save the space before issuing the ALTER TABLE statement. The save is necessary if you want to use the utility statement RECOVER to repair it.