Disabling Deferred Close

For a DDL statement to execute without error even when another session holds a lock on the database object, the user must take the following actions:

1. Use the onutil SET command to set the DISABLE_FILE_CLOSE_OPTIMIZATION configuration parameter to 1.
2. In the DDL session, use SET ENVIRONMENT to set FORCE_DDL_EXEC to ON.
3. In the same DDL session, issue the SET LOCK MODE TO WAIT command, specifying no time limit (and omitting the NOT keyword).
4. Run the DDL statement.

In the third step, the SET LOCK MODE TO WAIT statement enables the DDL statement to wait indefinitely until any DML transactions on the same database objects are rolled back, thereby releasing their locks.

In the first step, the onutil SET DISABLE_FILE_CLOSE_OPTIMIZATION command disables a query optimization feature that closes certain files related to tables only after the query is committed or rolled back. Otherwise, when a DDL session of user informix aborts a reader session that uses a deferred close for a table that the DDL statement references, the open file causes the DDL operation to fail with error -335. Setting the DISABLE_FILE_CLOSE_OPTIMIZATION configuration parameter disables the deferred close feature, allowing the DDL to succeed.

The onutil SET command can reset this configuration parameter by using the following syntax:

                                               .-0-.
>>-onutil set DISABLE_FILE_CLOSE_OPTIMIZATION--+-1-+------------>
 
>--+---------------------+-------------------------------------><
   +-COSERVER-+-number-+-+
   |          '-name---' |
   +-PERSISTENT----------+
   '-PROPAGATE-----------'
 

Here number is the coserver number, and name is the coserver name.

The specifications that can follow the DISABLE_FILE_CLOSE_OPTIMIZATION keyword have the following effect:

• The value 1 disables the deferred close feature, avoiding error -355 when using the FORCE_DDL_EXEC feature.
• The default value 0 enables the deferred close feature.
• The COSERVER number or COSERVER name identifies the coserver for which the configuration parameter is being set.
• The PERSISTENT keyword keeps the parameter setting in effect after the current session ends.
• The PROPAGATE keyword applies the setting to all coservers.

If you provide no COSERVER or PERSISTENT specification, then by default the setting is global and temporary, meaning that it applies to all coservers until the Extended Parallel Server instance is shut down.

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

For a summary of changes to the user interface that are described in this document, including administrative utilities, session environment variables, configuration parameters, and SQL statement syntax, see Summary of User Interface Enhancements.