SRV_FET_BUF_SIZE environment variable

Use the SRV_FET_BUF_SIZE environment variable to specify the size of the fetch buffer that the local database server uses in distributed DML transactions across database servers.


1  setenv SRV_FET_BUF_SIZE size
size
is a positive integer that is no greater than 1048576 (1 MiB), specifying the size (in bytes) of the fetch buffer that holds data retrieved by a cross-server distributed query.
For example, to set a buffer size to 5,000 bytes on a UNIX™ system that uses the C shell, set SRV_FET_BUF_SIZE by entering the following command:
setenv SRV_FET_BUF_SIZE 5000

When SRV_FET_BUF_SIZE is set to a valid value, the new value overrides the default value (or any previously set value) of SRV_FET_BUF_SIZE. The setting takes effect only when it is set in the starting environment of the database server.

When SRV_FET_BUF_SIZE is not set, the default setting for the fetch buffer is dependent on row size.

No error is raised if SRV_FET_BUF_SIZE is set to a value that is less than the default size, or that is greater than 1048576 (1MiB). If you specify a size for SRV_FET_BUF_SIZE that is greater than 1048576, the value is set to 1048576. In older 11.70 releases, up to and including 11.70.xC4, the upper limit is 32767.

A valid SRV_FET_BUF_SIZE setting is in effect only in cross-server DML transactions in which the local database server participates as the coordinator or as a subordinate database server.
  • It has no effect, however, on queries that access only databases of the local server instance, and it does not affect the size of the fetch buffer in client-to-local-server communication.
  • The processing of BYTE and TEXT objects is not affected by the SRV_FET_BUF_SIZE setting.
  • Setting SRV_FET_BUF_SIZE for the environment of the local database server does not reset the fetch buffer size of remote server instances that coordinate or participate in cross-server DML transactions with the local server instance.

The greater the size of the buffer, the more rows can be returned, and the less frequently the local server must wait while the database server returns rows. A large buffer can improve performance when transferring a large amount of data between servers.