FET_BUF_SIZE environment variable

The FET_BUF_SIZE environment variable can override the default setting for the size of the fetch buffer for all data types except BYTE and TEXT values. For ANSI databases, you must set transactions to READ ONLY for the FET_BUF_SIZEenvironment variable to improve performance, otherwise rows are returned one by one.

1  setenv FET_BUF_SIZE size
is a positive integer that is larger than the default buffer size, but no greater than 2147483648 (2GB), specifying the size (in bytes) of the fetch buffer that holds data retrieved by a query.
For example, to set a buffer size to 5,000 bytes on a UNIX™ system that uses the C shell, set FET_BUF_SIZE by entering the following command:
setenv FET_BUF_SIZE 5000

When FET_BUF_SIZE is set to a valid value, the new value overrides the default value (or any previously set value of FET_BUF_SIZE). The default setting for the fetch buffer is dependent on row size.

The processing of BYTE and TEXT values is not affected by FET_BUF_SIZE.

No error is raised if FET_BUF_SIZE is set to a value that is less than the default size or is larger than 2147483648 (2GB). In these cases, however, the invalid fetch buffer size is ignored, and the default size is in effect.

A valid FET_BUF_SIZE setting is in effect for the local database server and for any remote database server from which you retrieve rows through a distributed query in which the local server is the coordinator and the remote database is subordinate. The greater the size of the buffer, the more rows can be returned, and the less frequently the client application must wait while the database server returns rows. A large buffer can improve performance by reducing the overhead of filling the client-side buffer.