Home
SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
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.
Programming for a multiuser environment
The SQL statement cache

SQL programming
You can use the HCL Informix® implementation of the SQL language to develop applications for Informix database servers.
- Guide to SQL: Syntax
- Guide to SQL: Reference
  The Informix® Guide to SQL: Reference contains the reference information for the system catalog tables, data types, and environment variables of the HCL Informix dialect of the SQL language, as implemented in HCL Informix. These topics also include information about the stores_demo, sales_demo, and superstore_demo databases that are included with HCL Informix.
- 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.
  - Database concepts
  - Compose SELECT statements
  - Select data from complex types
  - Functions in SELECT statements
  - Compose advanced SELECT statements
  - Modify data
  - Access and modify data in an external database
  - SQL programming
  - Modify data through SQL programs
  - Programming for a multiuser environment
    - Concurrency and performance
    - Locks and integrity
    - Locks and performance
    - Concurrency issues
    - How locks work
      Informix® database servers support a complex, flexible set of locking features that the topics in this section describe.
    - Lock with the SELECT statement
      The type and duration of locks that the database server places depend on the isolation set in the application and whether the SELECT statement is within an update cursor.
    - Retain update locks
    - Exclusive locks that occur with some SQL statements
      When you execute an INSERT, UPDATE, or DELETE statement, the database server uses exclusive locks. An exclusive lock means that no other users can update or delete the item until the database server removes the lock. In addition, no other users can view the row unless they are using the Dirty Read isolation level.
    - The behavior of the lock types
    - Control data modification with access modes
    - Set the lock mode
    - Simple concurrency
    - Hold cursors
    - The SQL statement cache
    - Summary
  - Create and use SPL routines
  - Create and use triggers

The SQL statement cache

The SQL statement cache is a feature that lets you store in a buffer identical SQL statements that are executed repeatedly so the statements can be reused among different user sessions without the need for per-session memory allocation. Statement caching can dramatically improve performance for applications that contain a large number of prepared statements. However, performance improvements are less dramatic when statement caching is used to cache statements that are prepared once and executed many times.

Use SQL to turn on or turn off statement caching for an individual database session when statement caching is enabled for the database server. The following statement shows how to use SQL to turn on caching for the current database session:

SET STATEMENT CACHE ON

The following statement shows how to use SQL to turn off caching for the current database session:

SET STATEMENT CACHE OFF

If you attempt to turn on or turn off statement caching when caching is disabled, the database server returns an error.

For information about syntax for the SET STATEMENT CACHE statement, see the Informix® Guide to SQL: Syntax. For information about the STMT_CACHE and STMT_CACHE_SIZE configuration parameters, see the Informix® Administrator's Reference and your Informix® Performance Guide. For information about the STMT_CACHE environment variable, see the Informix® Guide to SQL: Reference.