Editor Options (7 of 8) panel

Panel and field definitions

  Process   Options   Utilities   Help
 ────────────────────────────────────────────────────────────────────────────────
 FM/Db2 (DFG2)              Editor Options (7 of 8)             Global Settings

 Concurrency Options:                  SQL Cursor Concurrency Options:
    Enter "/" to select option            Concurrency
       Read-only access                   1  0. Uncommitted read
    /  Commit after data fetch               1. No with clause
       Skip locked rows                      2. Cursor stability
                                             3. Read stability
 Edit Locking Option:                        4. Read stability keep locks
    Locking                                  5. Repeatable read
    1  1. None                               6. Repeatable read keep locks
       2. Share mode
       3. Exclusive mode                  Keep locks option
                                          1  1. Use/Keep share locks
                                             2. Use/Keep update locks
                                             3. Use/Keep exclusive locks



 Command ===>                                                                  
  F1=Help      F2=Split     F3=Exit      F7=Backward  F8=Forward   F9=Swap
 F10=PrvPage  F12=Cancel

Concurrency options

For a detailed explanation about this subject, refer to the Db2 UDB for z/OS Administration Guide.

Concurrency options affect the way Db2® takes row and page locks for data in a Db2® table. Locking options affect the way Db2® takes table locks. When a table locking option is specified, it overrides any concurrency option.

In general terms, specifying a higher value for the concurrency option reduces the likelihood of deadlocks when editing an object, at the expense of other users' ability to access and change data.

FM/Db2 provides options that can minimize Db2® locking, and the duration of the Db2® locks taken, when FM/Db2 accesses Db2® and retrieves data for a File Manager/Db2 editor session.

FM/Db2 also provides options to lock the table to prevent other users from either updating or reading data. The locking options should be used with caution.

Note that both the concurrency and table locking options only apply to the initial fetch of data from Db2®. If either of the commit options Commit changes when save issued or Commit changes when no save errors is specified, each commit point releases any locks held by Db2® against the table. For more information, see the description about Enter Key Processing, Commit Options in Editor Options (6 of 8) panel.

After each commit point, FM/Db2 re-locks the table if a table-locking option has been specified; however, other Db2® users may access the table between the time of the commit point and FM/Db2 re-locking the table.

Note:

1. Use table locking options with caution.
2. For the table locking option to be effective, the Db2® object must be a table. It is not possible to lock a view.
3. Any concurrency option applies to the initial fetch of data from Db2® only.
4. Any concurrency or locking option persists only until the next commit point. See below for more information.
5. Set the Concurrency option to 0.

FM/Db2 provides options for accessing data in read-only mode, and for reducing the life of the Db2® locks taken when data is retrieved from Db2®. It also provides control over the locking mechanism used when data is retrieved from Db2®.

Read-only access

If you select this option, FM/Db2 accesses data from Db2® in read-only mode, by adding the FOR FETCH ONLY clause to the SQL statement used to retrieve data from Db2®. By default, this option is not selected.

Selecting this option does not prevent edit operations from occurring.

Note that, for a File Manager/Db2 Browse session, the FOR FETCH ONLY clause is always added to the SQL statement used to retrieve data from Db2®.

Commit after data fetch

The default for this option is selected. The value for this option may have been fixed by the product installer. If you select this option, FM/Db2 issues an explicit COMMIT when the data from the FM/Db2 Edit session was retrieved from Db2®. This releases all Db2® locks taken when the data for the FM/Db2 Edit session is retrieving. It also releases any explicit table locks taken when a locking shared or exclusive option is specified.

This option is recommended in high concurrency environments, where it is important to minimize the duration of any Db2® locks held.

Selecting this option does not prevent edit operations from occurring; however, it increases the likelihood of errors when changed data is presented to Db2® for validation. This is because other processes are free to change the Db2® data for which a copy is stored in the FM/Db2 Edit session. Therefore, when a change made in the FM/Db2 Edit session is presented to Db2®, the original data might have changed, resulting in various SQL errors.

Note that, for a File Manager/Db2 Browse or View session, FM/Db2 always issues an EXPLICIT commit once the data for the Browse session has been retrieved from Db2®.

Skip locked rows

Use this option to control whether a searched DELETE statement ignores or skips any rows that are incompatibly locked by other processes. The default is not selected.

Edit Locking Option

When FM/Db2 operates in edit mode only, use this option to control whether FM/Db2 explicitly locks the Db2® object that is being processed. This option applies to tables only, not to objects that are defined on a table such as views. This option may have been fixed by the product installer. When the option is available for update, the default is 1 (None).

1. None

FM/Db2 does not issue an explicit lock before accessing data for the table. This is the default choice.

2

FM/Db2 issues a statement that locks the table in share mode before accessing data for the table. The data from the table can be read by other tasks. However, the table cannot be updated by other processes until the FM/Db2 edit session ends. Use this option with caution to guarantee access to the table.

3

FM/Db2 issues a statement that locks the table in exclusive mode before accessing data for the table. The data from the table cannot be either read or written by other processes until the FM/Db2 edit session ends. Use this option with extreme caution to guarantee access to the table.

SQL Cursor Concurrency Options (Concurrency)

FM/Db2 can add a WITH clause to the SQL statement used to fetch data from Db2®. When a WITH clause is specified, Db2® takes locks (of varying levels) against either rows or pages within the table, as the data is accessed. The following options are possible. The default is 1 (No with clause).

0. Uncommitted read

Db2® locking is disabled. The data retrieved from Db2® may be inconsistent if another process is rolled back after the data access. Use this option in a high concurrency environment.

1. No with clause

This is the default value and recommended for most application programs. FM/Db2 does not add a WITH clause to the SELECT statement for the table. Any rows retrieved by FM/Db2 initially have a S(hare) lock. With this option it is possible that other processes can update the table you are editing.

2. Cursor stability

A row or page lock is held only long enough for the cursor to move to another row or page.

3. Read stability

A row or page lock is held for rows or pages that are read by the editor at least until the next commit point.

4. Read stability keep locks

A row or page lock is held for rows or pages that are read by the editor at least until the next commit point. The Read stability option takes an S or U lock, while this option takes an X lock on affected rows or pages.

5. Repeatable read

A row or page lock is held for all accessed rows, qualifying or not, at least until the next commit point. FM/Db2 can return to a previously read page and read the same row again, and the data is not changed and no new rows are inserted.

6. Repeatable read keep locks

This option is similar to the Repeatable read option, except that FM/Db2 takes an X lock, rather than a S or U lock.

SQL Cursor Concurrency Options (Keep locks option)

FM/Db2 can add a WITH clause to the SQL statement used to fetch data from Db2®. When a WITH clause is specified, Db2® takes locks (of varying levels) against either rows or pages within the table, as the data is accessed. The following options are possible. The default is 1 (Use/Keep share locks).

1. Use/Keep share locks

FM/Db2 does not issue an explicit table lock for the object being edited. Locking of rows and pages within the object is managed by Db2® according to FM/Db2 concurrency options and Db2® installation and object definition options.

2. Use/Keep update locks

FM/Db2 issues a LOCK TABLE IN SHARE MODE statement before reading the data from Db2®. This restricts all other users to read-only operations on the object. If either the Enter key option Save data and commit changes, or the Commit changes when SAVE issued option is selected, any commit point take during the Edit session releases the table lock. FM/Db2 re-issues the lock immediately after the commit point. However, other processes can access the table between the time of the commit point and FM/Db2 re-locking the table.

3. Use/Keep exclusive locks

FM/Db2 issues a LOCK TABLE IN EXCLUSIVE MODE statement before reading the data from Db2®. This prevents all other users from accessing the object, unless the other process is running with an isolation level of uncommitted read. If either the Enter key option Save data and commit changes, or the Commit changes when SAVE issued option is selected, any commit point take during the Edit session releases the table lock. FM/Db2 re-issues the lock immediately after the commit point. However, other processes can access the table between the time of the commit point and FM/Db2 re-locking the table.