ODBC Server Installation and Management Manual

Managing the NonStop ODBC Server
HP NonStop ODBC Server Installation and Management Manual—429395-002
4-5
Caching Considerations
with different caching profiles log on to the same server. If the statement cache is
resized to be smaller, the least recently used statements are dropped out of the cache.
If the cache is full and a new entry needs to be added, the least recently used cached
statement is dropped.
The following process shows how caching is turned on for the user SQL_ODBC. In this
scenario, this user’s name has not been added to the system’s user table (ZNSUS).
1. Add a profile entry that enables statement caching:
NOSCOM> ADD PROFILE CACHE SQL_MAX_STATEMENT_CACHE 100
2. Add the user SQL_ODBC to the NonStop ODBC Server system tables with
NOSUTIL. The following command, using the NonStop interface to NOSUTIL,
NOSCOM (the same action can also be performed using the PC interface, the
NonStop ODBC Server Configuration Manager), also associates the user with a
profile named CACHE that enables statement caching:
NOSCOM> ADD USER SQL_ODBC SQL.ODBC PROFILE CACHE
When SQL_ODBC logs on to the server, the CACHE profile is read. The NonStop
ODBC Server is set up to cache up to 100 statements. If this limit is reached, the least
recently used cached statements are dropped.
Suppose that SQL_ODBC issues five different SQL DML statements. The user
disconnects and reconnects to the same NonStop ODBC Server and issues the same
statements. The statements are executed immediately, and the time that would
otherwise be spent compiling these statements is saved.
To turn off statement caching, set SQL_MAX_STATEMENT_CACHE to zero.
Caching Considerations
Consider the following items when configuring and using SQL statement caching:
•
Statement caching requires additional extended memory. Therefore, there should
be sufficient space on the swap volume of the NonStop ODBC Server processes to
accommodate caching.
•
If statement caching is being used and an INSERT statement has been
parameterized, it is possible for a NonStop ODBC Server to return error 32214 the
next time the INSERT statement is executed if the table referenced by the INSERT
statement was dropped and re-created in an incompatible way. For example:
create table t1 (c1 int)
insert into t1 values (10) is parameterized
drop table t1
create table t1 (c1 varchar(20))
insert into t1 values ("abcdefgh")
*** ODBC error 32214 reported
Do not use statement caching if you are doing these types of DDL operations. If
you encounter this problem, the easiest solution is to flush the cache (see the
following subsection).