Welcome
SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
Guide to SQL: Syntax
The HCL® Informix® Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the Informix dialect of the SQL language.
SQL statements
This chapter describes the syntax and semantics of SQL statements that are recognized by Informix®.
CREATE INDEX statement
Use the CREATE INDEX statement to create an index for one or more columns in a table, or on values returned by a UDR that uses column values as arguments.
VISIBLE AND INVISIBLE options for indexes
Visibility option for indexes decides availability of the index to the optimizer during query optimization.

HCL Informix® V15.0.0 documentation
Welcome to the documentation for HCL Informix® 15.0.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 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.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 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
  The HCL® Informix® Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the Informix dialect of the SQL language.
  - Overview of SQL syntax
    These topics provide an overview about how to use the SQL statements, SPL statements, and syntax segments.
  - SQL statements
    This chapter describes the syntax and semantics of SQL statements that are recognized by Informix®.
    - ALLOCATE COLLECTION statement
      Use the ALLOCATE COLLECTION statement to allocate memory for a variable of a collection data type (such as LIST, MULTISET, or SET) or for an untyped collection variable.
    - ALLOCATE DESCRIPTOR statement
      Use the ALLOCATE DESCRIPTOR statement to declare the name and allocate memory for a system-descriptor area (SDA). Use this statement with ESQL/C.
    - ALLOCATE ROW statement
      Use the ALLOCATE ROW statement to allocate memory for a row variable. This statement is an extension to the ANSI/ISO standard for SQL. Use this statement with ESQL/C.
    - ALTER ACCESS_METHOD statement
      Use the ALTER ACCESS_METHOD statement to change one or more attributes of a user-defined primary or secondary access method in the sysams system catalog table.
    - ALTER FRAGMENT statement
      Use the ALTER FRAGMENT statement to change the distribution strategy or the storage location of an existing table or index. This statement is an extension to the ANSI/ISO standard for the SQL language.
    - ALTER FUNCTION statement
      Use the ALTER FUNCTION statement to change the routine modifiers or pathname of a user-defined function. This statement is an extension to the ANSI/ISO standard for SQL.
    - ALTER INDEX statement
      Use the ALTER INDEX statement to change the clustering attribute of an existing index. This statement is an extension to the ANSI/ISO standard for SQL.
    - ALTER PROCEDURE statement
      Use the ALTER PROCEDURE statement to change the routine modifiers or pathname of a previously defined external procedure. This statement is an extension to the ANSI/ISO standard for SQL.
    - ALTER ROUTINE statement
      Use the ALTER ROUTINE statement to change the routine modifiers or pathname of a previously defined user-defined routine (UDR), This statement is an extension to the ANSI/ISO standard for SQL.
    - ALTER SECURITY LABEL COMPONENT statement
      Use the ALTER SECURITY LABEL COMPONENT statement to add one or more new elements to an existing security label component in the current database. This statement is an extension to the ANSI/ISO standard for SQL.
    - ALTER SEQUENCE statement
      Use the ALTER SEQUENCE statement to modify the definition of a sequence object. This statement is an extension to the ANSI/ISO standard for SQL.
    - ALTER TABLE statement
      Use the ALTER TABLE statement to modify the schema of an existing table.
    - ALTER TRUSTED CONTEXT
      Use the ALTER TRUSTED CONTEXT statement to modify the current options and attributes (including the ENABLED or DISABLED mode) of a trusted-context object.
    - ALTER USER statement (UNIX™, Linux™)
      Use the ALTER USER statement to change one or more of the properties, including the password, user ID, surrogate group, administrative authorization, and home directory, and to enable or disable the account of an internally authenticated user, or of the default internally authenticated user.
    - BEGIN WORK statement
      Use the BEGIN WORK statement to start a transaction, which is a series of database operations that the COMMIT WORK or ROLLBACK WORK statement terminates, and that the database server treats as a single unit of work. This statement is an extension to the ANSI/ISO standard for SQL.
    - CLOSE statement
      Use the CLOSE statement when you no longer need to refer to the set of rows associated with a Select cursor or with a Function cursor. With ESQL/C, this statement can also flush and close an Insert cursor. Use this statement with or SPL.
    - CLOSE DATABASE statement
      Use the CLOSE DATABASE statement to close the implicit connection to the current database. This statement is an extension to the ANSI/ISO standard for SQL.
    - COMMIT WORK statement
      Use the COMMIT WORK statement to commit all modifications made to the database from the beginning of a transaction.
    - CONNECT statement
      Use the CONNECT statement to connect to a database environment. This statement is an extension to the ANSI/ISO standard for SQL.
    - CREATE ACCESS_METHOD statement
      Use the CREATE ACCESS_METHOD statement to register a new primary or secondary access method in the sysams system catalog table.
    - CREATE AGGREGATE statement
      Use the CREATE AGGREGATE statement to create a new aggregate function and register it in the sysaggregates system catalog table.
    - CREATE CAST statement
      Use the CREATE CAST statement to register a cast that converts data from one data type to another.
    - CREATE DATABASE statement
      Use the CREATE DATABASE statement to create a new database.
    - CREATE DEFAULT USER statement (UNIX™, Linux™)
      Use the CREATE DEFAULT USER statement to define the properties set of the default internally authenticated user. This statement is an extension to the ANSI/ISO standard for the SQL language.
    - CREATE DISTINCT TYPE statement
      Use the CREATE DISTINCT TYPE statement to create a new distinct data type.
    - CREATE EXTERNAL TABLE Statement
      Use the CREATE EXTERNAL TABLE statement to define an external source that is not part of your database to load and unload data for your database.
    - CREATE FUNCTION statement
      Use the CREATE FUNCTION statement to create a user-defined function, to register an external function, or to write and register an SPL function.
    - CREATE FUNCTION FROM statement
      Use the CREATE FUNCTION FROM statement to access a user-defined function whose CREATE FUNCTION statement resides in a separate file.
    - CREATE INDEX statement
      Use the CREATE INDEX statement to create an index for one or more columns in a table, or on values returned by a UDR that uses column values as arguments.
      - Index-type options
        Use the DISTINCT or UNIQUE and CLUSTER options of the CREATE INDEX statement to specify the characteristics of the index.
      - Index-key specification
        Use the Index-key specification of the CREATE INDEX statement to define the key value for the index. This can also specify the ascending or descending sort order, and the operator class.
      - Creating Composite Indexes
      - Using the ASC and DESC Sort-Order Options
      - USING Access-Method Clause (IDS)
      - HASH ON clause
        Use the HASH ON clause of the CREATE INDEX statement to specify the columns and number of subtrees (buckets) for a forest of trees index.
      - FILLFACTOR Option
      - Storage options
        The storage options specify the distribution scheme of an index. You can use the IN clause to specify a storage space for the entire index, or you can use the FRAGMENT BY clause to fragment the index across multiple storage spaces.
      - COMPRESSED option for indexes
        Use the COMPRESSED keyword of the CREATE INDEX statement to compress the B-tree index if the index has 2000 or more keys.
      - Extent Size Options
      - IN Clause
      - FRAGMENT BY Clause for Indexes
        Use the FRAGMENT BY clause to create a detached index and to define its fragmentation strategy across dbspaces or partitions.
      - Index modes
        Use the index mode options of the CREATE INDEX statement to specify the behavior of the index during INSERT, DELETE, MERGE, and UPDATE operations.
      - How the Database Server Treats Disabled Indexes
      - The ONLINE keyword of CREATE INDEX
        The DBA can reduce the risk of nonexclusive access errors, and can increase the availability of the indexed table, by including the ONLINE keyword as the last specification of the CREATE INDEX statement. The ONLINE keyword instructs the database server to create the index while minimizing the duration of an exclusive lock, so that the index can be created while concurrent users are accessing the table.
      - Automatic Calculation of Distribution Statistics
      - VISIBLE AND INVISIBLE options for indexes
        Visibility option for indexes decides availability of the index to the optimizer during query optimization.
    - CREATE OPAQUE TYPE statement
      Use the CREATE OPAQUE TYPE statement to create an opaque data type.
    - CREATE OPCLASS statement
      Use the CREATE OPCLASS statement to create an operator class for a secondary-access method.
    - CREATE PROCEDURE statement
      Use the CREATE PROCEDURE statement to create a user-defined procedure. (To create a procedure from text of source code that is in a separate file, use the CREATE PROCEDURE FROM statement.)
    - CREATE PROCEDURE FROM statement
      Use the CREATE PROCEDURE FROM statement to access a user-defined procedure. The actual text of the CREATE PROCEDURE statement resides in a separate file.
    - CREATE ROLE statement
      Use the CREATE ROLE statement to declare and register a new role.
    - CREATE ROUTINE FROM statement
      Use the CREATE ROUTINE FROM statement to register a UDR by referencing the text of a CREATE FUNCTION statement or CREATE PROCEDURE statement that resides in a separate file.
    - CREATE ROW TYPE statement
      Use the CREATE ROW TYPE statement to create a named ROW type.
    - CREATE SCHEMA statement
      Use the CREATE SCHEMA statement to issue a block of data definition language (DDL) and GRANT statements as a unit.
    - CREATE SECURITY LABEL statement
      Use the CREATE SECURITY LABEL statement to define a new security label for a specified security policy in the current database and to identify its components and the elements of its components.
    - CREATE SECURITY LABEL COMPONENT statement
      Use the CREATE SECURITY LABEL COMPONENT statement to define a new security label component in the current database and to define the elements the component.
    - CREATE SECURITY POLICY statement
      Use the CREATE SECURITY POLICY statement to define a new security policy in the current database and to identify its security label components and access rules.
    - CREATE SEQUENCE statement
      Use the CREATE SEQUENCE statement to create a sequence database object from which multiple users can generate unique integers.
    - CREATE SYNONYM statement
      Use the CREATE SYNONYM statement to declare and register an alternative name for an existing table, view, or sequence object.
    - CREATE TABLE statement
      Use the CREATE TABLE statement to create a new permanent table in the current database.
    - CREATE TEMP TABLE statement
      Use the CREATE TEMP TABLE statement to create a temporary table in the current database.
    - CREATE TRIGGER statement
      Use the CREATE TRIGGER statement to define a trigger on a table. You can also use CREATE TRIGGER to define an INSTEAD OF trigger on a view.
    - CREATE TRUSTED CONTEXT
      Use the CREATE TRUSTED CONTEXT statement to define a newtrusted-context object. This statement is an extension to the ANSI/ISO standard for the SQL language.
    - CREATE USER statement (UNIX™, Linux™)
      Use the CREATE USER statement to define internally authenticated users, or to map externally authenticated users to surrogate user properties required for access to Informix® resources.
    - CREATE VIEW statement
      Use the CREATE VIEW statement to create a new view that is based on one or more existing tables and views that reside in the database, or in another database of the local database server or of a different database server.
    - CREATE XADATASOURCE statement
      Use the CREATE XADATASOURCE statement to create a new XA-compliant data source and create an entry for it in the sysxadatasources system catalog table. This statement is an extension to the ANSI/ISO standard for SQL.
    - CREATE XADATASOURCE TYPE statement
      Use the CREATE XADATASOURCE TYPE statement to create a new XA-compliant data source type and create an entry for it in the sysxasourcetypes system catalog table. This statement is an extension to the ANSI/ISO standard for SQL.
    - DATABASE statement
      Use the DATABASE statement to open an accessible database as the current database. This statement is an extension to the ANSI/ISO standard for SQL.
    - DEALLOCATE COLLECTION statement
      Use the DEALLOCATE COLLECTION statement to release memory for a collection variable that was previously allocated with the ALLOCATE COLLECTION statement.
    - DEALLOCATE DESCRIPTOR statement
      Use the DEALLOCATE DESCRIPTOR statement to free a previously allocated, system-descriptor area. Use this statement with .
    - DEALLOCATE ROW statement
      Use the DEALLOCATE ROW statement to release memory for a ROW variable.
    - DECLARE statement
      Use the DECLARE statement of dynamic SQL to declare a cursor and to associate it with an SQL statement that returns a set of rows to an SPL or or routine.
    - DELETE statement
      Use the DELETE statement to delete one or more rows from a table, or to delete one or more elements from a collection variable of SPL or of .
    - DESCRIBE statement
      Use the DESCRIBE statement to obtain information about output parameters and other features of a prepared object before you execute it.
    - DESCRIBE INPUT statement
      Use the DESCRIBE INPUT statement to return input parameter information before a prepared statement is executed.
    - DISCONNECT statement
      Use the DISCONNECT statement to terminate a connection between an application and a database server.
    - DROP ACCESS_METHOD statement
      Use the DROP ACCESS_METHOD statement to remove a previously defined primary or secondary access method from the database.
    - DROP AGGREGATE statement
      Use the DROP AGGREGATE statement to drop a user-defined aggregate that you created with the CREATE AGGREGATE statement.
    - DROP CAST statement
      Use the DROP CAST statement to remove an existing cast from the database.
    - DROP DATABASE statement
      Use the DROP DATABASE statement to delete an entire database, including all system catalog tables, objects, and data.
    - DROP FUNCTION statement
      Use the DROP FUNCTION statement to remove a user-defined function from the database. This statement is an extension to the ANSI/ISO standard for SQL.
    - DROP INDEX statement
      Use the DROP INDEX statement to remove an index.
    - DROP OPCLASS statement
      Use the DROP OPCLASS statement to remove an existing operator class from the database.
    - DROP PROCEDURE statement
      Use the DROP PROCEDURE statement to drop a user-defined procedure from the database. This statement is an extension to the ANSI/ISO standard for SQL.
    - DROP ROLE statement
      Use the DROP ROLE statement to remove a user-defined role from the database.
    - DROP ROUTINE statement
      Use the DROP ROUTINE statement to remove a user-defined routine (UDR) from the database.
    - DROP ROW TYPE statement
      Use the DROP ROW TYPE statement to remove an existing named ROW data type from the database.
    - DROP SECURITY statement
      Use the DROP SECURITY statement to remove an existing security object from the current database. The object can be a security policy, security label, or a security label component.
    - DROP SEQUENCE statement
      Use the DROP SEQUENCE statement to remove a sequence object from the database.
    - DROP SYNONYM statement
      Use the DROP SYNONYM statement to unregister an existing synonym.
    - DROP TABLE statement
      Use the DROP TABLE statement to remove a table with its associated indexes and data. This statement is an extension to the ANSI/ISO standard for SQL.
    - DROP TRIGGER statement
      Use the DROP TRIGGER statement to remove a trigger definition from the database.
    - DROP TRUSTED CONTEXT statement
      Use the DROP TRUSTED CONTEXT statement to destroy a trusted-context object.
    - DROP TYPE statement
      Use the DROP TYPE statement to remove a user-defined distinct or opaque data type from the database. (You cannot use this statement to remove a built-in data type or a ROW data type.)
    - DROP USER statement (UNIX™, Linux™)
      Use the DROP USER statement to remove an internal user.
    - DROP VIEW statement
      Use the DROP VIEW statement to remove a view from the database.
    - DROP XADATASOURCE statement
      Use the DROP XADATASOURCE statement to drop a previously defined XA-compliant data source from the system catalog of the database.
    - DROP XADATASOURCE TYPE statement
      Use the DROP XADATASOURCE TYPE statement to drop a previously defined XA-compliant data source type from the database.
    - EXECUTE statement
      Use the EXECUTE statement to run a previously prepared statement or a multiple-statement prepared object.
    - EXECUTE FUNCTION statement
      Use the EXECUTE FUNCTION statement to invoke a user-defined function or a built-in routine that returns a value.
    - EXECUTE IMMEDIATE statement
      Use the EXECUTE IMMEDIATE statement to perform tasks equivalent to what the PREPARE, EXECUTE, and FREE statements accomplish, but as a single operation.
    - EXECUTE PROCEDURE statement
      Use the EXECUTE PROCEDURE statement to invoke a user-defined procedure or a built-in routine. This statement is an extension to the ANSI/ISO standard for SQL.
    - FETCH statement
      Use the FETCH statement to move a cursor to a new row in the active set and to retrieve the row values from memory.
    - FLUSH statement
      Use the FLUSH statement to force rows that a PUT statement buffered to be written to the database.
    - FREE statement
      Use the FREE statement to release resources that are allocated to a prepared statement or to a cursor.
    - GET DESCRIPTOR
    - GET DIAGNOSTICS statement
      Use the GET DIAGNOSTICS statement to return diagnostic information about the most recently executed SQL statement.
    - GRANT statement
      Use the GRANT statement to assign access privileges and roles to users and to other roles. Users who hold the DBSECADM role can use this statement to assign user security labels and exemptions from label-based access control (LBAC) security rules.
    - GRANT FRAGMENT statement
      Use the GRANT FRAGMENT statement to assign privileges on table fragments in the local database if the table is fragmented by expression.
    - INFO statement
      Use the INFO statement to list the names of all the user-defined tables in the current database, or to display information about a specific table.
    - INSERT statement
      Use the INSERT statement to insert one or more new rows into a table or view, or to insert one or more elements into an SQL or collection variable.
    - LOAD statement
      Use the LOAD statement to insert data from an operating-system file into an existing table or view.
    - LOCK TABLE statement
      Use the LOCK TABLE statement to control access to a table by other processes.
    - MERGE statement
      Use the MERGE statement to transfer data from a source table into a target table by combining UPDATE or DELETE operations with INSERT operations in a single SQL statement. You can also use this statement to join the source and target tables, and then perform only UPDATE operations, only DELETE operations, or only INSERT operations on the target table.
    - OPEN statement
      Use the OPEN statement to activate a cursor.
    - OUTPUT statement
      Use the OUTPUT statement to send the results of a query to an operating-system file or to a program.
    - PREPARE statement
      Use the PREPARE statement to parse, validate, and generate an execution plan for one or more SQL statements at runtime.
    - PUT statement
      Use the PUT statement to store a row in an insert buffer for later insertion into the database.
    - RELEASE SAVEPOINT statement
      Use the RELEASE SAVEPOINT statement to destroy a specified savepoint. The RELEASE SAVEPOINT statement is compliant with the ANSI/ISO standard for SQL.
    - RENAME COLUMN statement
      Use the RENAME COLUMN statement to change the name of a column. The RENAME COLUMN statement is an extension to the ANSI/ISO standard for SQL.
    - RENAME CONSTRAINT statement
      Use the RENAME CONSTRAINT statement to rename the constraint. This statement is an extension to the ANSI/ISO standard for SQL.
    - RENAME DATABASE statement
      Use the RENAME DATABASE statement to change the name of a database. This statement is an extension to the ANSI/ISO standard for SQL.
    - RENAME INDEX statement
      Use the RENAME INDEX statement to change the name of an existing index. This statement is an extension to the ANSI/ISO standard for SQL.
    - RENAME SECURITY statement
      Use the RENAME SECURITY statement to change the name of an existing security object. The object can be a security policy, or a security label, or a security label component.
    - RENAME SEQUENCE statement
      Use the RENAME SEQUENCE statement to change the name of a sequence. This statement is an extension to the ANSI/ISO standard for SQL.
    - RENAME TABLE statement
      Use the RENAME TABLE statement to change the name of a table. The RENAME TABLE statement is an extension to the ANSI/ISO standard for SQL.
    - RENAME TRUSTED CONTEXT statement
      Use the RENAME TRUSTED CONTEXT statement to change the name of a trusted-context object.
    - RENAME USER statement (UNIX™, Linux™)
      Use the RENAME USER statement to change the name of an internal user of a non-root installation of the database server.
    - REVOKE statement
      Use the REVOKE statement to cancel access privileges or roles that are held by users, by roles, or by PUBLIC, or to cancel user security labels or exemptions from the rules of security policies.
    - REVOKE FRAGMENT statement
      Use the REVOKE FRAGMENT statement to revoke from one or more users or roles the Insert, Update, or Delete fragment-level privileges that were granted on individual fragments of a table that has been fragmented by expression. This statement is an extension to the ANSI/ISO standard for SQL.
    - ROLLBACK WORK statement
      Use the ROLLBACK WORK statement to cancel all or part of the current transaction intentionally, undoing any changes that occurred since the beginning of the transaction, or between the ROLLBACK WORK statement and a specified or default savepoint.
    - SAVE EXTERNAL DIRECTIVES statement
      Use the SAVE EXTERNAL DIRECTIVES statement to create external optimizer directives for a specified query, and save the directives in the database. These directives are applied automatically to subsequent instances of the same query. This statement is an extension to the ANSI/ISO standard for SQL.
    - SAVEPOINT statement
      Use the SAVEPOINT statement to declare the name of a new savepoint within the current SQL transaction, and to set the position of the new savepoint within the lexical order of SQL statements within the transaction. The SAVEPOINT statement is compliant with the ANSI/ISO standard for SQL.
    - SELECT statement
      Use the SELECT statement to retrieve values from a database or from an SPL or collection variable. A SELECT operation is called a query.
    - SET AUTOFREE statement
      Use the SET AUTOFREE statement to instruct the database server to enable or disable a memory-management feature that can free the memory allocated for a cursor automatically, as soon as the cursor is closed.
    - SET COLLATION statement
      Use the SET COLLATION statement to specify a new collating order for the session, superseding the collation implied by the DB_LOCALE environment variable setting. SET NO COLLATION restores the default collation.
    - SET CONNECTION statement
      Use the SET CONNECTION statement to reestablish a connection between an application and a database environment and to make the connection current. You can also use this statement with the DORMANT option to put the current connection in a dormant state. Use this statement with .
    - SET CONSTRAINTS statement
      Use the SET CONSTRAINTS statements to change how some or all of the existing constraints on a table are processed.
    - SET Database Object Mode statement
      Use the SET Database Object Mode statement to change the filtering mode of constraints and of unique indexes, or to enable or disable constraints, indexes, and triggers, or to make user created indexes visible/invisible, or to bypass referential-integrity checking of constraints while this statement is resetting the constraint mode.
    - SET DATASKIP statement
      Use the SET DATASKIP statement to control whether the database server skips a dbspace that is unavailable during the processing of a transaction.
    - SET DEBUG FILE statement
      Use the SET DEBUG FILE statement to identify the file that is to receive the runtime trace output of an SPL routine.
    - SET DEFERRED_PREPARE statement
      Use the SET DEFERRED_PREPARE statement to control whether a client process postpones sending a PREPARE statement to the database server until the OPEN or EXECUTE statement is sent.
    - SET DESCRIPTOR statement
    - SET ENCRYPTION PASSWORD statement
    - SET ENVIRONMENT statement
    - SET EXPLAIN statement
      Use the SET EXPLAIN statement to enable or disable the recording measurements of queries in the current session, including the plan of the query optimizer, an estimate of the number of rows returned, and the relative cost of the query.
    - SET INDEXES statement
      Use the SET INDEXES statement to enable or disable a user-defined index, to change the filtering mode of a unique index, or to make an index visible or invisible.
    - SET ISOLATION statement
      Use the SET ISOLATION statement to define the degree of concurrency among processes that attempt to access the same rows simultaneously.
    - SET LOCK MODE statement
      Use the SET LOCK MODE statement to define how the database server handles a process that tries to access a locked row or table.
    - SET LOG statement
      Use the SET LOG statement to change your database logging mode from buffered transaction logging to unbuffered transaction logging or vice versa.
    - SET OPTIMIZATION statement
      Use the SET OPTIMIZATION statement to specify how much time the query execution optimizer spends developing a query plan or specifying optimization goals. The SET OPTIMIZATION statement is an extension to the ANSI/ISO standard for SQL
    - SET PDQPRIORITY statement
      The SET PDQPRIORITY statement enables an application to set the query priority level dynamically within a routine. The SET PDQPRIORITY statement is an extension to the ANSI/ISO standard for SQL.
    - SET ROLE statement
      Use the SET ROLE statement to enable the privileges of a user-defined role. This statement is an extension to the ANSI/ISO standard for SQL.
    - SET SESSION AUTHORIZATION statement
      The SET SESSION AUTHORIZATION statement lets you change the user name under which database operations are performed in the current session.
    - SET STATEMENT CACHE statement
      Use the SET STATEMENT CACHE statement to turn on caching or turn off caching for the current session. This statement is an extension to the ANSI/ISO standard for SQL.
    - SET TRANSACTION statement
      Use the SET TRANSACTION statement to define the isolation level and to specify whether the access mode of a transaction is read-only or read-write.
    - SET Transaction Mode statement
      Use the SET Transaction Mode statement to specify whether constraints are checked at the statement level or at the transaction level during the current transaction.
    - SET TRIGGERS statement
      Use the SET TRIGGERS statement to enable or disable all or some of the triggers on a table, or all or some of the INSTEAD OF triggers on a view.
    - SET USER PASSWORD statement (UNIX™, Linux™)
      Use the SET USER PASSWORD statement to change your password for database server access if you are an internally authenticated user. This statement is an extension to the ANSI/ISO standard for the SQL language.
    - START VIOLATIONS TABLE statement
      Use the START VIOLATIONS TABLE statement to create a violations table and a diagnostics table for a specified target table. The START VIOLATIONS TABLE statement is an extension to the ANSI/ISO standard for SQL.
    - STOP VIOLATIONS TABLE statement
      Use the STOP VIOLATIONS TABLE statement to drop the association between a target table, its violations table, and its diagnostics table. This statement is an extension to the ANSI/ISO standard for SQL.
    - TRUNCATE statement
      Use the TRUNCATE statement to quickly delete all rows from a local table and free the associated storage space. You can optionally reserve the space for the same table and its indexes. Only Informix® supports this implementation of the TRUNCATE statement, which is an extension to the ANSI/ISO standard for SQL.
    - UNLOAD statement
      Use the UNLOAD statement to write the rows retrieved by a SELECT statement to an operating-system file. The UNLOAD statement is an extension to the ANSI/ISO standard for SQL.
    - UNLOCK TABLE statement
      Use the UNLOCK TABLE statement in a database that does not support transaction logging to unlock a table that you previously locked with the LOCK TABLE statement. The UNLOCK TABLE statement is an extension to the ANSI/ISO standard for SQL.
    - UPDATE statement
      Use the UPDATE statement to change the values in one or more columns of one or more existing rows in a table or view.
    - UPDATE STATISTICS statement
      Use the UPDATE STATISTICS statement to update system catalog information that the query optimizer uses for operations on objects in the local database. The UPDATE STATISTICS statement is an extension to the ANSI/ISO standard for SQL.
    - WHENEVER statement
      Use the WHENEVER statement to trap exceptions that occur during the execution of SQL statements. The WHENEVER statement is equivalent to placing an exception-checking routine after every SQL statement.
    - WITH statement (Common Table Expressions)
      A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. The following discussion describes how to write statements that use CTEs.
  - SPL statements
    These topics describe Stored Procedure Language (SPL) statements, which you use to write SPL routines. You can store these routines in the database as user-defined routines (UDRs).
  - Data types and expressions
    These topics describe the data types and expressions that Informix®supports.
  - Other syntax segments
    These topics describe syntax segments, which are language elements, such as database object names or optimizer directives, that appear as a subdiagram reference in the syntax diagrams of some SQL or SPL statements.
  - Built-In Routines
  - Appendixes
- Guide to SQL: Reference
  The HCL® 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.
- Guide to SQL: Tutorial
  The HCL® 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.
Troubleshooting HCL Informix®
Several troubleshooting techniques, tools, and resources are available for resolving problems that you encounter in your HCL Informix® database server environment.

VISIBLE AND INVISIBLE options for indexes

Visibility option for indexes decides availability of the index to the optimizer during query optimization.

Index visibility Options

VISIBLE: An index that is set to be visible. A visible index is maintained and may be considered but not necessarily used by the optimizer. VISIBLE keyword is optional.
INVISIBLE: An index that is set to be invisible, is maintained but ignored by the optimizer.

The indexes are considered by the optimizer unless the indexes are disabled or directives are used. When the index is disabled, it is not maintained. Enabling a disabled index rebuilds the index.

A newly created index will be visible by default or can be set to visible using VISIBLE keyword while creating it. Use the INVISIBLE keyword of the CREATE INDEX statement to create an invisible index. The index will be created as invisible or can be made invisible from being visible.

Example 1:

CREATE TABLE tab1(col1 int, col2 int, col3 int);
CREATE INDEX idx1 on tab1(col1);
CREATE INDEX idx2 on tab1(col2) VISIBLE;
CREATE INDEX idx3 on tab1(col3) INVISIBLE;

idx1 and idx2 will be created VISIBLE. Although idx1 is created without the VISIBLE keyword, by default it is VISIBLE. idx3 will be created INVISIBLE.

Dbschema/Dbexport and VISIBLE/INVISIBLE indexes

If the index is created invisible or made invisible using the SET INDEXES statement, dbschema and dbexport will append invisible keyword to the create index statement.

Using the indexes created in Example 1,  dbschema output will be:

After the create index statements:
create index "uaymak1".idx1 on "uaymak1".tab1 (col1) using btree ;
create index "uaymak1".idx2 on "uaymak1".tab1 (col2) using btree ;
create index "uaymak1".idx3 on "uaymak1".tab1 (col3) using btree invisible ;

Indexes are set from visible to invisible and vice versa:
set indexes "uaymak1".idx1 INVISIBLE ;
set indexes "uaymak1".idx2 INVISIBLE ;
set indexes "uaymak1".idx3 VISIBLE ;

After the set indexes statements:
create index "uaymak1".idx1 on "uaymak1".tab1 (col1) using btree invisible ;
create index "uaymak1".idx2 on "uaymak1".tab1 (col2) using btree invisible ;
create index "uaymak1".idx3 on "uaymak1".tab1 (col3) using btree ;