Home
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 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®.
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.
TO CLUSTER Option
The TO CLUSTER option causes the database server to reorder the rows of the physical table according to the order of index-key values.

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 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.
      - TO CLUSTER Option
        The TO CLUSTER option causes the database server to reorder the rows of the physical table according to the order of index-key values.
      - TO NOT CLUSTER Option
        The TO NOT CLUSTER option drops the cluster attribute on the specified index without affecting the physical order of rows in the table.
    - 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 Informix® ESQL/C 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.
    - 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 Informix® ESQL/C.
    - 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 Informix® ESQL/C 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 Informix® ESQL/C.
    - 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 statement
      Use the GET DESCRIPTOR statement to read from a system descriptor area.
    - 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 Informix® ESQL/C 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 Informix® ESQL/C 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 Informix® ESQL/C.
    - 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 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
      Use the SET DESCRIPTOR statement to set values in a system-descriptor area (SDA).
    - SET ENCRYPTION PASSWORD statement
      Use the SET ENCRYPTION PASSWORD statement to define or reset a session password for the encryption and decryption of character, BLOB, or CLOB values.
    - SET ENVIRONMENT statement
      Use the SET ENVIRONMENT statement to specify settings for session environment variables that can affect how queries are executed in the same routine, or other operations in the current user session. For some options, the session variable overrides default behavior that is set by a configuration parameter or by an environment variable of the client or of the database server.
    - 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, or to change the filtering mode of a unique index.
    - 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 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 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.

TO CLUSTER Option

The TO CLUSTER option causes the database server to reorder the rows of the physical table according to the order of index-key values.

Clustering an index rebuilds the table in a different location within the same dbspace. When you run the ALTER INDEX statement with the TO CLUSTER keywords, all of the extents associated with the previous version of the table are released. The resulting newly-built version of the table has no empty extents.

For an ascending index, TO CLUSTER puts rows in lowest-to-highest order. For a descending index, the rows are reordered in highest-to-lowest order.

When you reorder, the entire file is rewritten. This process can take a long time, and it requires sufficient disk space to maintain two copies of the table.

While a table is clustering, it is locked IN EXCLUSIVE MODE. When another process is using the table to which the index name belongs, the database server cannot execute the ALTER INDEX statement with the TO CLUSTER keywords; it returns an error unless lock mode is set to WAIT. (When lock mode is set to WAIT, the database server retries the ALTER INDEX statement.)

Over time, if you modify the table, you can expect the benefit of an earlier cluster to disappear because rows are added in space-available order, not sequentially. You can recluster the table to regain performance by issuing another ALTER INDEX TO CLUSTER statement on the clustered index. You do not need to drop a clustered index before you issue another ALTER INDEX TO CLUSTER statement on a currently clustered index.

Example of clustering an index

The following example shows how you can use the ALTER INDEX TO CLUSTER statement to physically order the rows in the orders table. The CREATE INDEX statement creates an index on the customer_num column of the table. Then the ALTER INDEX statement causes the physical reordering of the existing rows within the orders table: following

CREATE INDEX ix_cust ON orders (customer_num); 
ALTER INDEX ix_cust TO CLUSTER;