Performance Enhancements

This release includes many features that help you monitor and improve performance.

Configurable Default Lock Modes

You can set the default lock mode to page or row for new tables in the following ways:

• LOCK MODE clause in the ALTER TABLE or CREATE TABLE statement
• IFX_DEF_TABLE_LOCKMODE environment variable
• DEF_TABLE_LOCKMODE configuration parameter

  Task	Manual
  Configure lock mode.	IBM Informix Performance Guide
  Use the DEF_TABLE_LOCKMODE configuration parameter.	IBM Informix Administrator's Reference
  Use the LOCK MODE clause in the ALTER TABLE or CREATE TABLE statement.	IBM Informix Guide to SQL: Syntax

The onstat -g stm Option

Use the onstat -g stm option to display the memory that prepared SQL statements use:

onstat -g stm session_id

For more information on onstat -g stm, see the IBM Informix Performance Guide and the IBM Informix Administrator's Reference.

The Ability to Display the Query Plan Without Executing the Query

To display the query plan without executing the query, use the SET EXPLAIN ON AVOID_EXECUTE statement or the AVOID_EXECUTE optimizer directive. This option allows you to evaluate the query plan that the optimizer has written to the sqexplain.out file.

To use this feature as a directive for a single statement:

SELECT --+EXPLAIN AVOID_DIRECTIVE
* FROM tablename;

To use this feature as a SET EXPLAIN keyword for a block of statements:

SET EXPLAIN ON AVOID_EXECUTE;

Dynamic Addition of Logical Logs

The database server automatically adds a logical-log file after the current log file when the next log file contains an open transaction. Dynamic log allocation prevents logs from filling and hanging the system during long-transaction rollbacks. You also can choose whether to add a log file manually after the current log file or at the end of the log file list.

The DYNAMIC_LOGS configuration parameter determines whether the database server allocates new logical-log files dynamically. The LTXHWM and LTXEHWM configuration parameters set high-watermarks for long transactions. If DYNAMIC_LOGS is set to 1 or 2, the default LTXHWM value is 80 percent and LTXEHWM is 90 percent.

The onstat -l output also displays information about temporary logical logs.