Setting Individual Database Buffers

Go Up to Setting Database Cache Buffers

For programs that access or change many rows in many databases, performance can sometimes be improved by increasing the number of buffers. The maximum number of buffers allowed is system dependent.

• Use the CACHE <n> parameter with CONNECT to change the number of buffers assigned to a database for the duration of the connection, where <n> is the number of buffers to reserve. To set the number of buffers for an individual database, place CACHE <n> after the database name. The following CONNECT specifies 500 buffers for the database pointed to by the EMP handle:

EXEC SQL
CONNECT EMP CACHE 500;

Note: If you specify a buffer size that is less than the smallest one currently in use for the database, the request is ignored.

The next statement opens two databases, TEST and EMP. Because CACHE is not specified for TEST, its buffers default to 256. EMP is opened with the CACHE clause specifying 400 buffers:

EXEC SQL
CONNECT TEST, EMP CACHE 400;