FORCE_DDL_EXEC session environment option

Use the FORCE_DDL_EXEC session environment option of the SET ENVIRONMENT statement to force out other transactions that have opened or have locks on the tables involved in an ALTER FRAGMENT ON TABLE operation.

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:

Read syntax diagramSkip visual syntax diagram
FORCE_DDL_EXEC session environment option

|--SET ENVIRONMENT FORCE_DDL_EXEC--+-+-OFF-+-------+------------|
                                   | '-'0'-'       |   
                                   +-+-ON--+-------+   
                                   | '-'1'-'       |   
                                   '-'--seconds--'-'   

Element Description Restrictions Syntax
seconds Nonnegative integer, setting a limit in seconds on the time available for the server to force out transactions and obtain exclusive access to the table Must be an integer greater than zero Quoted String

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.

After you complete an ALTER FRAGMENT ON TABLE operation with the FORCE_DDL_EXEC environment option enabled, you can run the following statement to disable FORCE_DDL_EXEC environment option:
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

Unlike most session environment options of the SET ENVIRONMENT statement, the effects of enabling the FORCE_DDL_EXEC option in a logged database can directly affect DDL operations on the same table in concurrent sessions,
  • by requiring their transaction to commit within your SET LOCK MODE TO WAIT interval time limit,
  • or by rolling back their transaction, if that provides you with exclusive access to the table within the seconds value of your SET ENVIRONMENT FORCE_DDL_EXEC statement, or when the FORCE_DDL_EXEC option is enabled with no time limit.
If the database server succeeds in rolling back a transaction in the other session, it issues the following error, regardless of the duration of the forced-out transaction:
-458  Long transaction aborted.
For example, the following SQL statements take these actions:
  • Enable FORCE_DDL_EXEC for 240 seconds
  • Wait up to 60 seconds for locks held by other sessions to be released
  • Change the Rolling Window purge policy of the window_orders table
  • Disable the FORCE_DDL_EXEC environment option.
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;
But if the SET LOCK MODE statement above had specified no limit
SET ENVIRONMENT FORCE_DDL_EXEC '240';
SET LOCK MODE TO WAIT;
then the FORCE_DDL_EXEC setting would have no effect, because the database server would have waited until no concurrent transactions held locks on the window_orders table, which is the default behavior without FORCE_DDL_EXEC set.
The following statement enables the FORCE_DDL_EXEC environment option with no time limit during ALTER FRAGMENT operations:
SET ENVIRONMENT FORCE_DDL_EXEC '1';

Copyright© 2018 HCL Technologies Limited