Using the FET_BUF_SIZE Environment Variable

You can use the Informix FET_BUF_SIZE environment variable to override the default size of the tuple buffer. 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.

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.

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

For more information on setting Informix environment variables, see "Establishing a Connection".

Memory Management of TEXT and BYTE Data Types

If the TEXT or BYTE value is less than or equal to 1024 bytes, Informix JDBC Driver fetches the value into main memory. This improves the performance of retrieving small simple large object data.

If, however, the TEXT or BYTE value is larger than 1024 bytes, Informix JDBC Driver first attempts to store the value in a file on the operating system. If this attempt fails, the entire TEXT or BYTE value is fetched into main memory, regardless of its size. This ensures that values fetched from within a Java applet are retrieved correctly, even though browsers are usually unable to write to the local operating system.

For programming information on how to use the TEXT and BYTE data types in a Java program, refer to "Manipulating Informix BYTE and TEXT Data Types".

Reducing Network Traffic

Set OPTOFC to 1 to specify that the ResultSet.close() method does not require a network round-trip if all the qualifying rows have already been retrieved in the client's tuple buffer. The database server automatically closes the cursor after all the rows have been retrieved.

Informix JDBC Driver might or might not have additional rows in the client's tuple buffer before the next ResultSet.next() method is called. Therefore, unless Informix JDBC Driver has received all rows from the database server, the ResultSet.close() method might still require a network round-trip when OPTOFC is set to 1.

Set IFX_AUTOFREE to 1 to specify that the Statement.close() method does not require a network round-trip to free the server cursor resources if the cursor has already been closed in the database server.

The database server automatically frees the cursor resources right after the cursor is closed, either explicitly by the ResultSet.close() method or implicitly by the OPTOFC environment variable.

When the cursor resources have been freed, the cursor can no longer be referenced.

For examples of how to use the OPTOFC and IFX_AUTOFREE environment variables, see the autofree.java and optofc.java demo examples. In these examples, the variables are set with the Properties.put() method.

For more information on setting Informix environment variables, refer to "Establishing a Connection".