StmtCache – PB Docs 2021

Description

Specifies whether SQL statement caching is enabled on the
client.

Applies to

I10 Informix

Syntax

Default value

StmtCache=0

Usage

In IDS 9.2.1 and later, the database server uses the SQL statement
cache (SSC) to store SQL statements across user sessions. When any user
executes a statement already stored in the SQL statement cache, the
database server does not parse and optimize the statement again,
resulting in improved performance. The statement must be a SELECT,
UPDATE, DELETE, or INSERT statement, and it cannot contain user-defined
routines.

There are several ways to configure caching on the server. The SET
STATEMENT CACHE statement takes precedence over the STMT_CACHE
environment variable and the STMT_CACHE configuration parameter. You
must enable the SQL statement cache, either by setting the STMT_CACHE
configuration parameter or by using the Informix onmode utility, before
the SET STATEMENT CACHE statement can execute successfully.

You can set the StmtCache DBParm on the System tab page in the
Database Profile Setup dialog box for I10 connections to turn SQL
statement caching on or off on the client. However, the server must be
configured to support SQL statement caching before you can access the
cache from the client.

For more information about Informix SQL statement caching, see the
IBM Informix Dynamic Server Performance Guide at the IBM Web
site.

Examples

To specify that the client can access data from the SQL statement
cache if it is configured on the server: