onmode -e: Change usage of the SQL statement cache

Figure 1: Syntax:

1  onmode   -e mode
Element Purpose Key considerations
onmode -e ENABLE Enables the SQL statement cache. For more information, see the material on improving query performance in the HCL OneDB™ Performance Guide. User sessions use the cache only when they perform either of the following actions:
  • Set the environment variable STMT_CACHE to 1
  • Execute the SQL statement SET STATEMENT CACHE ON
onmode -e FLUSH Flushes the statements that are not in use from the SQL statement cache The onstat -g ssc ref_cnt field shows 0.
onmode -e OFF Turns off the SQL statement cache No statements are cached.
onmode -e ON Turns on the SQL statement cache All statements are cached unless the user turns it off with one of the following actions:
  • Set the environment variable STMT_CACHE to 0
  • Execute the SQL statement SET STATEMENT CACHE OFF

The onmode -e changes are in effect for the current database server session only. When you restart the database server, it uses the default STMT_CACHE parameter value in the ONCONFIG file.

This command has an equivalent SQL administration API function.