Specifies in kilobytes the size of the buffer area (shared memory) in which SQL objects are to be placed.
Specificationguidelines
SQL objects are saved in a buffer until the user's transaction has terminated. The buffer must be large enough to store the SQL objects of all transactions that will be executed concurrently.
SQL analysis can be reduced by saving the SQL objects of static SQLs in the buffer after transaction termination (until the buffer runs out of space) and sharing them among multiple users who execute the same UAP. To effectively utilize the buffer, it should be allocated so that the SQL objects of frequently-used UAPs are resident in the buffer.
To estimate the buffer size, first determine the buffer size needed for UAP execution from the length of the SQL objects from the SQL statements to be issued by the UAP. Then, compute the buffer size by considering the number of UAPs that will be executed concurrently and the number of concurrently executing users.
Specifies in kilobytes the size of the buffer (shared memory) for table definition information that has been used. Table definition information is used during pre-processing of an SQL statement.
Advantages
Table definition information that has been used is kept in the memory as long as possible and thus can be used subsequently without an input operation.
Performance improves when a large number of dynamic SQLs are used.
The number of communications with the dictionary server is reduced (in a HiRDB/Parallel Server).
Specificationguidelines
The total size of the definition information for frequently-used tables is specified.
Specifies in kilobytes the size of the buffer (shared memory) for user privilege information.
Specificationguidelines
The user privilege information buffer stores CONNECT privilege, DBA privilege, and audit privilege information. If this buffer contains no information, information is obtained from a dictionary table during HiRDB connection, thus lengthening the response time. Therefore, specify a buffer size that can store the information for the users who are always connected.
Storing the user privilege information of each user requires 68 bytes. Use this information when computing the total buffer size.
Tuningthespecifiedvalue
For details about how to tune the size of the user privilege information buffer, see the HiRDB Version 8 System Operation Guide.
This operand is applicable to user-defined types; specification of this operand is recommended when user-defined types will be used.
Specifies in kilobytes the size of the buffer (shared memory) for information on user-defined types. When a user-defined type is used, information on it is stored in this buffer. This information is used during pre-processing of SQL statements.
Advantages
When user-defined type information is stored in this buffer, it is not necessary to access the dictionary table when the same user-defined type is used subsequently, thus reducing the number of input operations and the CPU usage time.
The number of communications with the dictionary server is reduced.
Specification of this operand improves performance when many dynamic SQLs are used.
Specificationguidelines
The specified size should be the total of the sizes of the definition information for frequently-used user-defined types that are defined in tables. The following formula can be used to determine the definition information size for one user-defined type:
{((0.3 + 0.2 a + 0.1 b) + 3) 4} 4 (KB)
a: Number of user-defined type attributes
b: Number of subtypes that have inherited a supertype
Tuningthespecifiedvalue
For details about how to tune the size of the buffer for user-defined type information, see the HiRDB Version 8 System Operation Guide.
Specifies in kilobytes the size of the buffer (shared memory) for storing the following types of definition information (this information is used during pre-processing of SQL statements):
Plug-in facility definition information
System definition scalar facility definition information
Routine definition information
Advantages
When these types of definition information are stored in this buffer, it is not necessary to access the dictionary table when the same information is used subsequently, thus reducing the number of I/O operations and CPU usage time.
The number of communications with the dictionary server is reduced.
Applicationcriteria
This operand should be specified when a large number of the following types of SQL statements are used:
SQL statements that use a plug-in
SQL statements that use the system definition scalar facility
For details about how to tune the size of the routine definition information buffer, see the HiRDB Version 8 System Operation Guide.
Note
If the value specified for this operand is smaller than the total of the sizes of the definition information for all plug-ins, the definition information on plug-in facilities will not be allocated in the buffer.
This operand is related to plug-ins. If you use a plug-in that uses registry information, Hitachi recommends the use of this operand. Hitachi also recommends the use of this operand if you use HiRDB Text Search Plug-in.
Specify the size of the buffer (shared memory) for storing registry information (units: KB). When registry information is used, it is stored in the buffer. Registry information is used during the execution of an SQL statement.
Advantages
Once registry information is stored in this buffer, it is not necessary to access the registry when the same information is used subsequently, thus reducing the number of I/O operations and CPU usage time.
The number of communications with the dictionary server is reduced.
Specifying this operand can improve performance when a plug-in that makes frequent use of registry information is used.
Specificationguidelines
Use the following formula to determine the registry information buffer size:
(0.3 + a)b (KB)
a: Average registry key length (KB)
The average registry key length can be determined with the following SQL statement:
SELECT AVG(KEY_LENGTH) FROM MASTER.SQL_REGISTRY
Because the result of this SQL statement is output in bytes, convert it to kilobytes.
b: Number of registry keys registered
The number of registry keys registered can be determined with the following SQL:
SELECT COUNT(*)FROM MASTER.SQL_REGISTRY
Tuningthespecifiedvalue
For details about how to tune the size of the buffer for registry information, see the HiRDB Version 8 System Operation Guide.