SET

DIR

Use the DIR keyword to nominate a relative or absolute directory path to be set as the current directory.

The DIR keyword does not relate to the other SET keywords.

READONLY

Indicate if the current database connection should allow read or write access. A database connection must be established using the CONNECT command. This keyword then applies to the current database connection.

By default when a database connection is established, read and write access are permitted (depending on restrictions imposed by the database).

A value of *YES indicates any changes to the database are automatically committed after executing each statement.

A value of *NO indicates that changes to the database are controlled by programmed transaction boundaries.

The READONLY keyword does not have any relationship with the DIR keyword.

AUTOCOMMIT

Indicate if information should be automatically committed to the database. A database connection must be established using the CONNECT command to use this keyword. This keyword then applies to the current database connection.

By default when a connection is established it is in auto-commit mode.

A value of *YES indicates any changes to the database are automatically committed after executing each statement.

A value of *NO indicates that changes to the database are controlled by programmed transaction boundaries.

The AUTOCOMMIT keyword does not have any relationship with the DIR keyword.

ISOLATION

Indicate the level of transaction isolation to be applied to the current database. A database connection must be established using the CONNECT command to use this keyword. This keyword then applies to the current database connection.

The ISOLATION keyword does not have any relationship with the DIR keyword.

The default value is *NONE otherwise you can choose from these four levels of transaction defined by the ANSI/ISO SQL standard:

All uncommitted data is readable from any connection. This is the same as not having any isolation (*NONE).

This prevents dirty reads but does not prevent phantoms or non-repeatable reads. Using this isolation level, only data committed before the current transaction began will be available. Any dirty data or changes made by concurrent transactions will not be available.

This level is obviously more restrictive than the *READUNCOMMITTED.

This prevents dirty and non-repeatable reads but does not prevent phantom rows. This means the probability of other transactions having to wait for this one are increased when compared to *READUNCOMMITTED and *READCOMMITTED

This is more restrictive than *READCOMMITTED.

*SERIALIZABLE provides the highest transaction isolation. When a transaction is isolated at the *SERIALIZABLE level, only data committed before the transaction began is available. Neither dirty data nor concurrent transaction changes committed during transaction execution are available. This level emulates serial transaction execution, as transactions will effectively be executed one after another rather than concurrently.

This is more restrictive than *REPEATABLEREAD.

In relation to these isolation levels there are three phenomena that you need to understand before you can determine the correct isolation level to apply to your application, namely:

• Dirty Reads - A transaction reads data written by an uncommitted transaction. If the second transaction is rolled back, the data read by the first transaction is then invalid because the rollback undoes the changes. The first transaction won't be aware that the data it has read has become invalid.
• Non-repeatable Reads - A transaction re-reads data it has previously read and finds that data has been modified by another committed transaction.
• Phantom Read - Phantom reads occur when new records added to the database are detectable by transactions that started prior to the insert. A transaction re-executes a query and returns a set of rows satisfying a search condition only to find that additional rows satisfying the condition have been inserted by another committed transaction.

The ANSI/ISO SQL standard isolation levels and the corresponding behaviors are summarized in the following table: