[No]Logging

The set nologging statement allows a database administrator to bypass the logging and recovery system. This can be speed up certain types of batch update operations but must always be used with extreme care.

The set nologging statement is intended to be used solely for large database load operations for which the reduction of logging overhead and log file space usage outweigh the benefits of having the system recover automatically from update errors.

Set Nologging

To disable transaction logging, issue the following set statement:

set nologging

When transaction logging is disabled, the standard Ingres tools and procedures cannot be used to recover from transaction errors. Any error that occurs when the database is being updated (including interrupts, deadlock, lock timeout, and forced abort), or any attempt to rollback a transaction causes the DBMS Server to mark the database inconsistent.

To use the set nologging option, the DBA must:

• Obtain exclusive access on the database to ensure that no updates (other than those performed by the batch update operation) can occur during the operation.
• Prepare to recover the database before suspending logging. There are two cases:
• For existing databases, checkpoint the database prior to executing the operations that use the set nologging statement. If an error halts processing, the database can be restored from the checkpoint and the process restarted.
• If loading a new database, no checkpoint is required. To correct consistency errors, destroy the inconsistent database, create a new database, and restart the load operation.

Caution! Do not use the set nologging statement in an attempt to improve performance during everyday use of a production database.

Set Logging

To reenable transaction logging, issue the following set statement:

set logging

This set statement can only be issued by the DBA of the database on which the session is operating and cannot be issued within a multi-statement transaction. After set logging is executed, automatic database recovery is again guaranteed.

If set nologging is used on a journaled database, take a new checkpoint immediately after completion of the set nologging operations to establish a new base from which to journal updates.

When a session in set nologging mode disconnects from a database, the DBMS Server executes a set logging operation to bring the database to a guaranteed consistent state before completing the disconnect.

[No]Optimizeonly

Specifies whether query execution halts after the optimization phase. To halt execution after the query has been optimized, specify set optimizeonly. To continue query execution after the query is optimized, specify set nooptimizeonly. To view query execution plans (QEP's) without executing a query, use set optimizeonly in conjunction with set qep.

Connection

The set connection statement switches the current session to a database connection previously established using the connect statement. To determine the current connection name, use the dbmsinfo(connection_name) statement.

Issuing the set connection none statement results in the state being identical to prior to the first connect statement or following a disconnect statement: no current session.

Work Locations

The set work locations statement adds, removes, or changes the set of (disk and directory) locations that the DBMS Server uses for sorting. Sorting can occur when queries are executed (select or fetch statements with order by clauses) or tables are restructured (for example, using the modify statement). Work locations are defined using the create location statement.

To add work locations to the list of locations currently in use, issue the set work locations add statement and specify the locations to be added. To remove locations from the set of locations currently in use, issue the set work locations drop statement. To replace the set of locations currently in use, issue the set work locations use statement. The work locations you specify are in effect for the duration of the session.

For details about configuring your installation to improve the performance of sorting, see the Database Administrator Guide.

Update_Rowcount

The set update_rowcount statement specifies the nature of the value returned by the inquire_sql(rowcount) statement. Valid options are:

• Changed - Inquire_sql(rowcount) returns the number of rows changed by the last query.
• Qualified - Inquire_sql(rowcount) returns the number of rows that qualified for change by the last query. Qualified is the default setting.

For example, for the following table:

and the following query:

update test_table set column1 = 'Jones'
where column2 = 000 and column3 = 'green';

The DBMS Server, for reasons of efficiency, does not actually update the first row because column1 already contains Jones; however, the row does qualify for updating by the query.

For the preceding query, if the update_rowcount option is set to changed, inquire_sql(rowcount) returns 2 (the number of rows actually changed), but if the update_rowcount option is set to qualified, inquire_sql(rowcount) returns 3 (the number of rows that qualified to be changed).

To determine the setting for the update_rowcount option, issue the select dbmsinfo('update_rowcnt') statement.