Forcing out transactions when altering table fragments

You can enable the server to force out transactions that have opened or hold locks on the target table of an ALTER FRAGMENT ON TABLE operation in a logging database. Users holding the DBA access privilege can do this by enabling the FORCE_DDL_EXEC session environment option of the SET ENVIRONMENT statement.

About this task

You might want to do this on a busy system, perhaps one that runs 24 hours a day, if you do not want to wait for sessions to close before you alter a fragment.

Be aware, however, that by forcing out concurrent transactions to avoid waiting for locks to be released, the database server closes the Update cursors and rolls back the transactions of other users.

Prerequisites:

You must be user informix or hold DBA access privileges on the database.
The table must be in a database that supports transaction logging.

Procedure

To force out concurrent transactions of other sessions when altering a table fragment:

Set the FORCE_DDL_EXEC environment option of the SET ENVIRONMENT statement to one of the following values:
- ON, on , '1', or "1" to enable the server to force out transactions that are open or have a lock on the table when an ALTER FRAGMENT ON TABLE statement is issued, until the server obtains a lock and exclusive access to the table.
- A positive integer that represents an amount of time in seconds. The numeric value enables the server to force out transactions until the server gets exclusive access and exclusive locks on the table, or until the specified time limit. If the server cannot force out transactions by the specified number of seconds, the server stops attempting to force out the transactions, and the ALTER FRAGMENT statement waits for the locks to be released when the concurrent transactions are committed or rolled back.
For example, to enable the FORCE_DDL_EXEC environment option to operate for 100 seconds when an ALTER FRAGMENT ON TABLE statement is issued, specify:
```
SET ENVIRONMENT FORCE_DDL_EXEC '100';
```
Set the lock mode to wait to ensure that the server will wait a specified amount of time before forcing out any transactions.
For example, to set the lock mode to wait for 20 seconds, specify:
```
SET LOCK MODE TO WAIT "20";
```
For more information, see Setting the lock mode to wait.
Run an ALTER FRAGMENT ON TABLE statement, for example, to attach, detach, modify, add, or drop the fragment.

Example

The following SQL statements perform these actions:

enable the FORCE_DDL_EXEC session environment option for 100 seconds,
set the database server to wait up to 25 seconds for locks to be released,
and change the interval size and storage location of range fragment p2 of table tabF:

SET ENVIRONMENT FORCE_DDL_EXEC '100';
SET LOCK MODE TO WAIT 25;
ALTER FRAGMENT ON TABLE tabF MODIFY 
   PARTITION p2 TO PARTITION p2 VALUES < 500 IN dbs0;

Attention:

While the ALTER FRAGMENT statement above is running, other transactions that attempt to access rows in table tabF are at risk of being forced out, if their Update cursor holds locks on rows in fragment p2.

After a transaction is rolled back because the FORCE_DDL_EXEC session environment option is enabled by another session, the database server returns this error to the session whose transaction failed:

-458  Long transaction aborted.

The concurrent transaction failing with error -458 was not necessarily "long," but it had not yet been committed after opening or holding locks on the same table that the ALTER FRAGMENT statement in this example was modifying.

What to do next

After you complete an ALTER FRAGMENT ON TABLE operation with the FORCE_DDL_EXEC session environment option enabled, you can turn the FORCE_DDL_EXEC session environment option off. For example, specify:

SET ENVIRONMENT FORCE_DDL_EXEC OFF;