Manage the fetch buffer size

Use the FET_BUF_SIZE and SRV_FET_BUF_SIZE environment variables to set the size of the fetch buffer.

When a SELECT statement is sent from a Java™ program to the IBM® Informix® database, the returned rows, or tuples, are stored in a tuple buffer in . The default size of the tuple buffer is the larger of the returned tuple size or 4096 bytes.

You can use the Informix FET_BUF_SIZE environment variable to override the default size of the tuple buffer. FET_BUF_SIZE can be set to any positive integer less than or equal to 2 GiB (2147483648). If the FET_BUF_SIZE environment variable is set, and its value is larger than the default tuple buffer size, the tuple buffer size is set to the value of FET_BUF_SIZE.

Similarly, you can use the SRV_FET_BUF_SIZE environment variable to set the fetch buffer size for the local database server to use when it participates in cross-server distributed DML transactions. For 11.70.xC5 and newer versions, the maximum size to which SRV_FET_BUF_SIZE can be set is 1048576 ( = 1 MiB).

Increasing the size of the tuple buffer can reduce network traffic between your Java program and the database, often resulting in better performance of queries. There are times, however, when increasing the size of the tuple buffer can actually degrade the performance of queries. This could happen if your Java program has many active connections to a database or if the swap space on your computer is limited. If this is true for your Java program or computer, you might not want to use the FET_BUF_SIZE or SRV_FET_BUF_SIZE environment variables to increase the size of the tuple buffer.

For more information about setting Informix environment variables, see Connect to the database. For more information about increasing the fetch buffer size, see the IBM Informix Guide to SQL: Reference.