Informix® environment variables with the HCL Informix® JDBC Driver and 'informix' protocol

The following table lists most of the HCL 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 connection properties with informix protocol.

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
HCL Informix® JDBC properties Default Value Description
appendIsamCodeToSqlException false When set to TRUE, the appendIsamCodeToSqlException parameter 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>)
autoCaseSchema false To instruct JDBC to automatically case schema (default = true) or when set to false, to return the schema as it was saved.
autoFreeCursors false When set to true, 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 property. After the cursor resources have been freed, the cursor can no longer be referenced. For more information, see The Auto Free feature with autoFreeCursors connection property.
batchInsertPreprocessing false When set to true, enables more efficient bulk inserts on data inserted with JDBC API calls for batched inserts. For more information, see Perform bulk inserts using informix protocol.
certificateVerification true Validate the certificates used for encrypted connections.
CLIENT_LOCALE The locale and the encoding of the characters which the client application wants to receive from the JDBC driver
commitBeforeIsolationChange false To instruct JDBC driver to automatically commit the current transaction if it detects that user is trying to change the transaction isolation level for the session.
connectionCleanerDelay 15000 Number of milliseconds for the cleaner thread to wait until it looks for more JDBC resources to clean up. Set to 0 or -1 to disable the cleaner thread.
customNLSMap Allows new mappings to be defined between NLS and Java development kit locales and code sets.

For more information, see User-defined locales.
database The database name to connect.
DB_LOCALE The locale and encoding of the database user is connecting to.
DBANSIWARN false When set to true, checks for Informix® extensions to ANSI-standard syntax
defaultCursorHoldability false Indicates the default cursor holdability. For more information, see Holdable result sets
delimident false When set to true, specifies that strings set off by double quotation marks are delimited identifiers
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.
udtCache true 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 false When set to TRUE, secondary server properties are used to connect to the secondary server if the primary server is unavailable.
resultsBufferSize 4096 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.
encrypt false

When set to true, enables the connection to use SSL/TLS encryption for communication with the server.
encryptionProtocols TLSv1.2,TLSv1.1,TLSv1

Override the encryption protocols presented to the server using a comm separated list of valid JDK protocols that are also supported by the database server.
trustStore Specifies the location of the truststore to load by the JDBC driver.
trustStorePassword Specifies the password to the truststore that is being loaded by the JDBC driver.
trustedContext false 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.)
host Specifies the host name or IP address to connect to.
invalidAutoCommitThrowError Throws an exception if user disables autocommit(enabling transactions) on a non-logged database.
lobBufferSize 16384 Size of buffer used to retrieve large objects from the server.
lobCodesetConversionMemory -1 The amount of memory used for conversion of character codesets when processing large objects before using temporary files for caching. 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 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 lobCodesetConversionMemory.
lobReadonly false Specifies whether BLOB/CLOB objects are forced to be read only or not.
lockTimeout -1
How long (in seconds) to wait on a lock on a server. 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.
loginRetries 0 Specifies the number of retires for establishing a connection to a server
loginTimeout 15 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.
metadataReplicationColumn false Indicate to the server and MetaData queries to use the fix_replcheck column.
metadataUppercaseValues false Uppercase values from ResultSetMetaData queries.
optOFC false When set to true, 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. HCL Informix® JDBC Driver might not have additional rows in the clients tuple buffer before the next ResultSet.next() method is called. Therefore, unless HCL 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 true.
padVarchar false Controls how data associated with a VARCHAR data type is transmitted to and from the Informix® server. When set to true, simulates a CHAR column from a VARCHAR by padding the end of VARCHAR column to the size of the column.
password Specifies the password that corresponds to the USER value set
port 9088 Specifies the port number to use for establishing a connection to the database server.
preparedStatementCacheSize 0
protocolTraceFile File path to generate a protocol level trace file for support teams. Only enables the trace file when directed by a technical support representative.
removeLobTempFilesOnRSClose false Remove temporary files used with large objects when the ResultSet is closed. If true when a ResultSet is closed, the temporary files are removed, and any references to BLOB/CLOB objects that reference those files will no longer function. If false, then the temporary files can persist until the connection is closed.
replaceUnmappableCharacterSequences false Specifies what is to be done if a character is not mappable from the database encoding to the client encoding. When set to true, the character should be replaced with the charset's default replacement string. When set to false, an error is thrown to indicate presence of unmappable characters.
secondaryServerName Specifies the secondary database server name for looing up a secondary server from an SQLHOSTS file in case of an HDR pair to which an explicit or implicit connection is made by a client application if the primary database server is unavailable.
secondarySwitch false When set to true, secondary server properties are used to connect to the secondary server if the primary server is unavailable.
secondaryHost Sets the secondary host name or host IP address for HDR connection redirection.
secondaryPort Specifies the port number of the secondary database server in an HDR pair. The port number is listed in the /etc/services file.
serverName Specifies the database server to which an explicit or implicit connection is made by a client application when looking up via an SQLHOSTS file.
sessionVariables

Informix server allows setting of many variables at the session (connection) level. Instead of executing a SET ENVIRONMENT SQL statement for each session variable that needs to be set, user can specify a comma separated list of session variables. End the list with a semi-colon as you would any other connection property. Below is an example of mixing in a number of session variables along with normal connection properties.

user=myuser;sessionVariables=AUTOLOCATE = '1',EXTDIRECTIVES=OFF , force_ddl_exec='OFF';password=mypassword

SQLH_LOC Specifies path to a client SQLHOSTS file which contains entries of servers the driver can connect to.
SQLH_TYPE When set to FILE, specifies that the database information (such as host-name, port-number, user, and password) is specified in an sqlhosts file. For more information, see Dynamically reading the Informix sqlhosts file (informix protocol).
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.
socketTimeout 0 - Wait forever Specifies how long to wait (in milliseconds) for a response on the TCP socket.
socketKeepAlive false Enable keep alive on the TCP socket connection.
tempDir Specifies where temporary files for handling smart large objects are created. You must supply an absolute path name.
transactionIsolationLevel Defines the degree of concurrency among processes that attempt to access the same rows simultaneously.
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. For example, a value could be: 2U (equivalent to SET ISOLATION TO COMMITTED READ RETAIN UPDATE LOCKS).
trimTrailingSpaces false Removes trailing spaces from character columns queried from the database server.
uppercaseMetaDataRSColumnNames false Return UPPERCASE column names in the link DatabaseMetaData ResultSet column names.
user false The user for to be authenticated.
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_FLAT_UCSQ Overrides the global setting and directs the optimizer to use subquery flattening for all sessions. Default value is 1.
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_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.
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
INFORMIXSTACKSIZE Specifies the stack size, in kilobytes, that the database server uses for a particular client session.
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
PATH Specifies the directories that are to be searched for executable programs
PDQPRIORITY Determines the degree of parallelism used by the database server
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.
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.

For a detailed description of a particular property, see HCL® Informix® Guide to SQL: Reference.

Code example lockTimeout property

lockTimeout
Connection conn = DriverManager.getConnection ( "jdbc:informix://localhost:9080/stores_demo;user=rdtest;password=my_password;;lockTimeout=15");

Code example transactionIsolationLevel property

transactionIsolationLevel
Connection conn = DriverManager.getConnection( "jdbc:Informix://localhost:9088;user=rdtest;password=my_passwd;transactionIsolationLevel=1U");
Important: The isolation property can be set in the URL only when it is an explicit connection to a database.