Set database server shared-memory configuration parameters

You can modify the configuration parameters that affect the resident, buffer pool, or virtual portion of shared memory.

Set parameter for buffer pool shared memory

The BUFFERPOOL configuration parameter in the onconfig file specifies information about a buffer pool. Each page size that is used by the database server requires a buffer pool, which is represented in the onconfig file by a BUFFERPOOL configuration parameter entry.

Set parameters for resident shared memory

The following list contains parameters in the onconfig file that specify the configuration of the buffer pool and the internal tables in the resident portion of shared memory. Before any changes that you make to the configuration parameters take effect, you must shut down and restart the database server.
LOCKS
Specifies the initial number of locks for database objects; for example, rows, key values, pages, and tables.
LOGBUFF
Specifies the size of the logical-log buffers.
PHYSBUFF
Specifies the size of the physical-log buffers.
RESIDENT
Specifies residency for the resident portion of the database server shared memory.
SERVERNUM
Specifies a unique identification number for the database server on the local host computer.
SHMTOTAL
Specifies the total amount of memory to be used by the database server.

Set parameters for virtual shared memory

The following list contains the configuration parameters that you use to configure the virtual portion of shared memory:
DS_HASHSIZE
Number of hash buckets for lists in the data-distribution cache.
DS_POOLSIZE
Maximum number of entries in the data-distribution cache.
PC_HASHSIZE
Specifies the number of hash buckets for the UDR cache and other caches that the database server uses.
PC_POOLSIZE
Specifies the number of UDRs (SPL routines and external routines) that can be stored in the UDR cache. In addition, this parameter specifies the size of other database server caches, such as the typename cache and the opclass cache.
SHMADD
Specifies the size of dynamically added shared-memory segments.
SHMNOACCES
Specifies a list of virtual memory address ranges that are not used to attach shared memory. Use this parameter to avoid conflicts with other processes.
EXTSHMADD
Specifies the size of a virtual-extension segment added when a user-defined routine or a DataBlade routine runs in a user-defined virtual processor.
SHMTOTAL
Specifies the total amount of memory to be used by the database server.
SHMVIRTSIZE
Specifies the initial size of the virtual portion of shared memory.
STACKSIZE
Specifies the stack size for the database server user threads.

Set parameters for shared-memory performance

The following configuration parameters affect shared-memory performance.
AUTO_READAHEAD
Specifies the automatic read-ahead mode or disables automatic read-ahead operations for a query. Automatic read-ahead operations help improve query performance by issuing asynchronous page requests when the database server detects that the query is encountering I/O. Asynchronous page requests can improve query performance by overlapping query processing with the processing necessary to retrieve data from disk and put it in the buffer pool.
CKPTINTVL
Specifies the maximum number of seconds that can elapse before the database server checks if a checkpoint is required and the RTO_SERVER_RESTART configuration parameter is not set to turn on automatic checkpoint tuning.
CLEANERS
Specifies the number of page-cleaner threads that the database server is to run.

Copyright© 2019 HCL Technologies Limited