Enabling the SQL statement cache

The database server does not use the SQL statement cache when the STMT_CACHE configuration parameter is 0 (the default value). You can change this value to enable the SQL statement cache in one of two modes.

Use one of the following methods to change this STMT_CACHE default value:

Update the ONCONFIG file to specify the STMT_CACHE configuration parameter and restart the database server.
If you set the STMT_CACHE configuration parameter to 1, the database server uses the SQL statement cache for an individual user when the user sets the STMT_CACHE environment variable to 1 or executes the SET STATEMENT CACHE ON statement within an application.
```
STMT_CACHE 1
```
If the STMT_CACHE configuration parameter is 2, the database server stores SQL statements for all users in the SQL statement cache except when individual users turn off the feature with the STMT_CACHE environment variable or the SET STATEMENT CACHE OFF statement.
```
STMT_CACHE 2
```
Use the onmode -e command to override the STMT_CACHE configuration parameter dynamically.
If you use the enable keyword, the database server uses the SQL statement cache for an individual user when the user sets the STMT_CACHE environment variable to 1 or executes the SET STATEMENT CACHE ON statement within an application.
```
onmode -e enable
```
If you use the on keyword, the database server stores SQL statements for all users in the SQL statement cache except when individual users turn off the feature with the STMT_CACHE environment variable or the SET STATEMENT CACHE OFF statement.
```
onmode -e on
```

The following table summarizes the use of the SQL statement cache, which depends on the setting of the STMT_CACHE configuration parameter (or the execution of onmode -e) and the use in an application of the STMT_CACHE environment variable and the SET STATEMENT CACHE statement.


STMT_ CACHE Configuration Parameter or onmode -e	STMT_CACHE Environment Variable	SET STATEMENT CACHE Statement	Resulting Behavior
0 (default)	Not applicable	Not applicable	Statement cache not used
1	0 (or not set)	OFF	Statement cache not used
1	1	OFF	Statement cache not used
1	0 (or not set)	ON	Statement cache used
1	1	ON	Statement cache used
1	1	Not executed	Statement cache used
1	0	Not executed	Statement cache not used
2	1 (or not set)	ON	Statement cache used
2	1 (or not set)	OFF	Statement cache not used
2	0	ON	Statement cache used
2	0	OFF	Statement cache not used by user
2	0	Not executed	Statement cache not used by user
2	1 (or not set)	Not executed	Statement cache used by user