The FORCE_DDL_EXEC Session Environment Setting

Data definition language (DDL) statements of SQL that operate on database objects, such as ALTER FRAGMENT or TRUNCATE, fail with an error if a data manipulation language (DML) statement in another concurrent session holds a lock on an object that is referenced in the DDL statement.

To force execution of DDL statements, a new FORCE_DDL_EXEC session environment option to the SET ENVIRONMENT statement can abort all DML statements in other sessions that hold locks on database objects that DDL statements reference in the current session:

>>-SET ENVIRONMENT FORCE_DDL_EXEC--+-ON--------+---------------><
                                   +-OFF-------+
                                   +-DEFAULT---+
                                   +-MUTABLE---+
                                   '-IMMUTABLE-'
 

The keywords that can follow the FORCE_DDL_EXEC keyword have the following effect:

• ON enables this feature, so that subsequent DDL operations in the session release any locks that concurrent DML operations in other sessions have placed on objects that the DDL statement references. The locks are released by aborting those DML operations.
• DEFAULT sets the session environment to its default value, which is OFF.
• MUTABLE enables any user to reset the FORCE_DDL_EXEC session variable.
• IMMUTABLE prevents any user who is neither the DBA nor the owner of the database from executing the SET ENVIRONMENT FORCE_DDL_EXEC statement.

When ON is the value of this environment setting, the DDL operation aborts all sessions that holds locks on objects that the DDL references. The DDL operation waits for these sessions to rollback their transactions before beginning DDL execution.

The DBA can include the following statement in the sysdbopen( ) procedure:

   SET ENVIRONMENT FORCE_DDL_EXEC IMMUTABLE

After the mutability attribute of FORCE_DDL_EXEC is set to IMMUTABLE, only the DBA can change the mutability attribute of FORCE_DDL_EXEC.

The onstat utility can display information about the FORCE_DDL_EXEC setting:

• The onstat -g env session_id command prints the values of all session environment variables that are not set to the default, including the FORCE_DDL_EXEC variable.
• The onstat -g mut session_id command prints the mutability attribute of all session environment variables, including FORCE_DDL_EXEC, that are not set to the default.

For more information about command-line options of the onstat utility, see Appendix A. Syntax Summaries of Selected Utilities.