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®.
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.
QUERY_TIMEOUT session environment option
Use the QUERY_TIMEOUT environment option of the SET ENVIRONMENT statement to limit the amount of clock time a query may take before being interrupted internally.

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.
    - 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.
      - AUTOLOCATE session environment option
        Use the AUTOLOCATE environment option of the SET ENVIRONMENT statement to enable or disable the automatic location of databases, indexes, and permanent tables.
      - AUTO_READAHEAD session environment option
        Use the AUTO_READAHEAD environment option to change the automatic read-ahead mode, the batch size, the threshold, or to disable automatic read-ahead operations for the current session.
      - AUTO_STAT_MODE session environment option
        Use the AUTO_STAT_MODE environment option to enable or disable an automatic mode for UPDATE STATISTICS operations on column distribution statistics during the current session. In automatic mode, the user can define a minimum data change threshold as a property of the table. The database server refreshes statistics on the table, its indexes, and on table and index fragments selectively, only if the data has changed in a percentage of rows beyond that threshold since the distribution statistics were last calculated.
      - BOUND_IMPL_PDQ session environment option
        You can set the BOUND_IMPL_PDQ session environment option to limit PDQ resource allocation. If the IMPLICIT_PDQ session environment option is set to ON or to a positive integer value no greater than 100, you can use the BOUND_IMPL_PDQ environment option to specify that the allocated memory should be bounded by the current explicit PDQPRIORITY value or by a range.
      - CLUSTER_TXN_SCOPE session environment option
        When a client session in a high-availability cluster server issues a commit, the server blocks the current session until the committed transaction is applied in that session, or applied on a secondary server, or applied across the cluster, depending on the setting of the CLUSTER_TXN_SCOPE session environment option of the SET ENVIRONMENT statement.
      - DEFAULTESCCHAR session environment option
        You can use the DEFAULTESCCHAR session environment option of the SET ENVIRONMENT statement to override the current default escape character within character-string operands of LIKE or MATCHES expressions during the current session.
      - EXTDIRECTIVES session environment option
        You can use the EXTDIRECTIVES environment option of the SET ENVIRONMENT statement to enable or disable external optimizer directives during the current session.
      - FORCE_DDL_EXEC session environment option
        Use the FORCE_DDL_EXEC session environment option of the SET ENVIRONMENT statement to force out other transactions that have opened or have locks on the tables involved in an ALTER FRAGMENT ON TABLE operation.
      - GRID_NODE_SKIP session environment option
        Use the GRID_NODE_SKIP option of the SET ENVIRONMENT statement to define the behavior of the database server when a grid server within the grid or region that the query specifies is not available.
      - HDR_TXN_SCOPE session environment option
        Use the SET ENVIRONMENT HDR_TXN_SCOPE statement of SQL to control when a transaction commit is returned to a client application in a cluster environment.
      - IFX_AUTO_REPREPARE session environment option
        Use the IFX_AUTO_REPREPARE session environment option to temporarily change the system-wide setting of the AUTO_REPREPARE configuration parameter. The new setting is in effect for the remainder of the session or until you reset IFX_AUTO_REPREPARE for the session.
      - IFX_BATCHEDREAD_INDEX session environment option
        You can use the IFX_BATCHEDREAD_INDEX session environment option of the SET ENVIRONMENT statement of SQL to enable the query execution plans that execute light scans on the indexes of large tables, bypassing the buffer pool by using session memory to read directly from disk.
      - IFX_BATCHEDREAD_TABLE session environment option
        Use the IFX_BATCHEDREAD_TABLE environment option of the SET ENVIRONMENT statement to enable or disable light scans on compressed tables, tables with rows that are larger than a page, and tables with VARCHAR, LVARCHAR, and NVARCHAR data during the current session.
      - IFX_SESSION_LIMIT_LOCKS session environment option
        When IFX_SESSION_LIMIT_LOCKS is set in the session, its setting can specify a lower limit than the SESSION_LIMIT_LOCKS configuration parameter value for the maximum number of locks for users who are not administrators. This option, however, cannot restrict the number of locks in the session of a user who holds administrative privileges, such as user informix or a DBSA user, and cannot specify a limit for nomadministrative users below 500 locks.
      - IMPLICIT_PDQ session environment option
        Use the IMPLICIT_PDQ session environment option to allow the database server to determine the amount of memory allocated to a query, ignoring the explicit system default PDQPRIORITY environment variable setting.
      - INFORMIXCONRETRY session environment option
        Use the INFORMIXCONRETRY session environment option of the SET ENVIRONMENT statement to specify the maximum number of additional connection attempts that can be made to each database server during a single CONNECT statement in the current session, after the initial connection attempt fails. All these attempts must be made within the time limit that the INFORMIXCONTIME setting specifies.
      - INFORMIXCONTIME session environment option
        Use the INFORMIXCONTIME session environment option of the SET ENVIRONMENT statement to set an upper limit on the number of seconds while the current session attempts to establish a connection to another database server, before an error is returned.
      - NOVALIDATE session environment option
        Use the NOVALIDATE environment option to specify whether a foreign-key or check constraint that the ALTER TABLE ADD CONSTRAINT statement creates, or that has its constraint mode reset by the SET CONSTRAINTS statement, are in NOVALIDATE mode by default. The NOVALIDATE setting has no effect, however, on ADD CONSTRAINT or SET CONSTRAINT operations that specify DISABLED mode.
      - OPTCOMPIND session environment option
        Use the OPTCOMPIND session environment option of the SET ENVIRONMENT statement to specify methods for the query optimizer to choose in join queries and MERGE statements of the currently executing routine.
      - QUERY_TIMEOUT session environment option
        Use the QUERY_TIMEOUT environment option of the SET ENVIRONMENT statement to limit the amount of clock time a query may take before being interrupted internally.
      - RETAINUPDATELOCKS session environment option
        The RETAINUPDATELOCKS environment option can improve concurrency in Dynamic SQL applications that include the SELECT ... FOR UPDATE statement. This option can modify the behavior of the current transaction isolation level at runtime if the session is using the Committed Read, Dirty Read, or Cursor Stability isolation levels to enable (or to disable) the RETAIN UPDATE LOCKS clause of the SET ISOLATION statement.
      - SELECT_GRID session environment option
        Use the SELECT_GRID option of the SET ENVIRONMENT statement to define a default GRID clause for grid queries. This clause specifies a default grid or region from which to return a result set equivalent to logical UNION of qualifying rows from participating grid servers.
      - SELECT_GRID_ALL session environment option
        Use the SELECT_GRID_ALL option of the SET ENVIRONMENT statement to define a default GRID clause for subsequent grid queries in the session. This clause specifies a default grid or region from which to return a result set equivalent to a logical UNION ALL of qualifying rows from participating grid servers.
      - STATCHANGE session environment option
        Use the STATCHANGE environment option to specify a positive integer as a global percentage-of-change threshold for UPDATE STATISTICS operations in automatic mode. The setting restricts automatic UPDATE STATISTICS operations to stale or missing distributions in which at least that percentage of rows have changed since statistics were updated.
      - USELASTCOMMITTED session environment option
        The USELASTCOMMITTED session environment option can improve concurrency in sessions that use the Committed Read, Dirty Read, Read Committed, or Read Uncommitted isolation levels by reducing the risk of locking conflicts when two or more sessions attempt to access the same row in a table whose locking granularity is row-level locking.
      - USE_SHARDING session environment option
        The USE_SHARDING session environment option controls the scope of sharded queries.
      - SHARDJOIN_FALLBACK session environment option
        Use SHARDJOIN_FALLBACK session environment option to create a local temporary table to pull the data from the shard participants.
      - USE_DWA session environment options
        Use the USE_DWA session environment options of the SET ENVIRONMENT statement to control various aspects of workload analysis, data mart creation, and query acceleration by setting the database client environment for Informix® Warehouse Accelerator sessions. Each of the USE_DWA options has an effect only with Informix Warehouse Accelerator.
      - USTLOW_SAMPLE environment option
        Use the USTLOW_SAMPLE session environment option to enable or disable sampling during the collection of index statistics for UPDATE STATISTICS LOW operations in the current session.
    - 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.

QUERY_TIMEOUT session environment option

Use the QUERY_TIMEOUT environment option of the SET ENVIRONMENT statement to limit the amount of clock time a query may take before being interrupted internally.

Units of the QUERY_TIMEOUT value are seconds. This setting will not affect DML or DDL statements, or administration operations.

If a query times out, the client will receive SQL error 213 (Statement timed out or interrupted by user) and ISAM error 157 (Interrupted ISAM call).

The SET ENVIRONMENT QUERY_TIMEOUT statement of SQL supports the following syntax:

Syntax

SET ENVIRONMENT QUERY_TIMEOUT { 0 | - integer }


Element	Description	Restrictions	Syntax
`integer`	Non-negative integer that defines the maximum clock time a query is allowed	Must be in the range 1 ≤ integer ≤ 10000	Quoted String

Usage

The QUERY_TIMEOUT session environment option can have the following values:

'0' or "0"

Disables the timeout for the current session.
'integer' or "integer" where 1 ≤ integer ≤ 10000

Enables the query timeout for the current session and triggers an interrupt of any query that runs longer than integer seconds. Note that the query run time is evaluated based on clock time, not the cumulative time the session’s threads spend running on a VP.

It is not possible to set an instance-wide query timeout for all sessions. Each individual session must configure its own timeout.