Informix environment variables with the IBM Informix JDBC Driver

The following table lists most of the IBM® Informix® environment variables supported by the client JDBC driver. For server-side JDBC, use property settings in the database URL rather than setting environment variables, because the environment variables would apply to all programs running in the database server. For more information about properties, see Specify properties.

For a list of environment variables that provide globalization features, see Globalization and date formats. For a list of environment variables useful for troubleshooting, see Tuning and troubleshooting
Supported Informix environment variables Description
APPENDISAM When set to TRUE, the APPENDISAM environment variable appends the ISAM Error code and message (if present) to the SQL Exception message, which is shown when .toString() or .getMessage() of an SQL Exception is called. The exception message is shown in the following format:
<INFORMIX ERROR MESSAGE> (<INFORMIX CODE>)
ISAM error: <ISAM MESSAGE>(<ISAM CODE>)
AUTO_CASE_SCHEMA To instruct JDBC to automatically case schema (default = true) or when set to false, to return the schema as it was saved.
CSM To specify that Communication Support Module is to be used. IBM Informix JDBC Driver 3.0 and later supports an encryption CSM. For more information, see Encryption options.
Note: Support for Communication Support Module (CSM) is removed starting Informix Server 14.10.xC9 . You should use Transport Layer Security (TLS)/Secure Sockets Layer (SSL) instead.
DBANSIWARN When set to 1, checks for Informix extensions to ANSI-standard syntax
DBSPACETEMP Specifies the dbspaces in which temporary tables are built
DBTEMP Specifies the full path name of the directory into which you want gateway products to place their temporary files and temporary tables.

The driver does not use this variable; it just passes the value to the server.
DBUPSPACE Specifies the amount of disk space and memory that the UPDATE STATISTICS statement can use for sorting rows when it constructs multiple-column distributions, whether to sort with indexes, and whether to save the plan for calculating the column distributions in the sqexplain.out file.
DELIMIDENT When set to Y, specifies that strings set off by double quotation marks are delimited identifiers
ENABLE_TYPE_CACHE When set to TRUE, caches the data type information for opaque, distinct, or row data types.

When a Struct or SQLData object inserts data into a column and getSQLTypeName() returns the type name, the driver uses the cached information instead of querying the database server.
ENABLE_HDRSWITCH When set to TRUE, secondary server properties are used to connect to the secondary server if the primary server is unavailable.
FET_BUF_SIZE Overrides the default setting for the size of the fetch buffer for all data except large objects.

The default size is 4096 bytes. This variable is not supported in server-side JDBC.
IFX_AUTOFREE When set to 1, specifies 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.

The database server automatically frees the cursor resources after the cursor is closed, either explicitly by the ResultSet.close() method or implicitly through the OPTOFC environment variable. After the cursor resources have been freed, the cursor can no longer be referenced. For more information, see The Auto Free feature.
IFX_BATCHUPDATE_PER_SPEC When set to 1 (the default), returns the number of rows affected by the SQL statements executed in a batch operation by the executeBatch() method
IFX_CODESETLOB If set to a number greater than or equal to 0, automates code-set conversion for TEXT and CLOB data types between client and database locales. The value of this variable determines whether code-set conversion is done in memory in or in temporary files. If set to 0, code-set conversion uses temporary files. If set to a value greater than 0, code-set conversion occurs in the memory of the client computer, and the value represents the number of bytes of memory allocated for the conversion. For more information, see Convert with the IFX_CODESETLOB environment variable.
IFX_DIRECTIVES Determines whether the optimizer allows query optimization directives from within a query. This variable is set on the client. The driver does not use this variable; it just passes the value to the server.
IFX_EXTDIRECTIVES Specifies whether the query optimizer allows external query optimization directives from the sysdirectives system catalog table to be applied to queries in existing applications. The default is OFF. Possible values:
ON
External optimizer directives accepted
OFF
External optimizer directives not accepted
1
External optimizer directives accepted
0
External optimizer directives not accepted
IFX_GET_SMFLOAT_AS_FLOAT When set to 0 (the default), maps the Informix SMALLFLOAT data type to the JDBC REAL data type. This setting conforms to the JDBC specification. When set to 1, maps the Informix SMALLFLOAT data type to the JDBC FLOAT data type. This setting enables compatibility with earlier versions of IBM Informix JDBC Driver.
IFX_ISOLATION_LEVEL Defines the degree of concurrency among processes that attempt to access the same rows simultaneously. Gets the value of theIFX_ISOLATION_LEVEL variable, which is specific to Informix. The default value is 2 (Committed Read). If the value has been set explicitly, it returns the set value. Returns: integer.
Sets the value of the IFX_ISOLATION_LEVEL variable, which is specific to Informix. Possible values:
0
Equivalent to TRANSACTION_NONE
1
Dirty Read (equivalent to TRANSACTION_READ_UNCOMMITTED),
2
Committed Read (equivalent to TRANSACTION_READ_COMMITTED),
3
Cursor Stability (equivalent to TRANSACTION_READ_COMMITTED),
4
Repeatable Read (equivalent to TRANSACTION_REPEATABLE_READ)
5
Committed Read LAST COMMITTED (equivalent to TRANSACTION_LAST_COMMITTED)
8
Equivalent to TRANSACTION_SERIALIZABLE

Specifying U after the mode means retain update locks. (See the Important note following table.) For example, a value could be: 2U (equivalent to SET ISOLATION TO COMMITTED READ RETAIN UPDATE LOCKS

The following example shows the code that you would use to specify an isolation level: 

conn.setTransactionIsolation
   (IfxConnection.TRANSACTION_LAST_COMMITTED);
IFX_FLAT_UCSQ Overrides the global setting and directs the optimizer to use subquery flattening for all sessions. Default value is 1.
IFX_LO_READONLY Controls how smart large objects are opened by the database server during a query by a client application:
0
Default. Smart large objects are opened with read-write access. The database server locks the object until the object is closed by the client application or the transaction is completed.
1
Smart large objects are opened with read-only access. The database server does not lock the object during a query.
IFX_LOCK_MODE_WAIT Application can use this property to override the default server process for accessing a locked row or table. Gets the value of the IFX_LOCK_MODE_WAIT variable, which is specific to Informix. The default value is 0 (do not wait for the lock). If the value has been set explicitly, it returns the set value. Returns: integer.
Sets the value of the IFX_LOCK_MODE_WAIT variable, which is specific to Informix. Possible values:
-1
WAIT until the lock is released.
0
DO NOT WAIT, end the operation, and return with error.
nn
WAIT for nn seconds for the lock to be released.
IFX_PAD_VARCHAR Controls how data associated with a VARCHAR data type is transmitted to and from the Informix server. Can be set either on the connection URL when using the Connection class or as a property when using the DataSource class. Valid values are 0 (the default) and 1.
  • When set to 0, only the portion of the VARCHAR that contains data is transmitted (trailing spaces are stripped).
  • When set to 1, the entire VARCHAR data structure is transmitted to and from the server.
IFX_SET_FLOAT_AS_SMFLOAT When set to 0 (the default), maps the JDBC FLOAT data type to the Informix FLOAT data type. This setting conforms to the JDBC specification. When set to 1, maps the JDBC FLOAT data type to the Informix SMALLFLOAT data type. This setting enables compatibility with earlier versions of IBM Informix JDBC Driver.
IFX_SOC_KEEPALIVE When set to true, sets the TCP property SO_KEEPALIVE on the socket for open connections. This setting is useful to keep long running idle socket connections from timing out due to inactivity. Default is false.
IFX_TRIMTRAILINGSPACES Removes trailing spaces. Default value is 1.
IFX_USEPUT When set to 1, enables bulk inserts. For more information, see Perform bulk inserts.
IFX_XASPEC When set to y, XA transactions with the same global transaction ID are tightly coupled and share the lock space. This only applies to XA connections and cannot be specified in a database URL. It can be specified by DataSource setter (See DataSource extensions.) or by setting a System (JVM) property with the same name. The DataSource property overrides the System property. Values for the properties other than y, Y, n, or N are ignored. IfxDataSource.getIfxIFX_XASPEC returns the final IFX_SPEC value, which is either y or n. For example if the value of DataSource IFX_XASPEC equals n and the value of the System IFX_XASPEC equals Y or y, n is returned.
IFX_XASTDCOMPLIANCE_XAEND Specifies the behavior of XA_END when XA_RB* is returned.
0
XID is not forgotten. Transaction is in Rollback Only state. This is XA_SPEC+ compliant and is the default behavior with Informix.
1
XID is forgotten. Transaction is Nonexistent.
IFXHOST Sets the host name or host IP address
IFXHOST_SECONDARY Sets the secondary host name or host IP address for HDR connection redirection
INFORMIXCONRETRY Specifies the maximum number of additional connection attempts that can be made to each database server by the client during the time limit specified by the value of INFORMIXCONTIME
INFORMIXCONTIME Sets the timeout period for an attempt to connect to the database server. If a connection attempt does not succeed in this time, the attempt is aborted and a connection error is reported. The default value is 0 seconds. This variable adds timeouts for blocking socket methods and for socket connections.
INFORMIXOPCACHE Specifies the size of the memory cache for the staging-area blobspace of the client application
INFORMIXSERVER Specifies the default database server to which an explicit or implicit connection is made by a client application.
Note: This parameter is optional for simple connections (connections that do not use a SQLHOST file) since 4.10.JC4.
INFORMIXSERVER_SECONDARY Specifies the secondary database server in an HDR pair to which an explicit or implicit connection is made by a client application if the primary database server is unavailable.
INFORMIXSTACKSIZE Specifies the stack size, in kilobytes, that the database server uses for a particular client session.
JDBCTEMP Specifies where temporary files for handling smart large objects are created. You must supply an absolute path name.
LOBCACHE Determines the buffer size for large object data that is fetched from the database server Possible values are:
A number greater than 0
The maximum number of bytes is allocated in memory to hold the data. 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.
Zero
The data is always stored in a file. If a security violation occurs, the driver makes no attempt to store the data in memory.
A negative number
The data is always stored in memory. If the required amount of memory is not available, an error occurs.
If the LOBCACHE value is not specified, the default is 4096 bytes.
LOGINTIMEOUT Determines whether the Informix database server is running. If the server is running a connection to the server is established immediately. If the server is not running, this environment variable specifies how long, in milliseconds, that the server port is polled to establish a connection. If your application does not connect to the Informix database server within the specified time, an error is returned.
METADATA_UPPERCASE_VALUES Used to uppercase the ResultSet Metadata. When set to true, it returns ResultSet Metadata information in uppercase. This parameter is optional.
NEWNLSMAP Allows new mappings to be defined between NLS and Java development kit locales and code sets.

For more information, see User-defined locales.
NODEFDAC Prevents the PUBLIC group from receiving table or routine privileges by default when new tables or UDRs are created in a database that was not created WITH LOG MODE ANSI. The required yes setting is case sensitive.
OPT_GOAL Specifies the query performance goal for the optimizer. Set this variable in the user environment before you start an application. The driver does not use this variable; it just passes the value to the server.
OPTCOMPIND Specifies the join method that the query optimizer uses
OPTOFC When set to 1, the ResultSet.close() method does not require a network round trip if all the qualifying rows have already been retrieved in the clients tuple buffer. The database server automatically closes the cursor after all the rows have been retrieved. IBM Informix JDBC Driver might not have additional rows in the clients tuple buffer before the next ResultSet.next() method is called. Therefore, unless IBM Informix JDBC Driver has received all the rows from the database server, the ResultSet.close() method might still require a network round trip when OPTOFC is set to 1.
PATH Specifies the directories that are to be searched for executable programs
PDQPRIORITY Determines the degree of parallelism used by the database server
PLCONFIG Specifies the name of the configuration file used by the high-performance loader
PLOAD_LO_PATH Specifies the path name for smart-large-object handles (which identify the location of smart large objects such as BLOB, CLOB, and BOOLEAN data types).

The driver does not use this variable; it just passes the value to the server.
PORTNO_SECONDARY Specifies the port number of the secondary database server in an HDR pair. The port number is listed in the /etc/services file.
PROXY Specifies an HTTP proxy server. For more information, see An HTTP proxy server.
PSORT_DBTEMP Specifies one or more directories to which the database server writes the temporary files it uses when performing a sort
PSORT_NPROCS Enables the database server to improve the performance of the parallel-process sorting package by allocating more threads for sorting
SECURITY Uses 56-bit encryption to send the password to the server. For more information, see Password encryption.
SQLH_TYPE When set to FILE, specifies that database information (such as host-name, port-number, user, and password) is specified in an sqlhosts file.
When set to LDAP, specifies that this information is specified in an LDAP server. For more information, see Dynamically reading the Informix sqlhosts file.
Note: Starting Informix JDBC Driver version 4.50.JC10, the use of LDAP to retrieve Informix server connectivity information from a stored SQLHost files inside of an LDAP server has been removed.
SQLIDEBUG Specifies the path name for the file to which a binary SQLI trace is to be written. A new trace file is generated for every connection and is suffixed with timestamp. Only use the SQLI trace facility when directed by the IBM technical support representative.
SRV_FET_BUF_SIZE Overrides the default setting for the size of the fetch buffer in distributed transactions with other database servers. That fetch buffer holds, for example, the data retrieved by a cross-server distributed query. For IBM Informix 11.70.xC5 and later versions, the maximum value is 1048576 (1MiB).
SSLCONNECTION When set to true, enables the connection to use SSL encryption for communication to the server.
SSL_TRUSTSTORE Specifies the location of the truststore to load by the JDBC driver.
SSL_TRUSTSTORE_PASSWORD Specifies the password to the truststore that is being loaded by the JDBC driver.
STMT_CACHE When set to 1, enables the use of the shared-statement cache in a session.

This feature can reduce memory consumption and speed query processing among different user sessions. The driver does not use this variable; it just passes the value to the server.
TRUSTED_CONTEXT When set to TRUE, a trusted connection request is sent from client. Either a successful trusted connection is established or the following error is returned from the server: SQL Exception : -28021(Trusted Connection request rejected.)

For a detailed description of a particular environment variable, see IBM Informix Guide to SQL: Reference.

Code example IFX_LOCK_MODE_WAIT environment variable

IFX_LOCK_MODE_WAIT
Connection conn = DriverManager.getConnection ( "jdbc:Informix-sqli://cleo:1550:
IFXHOST=cleo;PORTNO=1550;user=rdtest;password=my_passwd;IFX_LOCK_MODE_WAIT=1";);

Code example IFX_ISOLATION_LEVEL environment variable

IFX_ISOLATION_LEVEL
Connection conn = DriverManager.getConnection( "jdbc:Informix-sqli://cleo:1550:
IFXHOST=cleo;PORTNO=1550;user=rdtest;password=my_passwd;IFX_ISOLATION_LEVEL=1U";);
Important: The isolation property can be set in the URL only when it is an explicit connection to a database. For server-only connection, this property is ignored at connection time.

Code example IFX_ISOLATION_LEVEL environment variable

Connection conn = DriverManager.getConnection( "jdbc:informix-sqli://localhost:9088
/csdk_db:user=informix;password=inform123;LOGINTIMEOUT=60000";);