Home
SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
Guide to SQL: Reference
The Informix® Guide to SQL: Reference contains the reference information for the system catalog tables, data types, and environment variables of the HCL Informix dialect of the SQL language, as implemented in HCL Informix. These topics also include information about the stores_demo, sales_demo, and superstore_demo databases that are included with HCL Informix.
Environment variables
Various environment variables affect the functionality of your HCL Informix® products. You can set environment variables that identify your terminal, specify the location of your software and define other parameters.
Environment variables in Informix® products
The topics that follow discuss (in alphabetic order) environment variables that HCL Informix® database server products and their utilities use.
DBDATE environment variable
Use the DBDATE environment variable to specify the end-user formats of DATE values.
DATE expressions in database objects
When an expression in a database object (including a check constraint, fragmentation expression, SPL routine, trigger, or UDR) contains a literal date value, the database server evaluates the expression using the setting that DBDATE (or other relevant environment variables) had when the database object was created (or was last modified). If DBDATE has been reset to a new value, the new value is ignored when the literal DATE is evaluated.

SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
- Guide to SQL: Syntax
- Guide to SQL: Reference
  The Informix® Guide to SQL: Reference contains the reference information for the system catalog tables, data types, and environment variables of the HCL Informix dialect of the SQL language, as implemented in HCL Informix. These topics also include information about the stores_demo, sales_demo, and superstore_demo databases that are included with HCL Informix.
  - System catalog tables
    The system catalog consists of tables and views that describe the structure of the database. Sometimes called the data dictionary, these table objects contain everything that the database knows about itself. Each system catalog table contains information about specific elements in the database. Each database has its own system catalog.
  - Data types
    Every column in a table in a database is assigned a data type. The data type precisely defines the kinds of values that you can store in that column.
  - Environment variables
    Various environment variables affect the functionality of your HCL Informix® products. You can set environment variables that identify your terminal, specify the location of your software and define other parameters.
    - Types of environment variables
    - Limitations on environment variables
    - Using environment variables on UNIX™
      You can set, unset, modify, and view environment variables. If you already use any HCL Informix® products, some or all of the appropriate environment variables might be set.
    - Using environment variables on Windows™
    - Environment variables in Informix® products
      The topics that follow discuss (in alphabetic order) environment variables that HCL Informix® database server products and their utilities use.
      - Environment variable portal
        This portal is an index of usage categories for HCL Informix® and UNIX™ environment variables. The portal contains links to the topics that describe the environment variables.
      - ANSIOWNER environment variable
        In an ANSI-compliant database, you can prevent the default behavior of upshifting lowercase letters in owner names that are not delimited by quotation marks by setting the ANSIOWNER environment variable to 1.
      - CPFIRST environment variable
        Use the CPFIRST environment variable to specify the default compilation order for all Informix® ESQL/C source files in your programming environment.
      - CMCONFIG environment variable
        Set the CMCONFIG environment variable to specify the location of the Connection Manager configuration file. You use the configuration file to specify service level agreements and other Connection Manager configuration options.
      - CLIENT_LABEL environment variable
        Set the CLIENT_LABEL environment variable in CSDK 4.10.xC10 or JDBC 4.10.JC10 client to assign a character string to CSDK or JDBC client session and identify that character string on the database server. You use this for environments where same userid runs multiple instances of the same application, and there is a need to distinguish one session from the other.
      - DBACCNOIGN environment variable
        Use the DBACCNOIGN environment variable to specify the behavior of the DB-Access utility when specified errors occurs.
      - DBANSIWARN environment variable
        Use the DBANSIWARN environment variable to indicate that you want to check for HCL Informix® extensions to ANSI-standard SQL syntax.
      - DBBLOBBUF environment variable
        Use the DBBLOBBUF environment variable to control whether TEXT or BYTE values are stored temporarily in memory or in a file while being processed by the UNLOAD statement. DBBLOBBUF affects only the UNLOAD statement.
      - DBCENTURY environment variable
        Use the DBCENTURY environment variable to specify how to expand literal DATE and DATETIME values that are entered with abbreviated year values. To avoid problems in expanding abbreviated years, applications should require entry of 4-digit years, and should always display years as four digits.
      - DBDATE environment variable
        Use the DBDATE environment variable to specify the end-user formats of DATE values.
        DATE expressions in database objects
        When an expression in a database object (including a check constraint, fragmentation expression, SPL routine, trigger, or UDR) contains a literal date value, the database server evaluates the expression using the setting that DBDATE (or other relevant environment variables) had when the database object was created (or was last modified). If DBDATE has been reset to a new value, the new value is ignored when the literal DATE is evaluated.
      - DBDELIMITER environment variable
        Set the DBDELIMITER environment variable to specify the field delimiter used with the dbexport utility and with the LOAD and UNLOAD statements.
      - DBEDIT environment variable
        Use the DBEDIT environment variable to specify the text editor to use with SQL statements and command files in DB-Access.
      - DBFLTMASK environment variable
      - DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM environment variable
        Use the DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM environment variable to specify if dbinfo('dbspace', partnum) raises an error -727 or returns NULL when an invalid partition number (partnum) is provided.
      - DBLANG environment variable
        Use the DBLANG environment variable to specify the subdirectory of $INFORMIXDIR or the full pathname of the directory that contains the compiled message files that HCL Informix® products use.
      - DBMONEY environment variable
        Use the DBMONEY environment variable to specify the display format of values in columns of smallfloat, FLOAT, DECIMAL, or MONEY data types, and of complex data types derived from any of these data types.
      - DBPATH environment variable
        Use the DBPATH environment variable to identify the database servers that contain databases. DBPATH can also specify a list of directories (in addition to the current directory) in which DB-Access looks for command scripts (.sql files).
      - DBPRINT environment variable
        Use the DBPRINT environment variable to specify the default printing program.
      - DBREMOTECMD environment variable (UNIX™)
        Use the DBREMOTECMD environment variable to override the default remote shell to perform remote tape operations with the database server.
      - DBSPACETEMP environment variable
        The DBSPACETEMP environment variable specifies the dbspaces in which temporary tables are built. The list can include standard dbspaces, temporary dbspaces, or both.
      - DBTEMP environment variable
      - DBTIME
      - DBUPSPACE environment variable
        Use the DBUPSPACE environment variable to specify the amount of system disk space and the amount of memory that the UPDATE STATISTICS MEDIUM and UPDATE STATISTICS HIGH statement can use when it reads and sorts column values to construct column distributions. The DBUPSPACE setting can also request SET EXPLAIN output to describe the execution path for calculating the statistical distributions.
      - DEFAULT_ATTACH environment variable
      - DELIMIDENT environment variable
        The DELIMIDENT environment variable specifies that strings enclosed between double quotation ( " ) marks are delimited database identifiers.
      - ENVIGNORE environment variable (UNIX™)
      - FET_BUF_SIZE environment variable
      - IFMXMONGOAUTH environment variable
        Set the IFMXMONGOAUTH environment variable to enable PAM authentication for MongoDB clients through the wire listener.
      - IFX_DEF_TABLE_LOCKMODE environment variable
      - IFX_DIRECTIVES environment variable
      - IFX_EXTDIRECTIVES environment variable
      - IFX_LARGE_PAGES environment variable
        The IFX_LARGE_PAGES environment variable specifies whether the database server can use large pages on platforms where the hardware and the operating system support large pages of shared memory. If this is enabled in the server environment, HCL Informix® can use the large pages for non-message shared memory segments that are located in physical memory.
      - IFX_LOB_XFERSIZE environment variable
        Use the IFX_LOB_XFERSIZE environment variable to specify the number of bytes in a CLOB or BLOB data type to transfer from a client application to the database server before checking whether an error has occurred.
      - IFX_LONGID environment variable
      - IFX_NETBUF_PVTPOOL_SIZE environment variable (UNIX™)
        Use the IFX_NETBUF_PVTPOOL_SIZE environment variable to specify the maximum size of the free (unused) private network buffer pool for each database server session.
      - IFX_NETBUF_SIZE environment variable
        Use the IFX_NETBUF_SIZE environment variable to configure the network buffers to the optimum size. This environment variable specifies the size of all network buffers in the free (unused) global pool and the private network buffer pool for each database server session.
      - IFX_NO_SECURITY_CHECK environment variable (UNIX™)
      - IFX_NO_TIMELIMIT_WARNING environment variable
      - IFX_NODBPROC environment variable
      - IFX_NOT_STRICT_THOUS_SEP environment variable
      - IFX_ONTAPE_FILE_PREFIX environment variable
      - IFX_PAD_VARCHAR environment variable
      - IFX_UPDDESC environment variable
      - IFX_XASTDCOMPLIANCE_XAEND environment variable
      - IFX_XFER_SHMBASE environment variable
      - INFORMIXC environment variable (UNIX™)
      - INFORMIXCMNAME environment variable
        If the Connection Manager raises an event alarm, the INFORMIXCMNAME environment variable is used to store the name of the Connection Manager instance that raised the alarm. The environment variable is set automatically by the Connection Manager.
      - INFORMIXCMCONUNITNAME environment variable
        If the Connection Manager raises an event alarm, the INFORMIXCMCONUNITNAME environment variable is used to store the name of the Connection Manager connection unit that raised the alarm. The environment variable is set automatically by the Connection Manager.
      - INFORMIXCONCSMCFG environment variable
        Use the INFORMIXCONCSMCFG environment variable to specify the location of the concsm.cfg file that describes communications support modules.
      - INFORMIXCONRETRY environment variable
        The INFORMIXCONRETRY environment variable sets a limit on the maximum number of connection attempts that can be made to each database server by the client after the initial connection attempt fails. These attempts are made within the time limit that the INFORMIXCONTIME setting specifies.
      - INFORMIXCONTIME environment variable
        The INFORMIXCONTIME environment variable specifies the number of seconds the CONNECT statement attempts to establish a connection to a database server before returning an error. If you set no value, the default of 60 seconds can typically support a few hundred concurrent client connections. However, some systems might encounter few connection errors with a value as low as 15. The total distance between nodes, hardware speed, the volume of traffic, and the concurrency level of the network can all affect what value you should set to optimize INFORMIXCONTIME.
      - INFORMIXCPPMAP environment variable
      - INFORMIXDIR environment variable
      - INFORMIXSERVER environment variable
        The INFORMIXSERVER environment variable specifies the default database server to which an explicit or implicit connection is made by an SQL API client, the DB-Access utility, or other HCL Informix® products.
      - INFORMIXSHMBASE environment variable (UNIX™)
      - INFORMIXSQLHOSTS environment variable
        The INFORMIXSQLHOSTS environment variable specifies where the SQL client or the database server can find connectivity information.
      - INFORMIXSTACKSIZE environment variable
      - INFORMIXTERM environment variable (UNIX™)
        The INFORMIXTERM environment variable specifies whether DB-Access should use the information in the terminfo directory or the termcap file.
      - INF_ROLE_SEP environment variable
      - INTERACTIVE_DESKTOP_OFF environment variable (Windows™)
      - JAR_TEMP_PATH environment variable
      - JAVA_COMPILER environment variable
      - JVM_MAX_HEAP_SIZE environment variable
      - LD_LIBRARY_PATH environment variable (UNIX™)
      - LIBPATH environment variable (UNIX™)
      - NODEFDAC environment variable
        Enabling NODEFDAC applies the ANSI-compliant restrictions on default access privileges for the PUBLIC group when tables or Owner-mode user-defined routines are created in databases that are not ANSI-compliant.
      - ONCONFIG environment variable
        The ONCONFIG environment variable specifies the name of the active file, called the onconfig file, which holds the configuration parameters for the database server.
      - ONINIT_STDOUT environment variable (Windows™)
        The ONINIT_STDOUT environment variable specifies a path and file name in which output from the oninit command is stored.
      - OPTCOMPIND environment variable
      - OPTMSG environment variable
      - OPTOFC environment variable
        Use the OPTOFC environment variable to enable optimize-OPEN-FETCH-CLOSE functionality in Informix® ESQL/C applications or other APIs (such as JDBC, ODBC, OLE DB, LIBDMI, and Lib C++) that use DECLARE and OPEN statements to establish a cursor.
      - OPT_GOAL environment variable (UNIX™)
      - PATH environment variable
      - PDQPRIORITY environment variable
        The PDQPRIORITY environment variable determines the degree of parallelism that the database server uses and affects how the database server allocates resources, including memory, processors, and disk reads.
      - PSM_ACT_LOG environment variable
        Use the PSM_ACT_LOG environment variable to specify the location of the Informix® Primary Storage Manager activity log for your environment, for example, for a single session.
      - PSM_CATALOG_PATH environment variable
        Use the PSM_CATALOG_PATH environment variable to specify the location of the Informix® Primary Storage Manager catalog tables for your environment, for example, for a single session.
      - PSM_DBS_POOL environment variable
        Use the PSM_DBS_POOL environment variable to change the name of the pool in which the Informix® Primary Storage Manager places backup and restore dbspace data for your environment, for example, for a single session.
      - PSM_DEBUG environment variable
        Use the PSM_DEBUG environment variable to specify the amount of debugging information that prints in the Informix® Primary Storage Manager debug log for your environment, for example, for a single session.
      - PSM_DEBUG_LOG environment variable
        Use the PSM_DEBUG_LOG environment variable to specify the location of the Informix® Primary Storage Manager debug log for your environment, for example, for a single session.
      - PSM_LOG_POOL environment variable
        Use the PSM_LOG_POOL environment variable to change the name of the pool in which the Informix® Primary Storage Manager places backup and restore log data for your environment, for example, for a single session.
      - PSORT_DBTEMP environment variable
        The PSORT_DBTEMP environment variable specifies the location where the database server writes the temporary files that the PSORT_NPROCS environment variable uses to perform a sort.
      - PSORT_NPROCS environment variable
        The PSORT_NPROCS environment variable enables the database server to improve the performance of the parallel-process sorting package by allocating more threads for sorting.
      - RTREE_COST_ADJUST_VALUE environment variable
      - SHLIB_PATH environment variable (UNIX™)
      - SRV_FET_BUF_SIZE environment variable
        Use the SRV_FET_BUF_SIZE environment variable to specify the size of the fetch buffer that the local database server uses in distributed DML transactions across database servers.
      - STMT_CACHE environment variable
        Use the STMT_CACHE environment variable to control the use of the shared-statement cache on a session.
      - TERM environment variable (UNIX™)
      - TERMCAP environment variable (UNIX™)
        The TERMCAP environment variable is used for terminal handling. It tells DB-Access (and other character-based applications) to communicate with the termcap file instead of the terminfo directory.
      - TERMINFO environment variable (UNIX™)
        The TERMINFO environment variable is used for terminal handling.
      - THREADLIB environment variable (UNIX™)
      - TZ environment variable
        The TZ environment variable is used for setting the time zone. It is used by various time functions to compute times relative to Coordinated Universal Time (UTC), formerly known as Greenwich Mean Time (GMT). The format is specified by the operating system.
      - USETABLENAME environment variable
  - Appendixes
- Guide to SQL: Tutorial
  The Informix® Guide to SQL: Tutorial shows how to use basic and advanced structured query language (SQL) to access and manipulate the data in your databases. It discusses the data manipulation language (DML) statements as well as triggers and stored procedure language (SPL) routines, which DML statements often use.

DATE expressions in database objects

When an expression in a database object (including a check constraint, fragmentation expression, SPL routine, trigger, or UDR) contains a literal date value, the database server evaluates the expression using the setting that DBDATE (or other relevant environment variables) had when the database object was created (or was last modified). If DBDATE has been reset to a new value, the new value is ignored when the literal DATE is evaluated.

For example, suppose DBDATE is set to MDY2/ and a user creates a table with the following check constraint on the column orderdate:

orderdate < '06/25/98'

The date of the preceding expression is formatted according to the value of DBDATE when the constraint is defined. The check constraint expression is interpreted as orderdate < '06/25/98' regardless of the value of DBDATE during inserts or updates on the orderdate column. Suppose DBDATE is reset to DMY2/ when a user inserts the value '30/01/98' into the orderdate column. The date value inserted uses the date format DMY2/, whereas the check constraint expression uses the date format MDY2/.

See Abbreviated years and expressions in database objects for a discussion of legacy objects from earlier versions of HCL Informix® that are always evaluated according to the runtime environment. That section describes how to redefine objects so that dates are interpreted according to environment variable settings that were in effect when the object was defined (or when the object was last modified).

Important: The behavior of DBDATE for HCL Informix® is not compatible with earlier versions.