ODBC Server Installation and Management Manual

Managing the NonStop ODBC Server
HP NonStop ODBC Server Installation and Management Manual—429395-002
4-4
SQL Statement Caching
If you are running NonStop ODBC Release 1, use the TDBSRV:STATUS command at
the TACL prompt.
The syntax for this command is:
TDBSRV:STATUS SCS-config-file
where SCS-config-file is the name of the SCS configuration file used to bring up
the system. This command generates a TACL status display of all of the processes
running for that NonStop ODBC Server system. An example of the output from this
command is shown in Section 2, Installing the NonStop ODBC Server.
If you are using Release 2, use the NOSCOM STATUS command.The syntax for this
command is:
NOSCOM STATUS SCS-name
SQL Statement Caching
Statement caching is useful in an environment where similar SQL statements are
generated in the same session, or across sessions within the same NonStop ODBC
Server class, and SQL compilation time is significant compared to SQL execution time.
Using statement caching in such an environment improves SQL statement execution
time.
When statement caching is enabled, the NonStop ODBC Server caches the compiled
statements. When an equivalent statement is resubmitted, the SQL compilation step is
skipped, and the statement is executed from the cache.
Configuring Statement Caching
Statement caching is disabled by default, so it must be configured to turn it on.
Statement caching can be configured in each user’s profile by setting the
SQL_MAX_STATEMENT_CACHE field to a number greater than zero (cache n
statements). Note that the NonStop ODBC Server requires additional heap space
when statement caching is active.
Typically, a simple SELECT (100 characters long) with two input variables and two
output variables of type INTEGER uses about 350 bytes of the NonStop ODBC Server
cache. In addition, the plan is stored in the SQL Executor’s extended memory for later
reference. Therefore, you should ensure there is enough primary (or secondary) disk
space.
The NonStop ODBC Server’s heap space can be configured for a server class by
altering the HEAP-SIZE parameter. A NonStop ODBC Server needs about 400
kilobytes to run even without caching.
Statement caching is turned on in the user’s profile. Therefore, a server class can
switch between statement caching and no statement caching, depending on the
identity of the user who logs on to the server. The cache is resized as different users