ODBC Server Installation and Management Manual

Installing the NonStop ODBC Server
HP NonStop ODBC Server Installation and Management Manual—429395-002
2-55
Configuring Stored Procedures
Statement caching is configured in a user’s profile record by setting the
SQL_MAX_STATEMENT_CACHE field to a number greater than zero (cache no more
than n statements). The default is zero, which means no statement caching.
The NonStop ODBC Server requires additional heap space when statement caching is
active. Typically, a simple SELECT (100 characters long) with two input and two output
parameters requires about 350 bytes of heap space to cache. You configure additional
heap space in the SCS configuration file for the necessary server class. The
recommended minimum heap size is 750 KB.)
There are no firm guidelines for when to use statement caching, how many statements
to cache, or how much heap space to specify, because all these factors depend on the
behavior of the application or the end user. In general, it is best to use as little
statement caching as you need, as caching consumes resources.
Use these steps to invoke caching for up to 75 statements for the user SQL.ODBC:
1. Add a profile entry that enables statement caching:
NOSCOM> ADD PROFILE cache SQL_MAX_STATEMENT_CACHE 75
2. Add user SQL.ODBC, specifying the profile you just added, CACHE, and server
class CLASS1.
NOSCOM> ADD USER sql_odbc sql.odbc PROFILE cache
3. Caching 75 statements might require 50 KB of memory, so the suggested heap
size of 750 KB is probably enough. However, as an example, we will modify this
profile to increase the heap size to 800 KB.
Modify the SCS configuration, setting the heap size for the server class named
CLASS1:
NOSCOM> MODIFY SERVERCLASS class1 INIT_HEAP_SIZE_KB 800
4. Start the SCS; your NonStop ODBC server for SQL.ODBC will use statement
caching.
Configuring Stored Procedures
Stored procedures are installed by writing Pathway server programs, configuring and
starting the Pathway systems, and registering the stored procedures in the NonStop
ODBC Server catalogs. For a complete description of the use of stored procedures,
see “Stored Procedures,” in the NonStop ODBC Server Reference Manual.
The last step, registration of the stored procedures in the NonStop ODBC Server
catalogs, is done by using NOSCOM. For example, to register a stored procedure
Note. If you configure statement caching, make sure that you also set MINSERVERS > 0 for
the related server classes so that the NonStop ODBC servers—and their caches of compiled
SQL statements—are reused for subsequent connections.