Autocommit

The set autocommit on statement directs the DBMS Server to treat each query as a single-query transaction. Set autocommit off, the default, means an explicit commit statement is required to commit a transaction.

The set autocommit statement cannot be issued in a multi-statement transaction. For a description of multi-statement transaction behavior, see the chapter "Working with Transactions and Handling Errors."

[No]Lock_Trace

The set lock_trace statement enables the display of ongoing locking activity for the current session, including information about the locks used and released by your statements. Lock tracing can be started or stopped at any time during a session. For additional information regarding the usage and output of this statement, see the Database Administrator Guide.

Important! Use set lock_trace as a debugging or tracing tool only. The lock_trace option is not a supported feature. This means that you must not include this feature in any application-dependent procedure.

[No]Journaling

The [no]journaling statement controls the default journaling status for permanent tables subsequently created in the session. If you specify the with nojournaling clause in the create table statement, the default setting is ignored. If you do not issue the [no] journaling statement, the default established for the server class that this session is attached to is used.

Important! Regardless of whether journaling is enabled for any specific table in a database, journaling occurs only when journaling is enabled for the database. Journaling for the entire database is turned on or off using the ckpdb command. For details about ckpdb, see the Command Reference Guide.

If the current journaling status of the table is not enabled, and you want to enable journaling for this table after the next checkpoint, use the set journaling on tablename statement.

Note: Journaling status can be enabled only when table is first created (in which case, a complete journal history of all files exists) or after a checkpoint (in which case the checkpoint and associated dump files have a consistent version of the table against which the subsequent journals can be applied).

To immediately cease journaling against a table, use the set nojournaling on tablename clause. The help table tablename statement shows that journaling is disabled.

Result_Structure

The set result_structure statement sets the default storage structure for tables created with the as clause of the create table statement. This storage structure can be any of the structures described in the modify statement, that is, heap, cheap, heapsort, cheapsort, hash, chash, btree, cbtree, isam or cisam.

For example, this first sequence of statements

set result_structure hash;
create temp as select id ... ;

does the same as this second sequence of statements

create temp as select id ... ;
modify temp to hash;

Either sequence results in the "temp" table being stored in a hash structure, hashed on the first column, "id" in this case. For hash, chash, isam, and cisam, the newly created table is automatically indexed on the first column.

The heap and cheap structures provide the best performance results for the create table as statement. However, these choices allow duplicate rows in the new table, even if noduplicates is specified in the create table as statement.

If a set result_structure statement is not executed, the default storage structure for a table created by the create table as statement is cheap.

Lockmode

The set lockmode statement sets different types and levels of locks that override the default locking strategy. Use this statement to optimize performance or enforce stricter validation and concurrency controls.

When beginning a session, the system defaults are in effect. Using the set lockmode statement, these defaults can be changed.

Note: The system defaults are taken from the config.dat file. Specific default values mentioned in this description are the initial system defaults.

One set of locking parameters can be established for all tables accessed during the session, or different locking parameters can be established for different tables. You can also return to the default parameters for one or all tables. The set lockmode statement cannot be issued within a transaction, except for the following statement: set lockmode … with timeout=<n|session|system|nowait>.

Set lockmode has five parameters that control locking for a session:

level: Specifies locking behavior; must be one of the following locking levels:

readlock: This parameter applies when accessing the table only to read its data. It does not apply if planning to update data in the table. Any of the following modes can be specified:

maxlocks: Specifies the maximum number of page locks taken on a table before locking escalates to a table lock. The number of locks available is dependent upon your system configuration. The following maxlocks escalation factors can be specified:

timeout: Specifies how long, in seconds, a lock request can remain pending. If the DBMS Server cannot grant the lock request within the specified time, the query that requested the lock aborts. Valid settings are:

[No]Printqry Option

The set printqry statement displays each query and its parameters as it is passed to the DBMS Server for processing. The set [no] printqry option disables this feature.

[No]Qep Option

The set qep statement displays a summary of the query execution plan chosen for each query by the optimizer. To disable this option, The set [no] qep option disables this option. For a discussion of query execution plans, see the Database Administrator Guide.

Joinop [No]Timeout

This statement turns the timeout feature of the optimizer on and off. When the optimizer is checking query execution plans, it stops when it believes that the best plan that it has found takes less time to execute than the amount of time already spent searching for a plan. If a set joinop notimeout statement is issued, the optimizer continues searching query plans. This option is often used with the set qep option to ensure that the optimizer is picking the best possible query plan.

To restore the default behavior, issue the set joinop timeout statement.

Joinop [No]Greedy

This statement enables or disables the complex query enumeration heuristic of the Ingres optimizer. The greedy heuristic enables the optimizer to produce a query plan much faster than with its default technique of exhaustive searching from queries that reference large numbers of tables. For a discussion of the greedy optimization heuristic, see the Database Administrator Guide.