onmode and e arguments: Change usage of the SQL statement cache (SQL administration API)

Use the onmode and e arguments with the admin() or task() function to temporarily change the mode of the SQL statement cache.


Read syntax diagramSkip visual syntax diagram
>>-EXECUTE FUNCTION--+-admin-+--(--"onmode"--,--"e"--,---------->



Use the enable argument to enable the SQL statement cache if it is disabled. Individual user sessions can use the statement cache only after they perform either of the following actions:
  • Set the environment variable STMT_CACHE to 1.
  • Execute the SQL statement SET STATEMENT CACHE ON.

Use the flush argument to flush the statements that are not in use from the SQL statement cache, which remains enabled. After the cache is flushed, the onstat -g ssc ref_cnt field shows 0.

Use the off argument to turn off the SQL statement cache, so that no statements are cached.

Use the on argument to cache all statements except those a user turns off by one of the following actions:

  • Use this command to specify the OFF mode.
  • Set the environment variable STMT_CACHE to 0.
  • Execute the SQL statement SET STATEMENT CACHE OFF statement.

This function cannot modify the STMT_CACHE configuration parameter setting in the ONCONFIG file, but the last argument overrides that setting (or the default value, if STMT_CACHE is not set). Any changes to the statement cache behavior that you make with this command are in effect for the current database server session only. When you restart the database server, it uses the setting of the STMT_CACHE parameter in the ONCONFIG file. If the STMT_CACHE configuration parameter is not defined in the ONCONFIG file, the server does not use a statement cache.

This function is equivalent to the onmode -e command.


The following example enables the SQL statement cache:
EXECUTE FUNCTION task("onmode","e","enable");

Copyright© 2018 HCL Technologies Limited