Forcing the Execution of DDL Operations

For Version 8.50 and earlier XPS releases, data definition language (DDL) statements of SQL that operate on database objects, such as ALTER TABLE or TRUNCATE, fail with an error if a data manipulation language (DML) statement in a concurrent session holds a lock on an object that the DDL statement references. This can interfere with maintenance operations by the DBSA when the database server is in on-line mode.

To force execution of DDL statements, a new 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 | IMMUTABLE | MUTABLE ]

The onstat utility can display information about the FORCE_DDL_EXEC setting:

• The onstat -g env session_id command prints the value 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.

FORCE_DDL_EXEC allows concurrent sessions to execute SQL statements that do not hold locks on the same objects that the DDL statements reference.

For more information about the FORCE_DDL_EXEC feature, see Forcing DDL Execution.