Informix JDBC Driver Programmer's Guide Troubleshooting

This section describes issues that might affect the performance of your queries: the FET_BUF_SIZE environment variable, memory management of the Informix TEXT and BYTE data types, memory management of the Informix BLOB and CLOB data types, and reducing network traffic.

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 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. 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.

Whenever a large object (a BYTE, TEXT, BLOB, or CLOB data type) is fetched from the database server, the data is either cached into memory or stored in a temporary file (if it exceeds the memory buffer). A JDBC applet can cause a security violation if it tries to create a temporary file on the local computer. In this case, the entire large object must be stored in memory.

You can specify how large object data is stored by using an environment variable, LOBCACHE, that you include as one of the name-value pairs in the database URL syntax, as follows:

• To set the maximum number of bytes allocated in memory to hold the data, set the LOBCACHE value to that number of bytes. If the data size exceeds the LOBCACHE value, the data is stored in a temporary file. If a security violation occurs during creation of this file, the data is stored in memory.
• To always store the data in a file, set the LOBCACHE value to 0. In this case, if a security violation occurs, Informix JDBC Driver makes no attempt to store the data in memory. This setting is not supported for unsigned applets. For more information, see Using the Driver in an Applet.
• To always store the data in memory, set the LOBCACHE value to a negative number. If the required amount of memory is not available, Informix JDBC Driver throws the SQLException message Out of Memory.

If the LOBCACHE size is invalid or not defined, the default size is 4096.

You can set the LOBCACHE value through the database URL, as follows:

URL ="jdbc:158.58.9.37:711test:user=guest;password=iamaguest;

informixserver=oltapshm;lobcache=4096";

The preceding example stores the large object in memory if the size is 4096 bytes or fewer. If the large object exceeds 4096 bytes, Informix JDBC Driver tries to create a temporary file. If a security violation occurs, memory is allocated for the entire large object. If that fails, the driver throws an SQLException message.

Here is another example:

URL = "jdbc:informix-sqli://icarus:7110/testdb:user=guest:passwd=whoknows;

informixserver=olserv01;lobcache=0";

The preceding example uses a temporary file for storing the fetched large object.

Here is a third example:

URL = "jdbc:informix-sqli://icarus:7110/testdb:user=guest:passwd=whoknows;

informixserver=olserv01;lobcache=-1";

The preceding example always uses memory to store the fetched large object.

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. For programming information on how to use the BLOB and CLOB data types in a Java program, refer to Manipulating Informix BLOB and CLOB Data Types.

The two environment variables OPTOFC and IFX_AUTOFREE can be used to reduce network traffic when you close Statement and ResultSet objects.

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 database server cursor resources if the cursor has already been closed in the database server.

You can also use the setAutoFree(boolean flag) and getAutoFree() methods to free database server cursor resources. For more information, see The Auto Free Feature.

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 demonstration examples described in Appendix A, Sample Code Files. 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.