FORCE_DDL_EXEC session environment option

These are prerequisites for enabling the FORCE_DDL_EXEC session environment option:

• You must be user informix or have DBA privileges on the database.
• The database must be a logging database.

FORCE_DDL_EXEC session environment option has this syntax:

1  SET ENVIRONMENT FORCE_DDL_EXEC
2.2.1 OFF
2.2.1 '0'
2.2.1 ON
2.2.1 '1'
2.1  ' seconds '

Usage

The FORCE_DDL_EXEC option can have any of the following values:

ON or '1'

This enables 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 gets a lock and exclusive access on the table.

'seconds'

Specifies a time interval, in units of seconds, to allow the server to force out transactions that are open or that hold a lock on the target table of an ALTER FRAGMENT ON TABLE statement, until the server obtains a lock and exclusive access on the table, or until the specified time limit occurs. If the server cannot force out transactions within the specified time interval, 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.

OFF or '0'

Prevent the database server from forcing out transactions that are open or have a lock on the table when an ALTER FRAGMENT ON TABLE statement is issued. (This is the default behavior, unless a previous SET ENVIRONMENT FORCE_DDL_EXEC statement in the same session has enabled forcing out transactions during ALTER FRAGMENT ON TABLE statements.)

You must delimit the seconds, 1, or 0 setting by single ( ' ) or double ( " ) quotation marks. The ON and OFF keywords are case insensitive.

Important: When you use the FORCE_DDL_EXEC environment option, also use the SET LOCK MODE TO WAIT statement to specify a period of time for the server to force out any transactions in order to get exclusive access and a lock. If you run SET LOCK MODE TO WAIT without specifying an amount of time, the FORCE_DDL_EXEC option will not impact the ALTER FRAGMENT operation. For more information, see the SET LOCK MODE statement.

When you enable the FORCE_DDL_EXEC environment option, the server supports multiple sessions performing ALTER FRAGMENT ON TABLE operations. If two sessions perform ALTER FRAGMENT ON TABLE on the same table when the FORCE_DDL_EXEC option is enabled, the second session receives an error. If another ALTER operation is occurring on the table, the ALTER FRAGMENT ON TABLE operation with an enabled FORCE_DDL_EXEC environment option will get an error.

Enabling this feature in sessions that issue ALTER FRAGMENT ON TABLE statements can avoid waiting for locks to be released. Effects on applications in other sessions where DDL statements access the same tables, however, can include closing their Update cursors, and rolling back their uncommitted transactions.

SET ENVIRONMENT FORCE_DDL_EXEC '0';

The onshowaudit utility displays an ALTER FRAGMENT event code (ALFR), which identifies ALTER FRAGMENT events that ran while the FORCE_DDL_EXEC environment option was enabled.

When the FORCE_DDL_EXEC environment option is enabled, the server also closes the hold cursors during rollback by the session that performs the ALTER FRAGMENT ON TABLE operation.

Examples of setting FORCE_DDL_EXEC

-458  Long transaction aborted.

SET ENVIRONMENT FORCE_DDL_EXEC '240';
SET LOCK MODE TO WAIT 60;
ALTER FRAGMENT ON TABLE window_orders MODIFY INTERVAL 
   LIMIT TO 10 MiB DETACH INTERVAL ONLY; 
SET ENVIRONMENT FORCE_DDL_EXEC OFF;

SET ENVIRONMENT FORCE_DDL_EXEC '240';
SET LOCK MODE TO WAIT;

SET ENVIRONMENT FORCE_DDL_EXEC '1';