Welcome
SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
Guide to SQL: Reference
These topics contain 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.
IMCSERVER environment variable

HCL Informix® V15.0 documentation
Welcome to the documentation for HCL Informix® 15.0 and related client tools and products.
Product overview
HCL Informix® is a fast and scalable database server that manages traditional relational, object-relational, and dimensional databases. Its small footprint and self-managing capabilities are suited to embedded data-management solutions.
Installing
These topics describe how to install HCL Informix® database servers, client products, and DataBlade® modules.
Administering
In addition to administering the database server, you can tune performance, replicate data, and archive data.
Migrating and upgrading
You can upgrade to the 15.0 release of HCL Informix® or migrate from other database servers to Informix. Upgrading is an in-place migration method that uses your existing hardware and operating system software. Some changes to the Informix database server can affect upgrading from a previous release.
Client APIs and tools
You can use the HCL Informix® implementation of client APIs to develop applications for Informix database servers.
Embedding Informix®
When you embed HCL Informix®, you can use enterprise-class high-availability and high performance with embeddability features such as easy programmability, a small disk and memory footprint, and silent deployment.
Extending Informix®
Beyond standard relational database objects, HCL Informix® can be extended to handle specialized data types, access methods, routines, and other objects. Informix includes many built-in extensions that are fully integrated in the database server. Informix also provides DataBlade® modules, which are packages of extended database objects for a particular purpose and that are installed separately from the database server. Alternatively, you can create your own user-defined objects for Informix.
Data warehousing
In addition to designing and implementing Informix® dimensional databases, you can use tools to create data warehouse applications and optimize your data warehouse queries.
Designing databases
The first step in creating a relational database is to construct a data model, which is a precise, complete definition of the data you want to store. After you prepare your data model, you must implement it as a database and tables. To implement your data model, you first select a data type for each column and then you create a database and tables and populate the tables with data. You can also implement fragmentation strategies and control access to your data.
JSON compatibility
You can use the popular JSON-oriented query language created by MongoDB to interact with data stored in HCL Informix®.
Security
You can secure your Informix® database server and the data that is stored in your Informix databases. You can encrypt data, secure connections, control user privileges and access, and audit data security.
SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
- Guide to SQL: Syntax
  These topics describe the syntax of the statements, data types, expressions, operators, and built-in functions of the Informix® dialect of the SQL language.
- Guide to SQL: Reference
  These topics contain 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 IBM® 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.
      - 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
      - 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_UNLOAD_EILSEQ_MODE environment variable
        Use the IFX_UNLOAD_EILSEQ_MODE environment variable to help migrate databases from Informix® Version 10 to Version 11.50 or 11.70, where character data might be encoded with a codeset that is different than the codeset used to create the Version 10 database.
      - IFX_UPDDESC environment variable
      - IFX_VALIDATE_STORAGEPOOLPATH environment variable
      - IFX_XASTDCOMPLIANCE_XAEND environment variable
      - IFX_XFER_SHMBASE environment variable
      - IMCADMIN environment variable
      - IMCCONFIG environment variable
      - IMCSERVER 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
      - INFORMIXOPCACHE 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™)
      - ISM_COMPRESSION environment variable
        Use the ISM_COMPRESSION environment variable to specify whether the IBM® Informix® Storage Manager (ISM) should use a data-compression algorithm to store and retrieve data.
      - ISM_DEBUG_FILE environment variable
        Use the ISM_DEBUG_FILE environment variable in the IBM® Informix® Storage Manager (ISM) server environment to specify where to write XBSA messages.
      - ISM_DEBUG_LEVEL environment variable
        Use the ISM_DEBUG_LEVEL environment variable in the ON-Bar environment to control the level of reporting detail recorded in the XBSA messages log. The XBSA shared library writes to this log.
      - ISM_ENCRYPTION environment variable
        Use the ISM_ENCRYPTION environment variable in the ON-Bar environment to specify whether IBM® Informix® Storage Manager (ISM) uses data encryption.
      - ISM_MAXLOGSIZE environment variable
        Use the ISM_MAXLOGSIZE environment variable in the IBM® Informix® Storage Manager (ISM) server environment to specify the size threshold of the ISM activity log.
      - ISM_MAXLOGVERS environment variable
        Use the ISM_MAXLOGVERS environment variable in the IBM® Informix® Storage Manager (ISM) server environment to specify the maximum number of activity-log files to be preserved by the ISM server.
      - 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 IBM® 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.
      - PLCONFIG environment variable
      - PLOAD_LO_PATH environment variable
      - PLOAD_SHMBASE environment variable
      - PSM_ACT_LOG environment variable
        Use the PSM_ACT_LOG environment variable to specify the location of the HCL 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 HCL 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 HCL 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 HCL 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 HCL 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 (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
  This publication 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.
Troubleshooting HCL Informix®
Several troubleshooting techniques, tools, and resources are available for resolving problems that you encounter in your HCL Informix® database server environment.

IMCSERVER environment variable

The IMCSERVER environment variable specifies the name of a database server entry in the sqlhosts file that contains information about connectivity.

The database server can be either local or remote. On UNIX™ systems that support the C shell, the IMCSERVER environment variable can be set by the command.

dbservername: is the valid name of a database server.

Here dbservername must be the name of a database server in the sqlhosts file. For more information about sqlhosts settings with MaxConnect, see your HCL® Informix® Administrator's Guide. You cannot use MaxConnect unless IMCSERVER is set to a valid database server name.