STMT_CACHE

onconfig.std value

None (this parameter is not in onconfig.std)

if not present

0

range of values

0, 1, or 2

takes effect

When the database server is shut down and restarted

utilities

onmode -e

refer to

The following material:

• Change Usage of the SQL Statement Cache
• Improving query performance, in the IBM Informix Performance Guide

STMT_CACHE determines whether the database server uses the SQL statement cache. You can enable the SQL statement cache in one of two modes:

• Always use the SQL statement cache unless a user explicitly specifies not to use it. Set the STMT_CACHE configuration parameter to 2 or onmode -e ON.
• Use the SQL statement cache only when a user explicitly specifies to use it. Set the STMT_CACHE configuration parameter to 1 or onmode -e ENABLE.

The following table describes the possible values.

Possible Value

Meaning

0

SQL statement cache not used (equivalent to onmode -e OFF).

1

SQL statement cache enabled, but user sessions do not use the cache. Users use the cache only if they set the environment variable STMT_CACHE to 1 or execute the SQL statement SET STATEMENT CACHE ON.

2

SQL statement cache turned on. All statements are cached. To turn off statement caching, set the environment variable STMT_CACHE to 0 or execute the SQL statement SET STATEMENT CACHE OFF.