Home | Previous Page | Next Page   Connecting to the Database >

Using Informix Environment Variables

The following table lists most of the Informix environment variables supported by the client JDBC driver. For server-side JDBC, you should 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 Specifying Properties.

For a list of environment variables that provide internationalization features, see Internationalization and Date Formats. For a list of environment variables useful for troubleshooting, see Tuning and Troubleshooting

Supported Informix Environment Variables Description
BIG_FET_BUF_SIZE In IBM Informix Extended Parallel Server, Version 8.4, overrides the default size of the tuple buffer and allows it to be increased up to 2GB.
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.
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 pathname of the directory into which you want IBM Informix Enterprise 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 system disk space that the UPDATE STATISTICS statement can use when it simultaneously constructs multiple-column distributions
DELIMIDENT When set to Y, specifies that strings set off by double quotes are delimited identifiers
ENABLE_CACHE_TYPE When set to 1, caches the data type information for opaque, distinct, or row data.
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 in an HDR pair, 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 Using 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 Converting Using 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 backward compatibility with older 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 Informix-specific variable IFX_ISOLATION_LEVEL. The default value is 1 (Committed Read). If the value has been set explicitly, it returns the set value. Returns: integer.
Sets the value of Informix-specific variable IFX_ISOLATION_LEVEL. Possible values:
  • 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)
Specifying U after the mode means retain update locks. (See Important note following table.) For example, a value could be: 2U (equivalent to SET ISOLATION TO COMMITTED READ RETAIN UPDATE LOCKS.
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 Informix-specific variable IFX_LOCK_MODE_WAIT. 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 Informix-specific variable IFX_LOCK_MODE_WAIT. 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_PADVARCHAR Controls how data associated with a VARCHAR data type is transmitted to and from a Dynamic Server 9.4 or later 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 backward compatibility with older versions of IBM Informix JDBC Driver.
IFX_USEPUT When set to 1, enables bulk inserts. For more information, see Performing 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 Appendix B. DataSource Extensions.) or by setting a System (JVM) property with the same name. The DataSource property will override 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 will be returned.
IFX_XASTDCOMPLIANCE_XAEND Specifies the behavior of XA_END when XA_RB* is returned.
1
XID is not forgotten. Transaction is in Rollback Only state. This is XA_SPEC+ compliant and is the default behavior with IDS 10.0.
2
XID is forgotten. Transaction is Nonexistent. This is default behavior with IDS 9.40.

For more information, see IBM Informix: Guide to SQL Reference

DISABLE_B162428_XA_FIX (IDS 10.0)
ENABLE_B162428_XA_FIX (IDS 9.40)
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
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 pathname.
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 (0). 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.
NEWNLSMAP Allows new mappings between NLS and JDK locales and JDK codesets to be defined
For more information, see User-Defined Locales.
NODEFDAC When set to YES, prevents default table and routine privileges from being granted to the PUBLIC user when a new table or routine is created in a database that is not ANSI compliant. Default is NO.
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 client's 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 client's 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 should 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 pathname 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 Using 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 Using 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.
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.
USEV5SERVER When set to 1, specifies that the Java program is connecting to an IBM Informix OnLine 5.x or IBM Informix SE 5.x or IBM Informix SE 7.x database server.
This environment variable is mandatory if you are connecting to an IBM Informix OnLine 5.x or IBM Informix SE 5.x or IBM Informix SE 7.x database server.

Important:
RETAIN UPDATE LOCKS is not supported in IBM Informix Dynamic Server, Version 5.x. The U option will be ignored when connecting to a 5.x server.

The following are code examples of the IFX_LOCK_MODE_WAIT and IFX_ISOLATION_LEVEL environment variables: 

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

Connection conn = DriverManager.getConnection( "jdbc:Informix-sqli://cleo:1550:
INFORMIXSERVER=cleo_921;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.

For a detailed description of a particular environment variable, refer to IBM Informix: Guide to SQL Reference. You can find the online version of this guide at http://www.ibm.com/software/data/informix/pubs/library/.

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]