The FET_BUF_SIZE and BIG_FET_BUF_SIZE Environment Variables

When a SELECT statement is sent from a Java program to an Informix database, the returned rows, or tuples, are stored in a tuple buffer in IBM Informix JDBC Driver. 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 32,767. 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.

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 BIG_FET_BUF_SIZE environment variable to increase the size of the tuple buffer.

For more information on setting Informix environment variables, see Connecting to the Database. For more information on increasing the fetch buffer size, see the IBM Informix: Guide to SQL Reference.