Using the FOR READ ONLY Clause in Read-Only Mode

Normally, you do not need to include the FOR READ ONLY clause in a SELECT statement. SELECT is a read-only operation by definition, so the FOR READ ONLY clause is usually unnecessary. In certain circumstances, however, you must include the FOR READ ONLY keywords in a SELECT statement.

If you have used the High-Performance Loader (HPL) in express mode to load data into the tables of an ANSI-compliant database, and you have not yet performed a level-0 backup of this data, the database is in read-only mode. When the database is in read-only mode, the database server rejects any attempts by a select cursor to access the data unless the SELECT or the DECLARE includes a FOR READ ONLY clause. This restriction remains in effect until the user has performed a level-0 backup of the data.

In an ANSI-compliant database, select cursors are update cursors by default. An update cursor is a cursor that can be used to modify data. These update cursors are incompatible with the read-only mode of the database. For example, this SELECT statement against the customer_ansi table fails:

EXEC SQL declare ansi_curs cursor for
   select * from customer_ansi;

The solution is to include the FOR READ ONLY clause in your select cursors. The read-only cursor that this clause specifies is compatible with the read-only mode of the database. For example, the following SELECT FOR READ ONLY statement against the customer_ansi table succeeds:

EXEC SQL declare ansi_read cursor for
   select * from customer_ansi for read only;

DB–Access executes all SELECT statements with select cursors, so you must specify FOR READ ONLY in all queries that access data in a read-only ANSI-compliant database. The FOR READ ONLY clause causes DB–Access to declare the cursor for the SELECT statement as a read-only cursor.

For more information on level-0 backups, see your IBM Informix Backup and Restore Guide. For more information on select cursors, read-only cursors, and update cursors, see DECLARE .

For more information on the express mode of the HPL of Dynamic Server, see the IBM Informix High-Performance Loader User's Guide.