Customizing FM/Db2 for use in production environments

If you intend to use FM/Db2 to access Db2® systems that support a high throughput online transaction processing environment, you should consider the potential for adverse performance and concurrency problems arising from the use of FM/Db2, particularly the FM/Db2 editor.

This section outlines concurrency and performance issues that might arise:

  • The use of large mode in the FM/Db2 editor.

    The File Manager Db2® editor can operate in two modes: normal and large. The mode of operation is determined by the value entered in the row count field for those FM/Db2 functions that use the FM/Db2 editor to display data. The characteristics of the two editor modes are documented in the File Manager User’s Guide and Reference for DB2 Data.

    The use of large editor mode might have negative Db2® performance implications. When the FM/Db2 editor operates in large mode, it uses a Db2® scrollable cursor for access to Db2® data. This minimizes the memory usage in the FM/Db2 user's TSO address space, but might require Db2® to build a temporary copy of the entire result table in a Db2® temporary database. For large tables, this can lead to SQLCODE-904 (unavailable resource) on table spaces defined within the Db2® temporary database. For these reasons, providing access to the FM/Db2 editor in production Db2® environments, where there are large Db2® tables, should be carefully considered.

    The product installer can disable the use of large editor mode, by Db2® subsystem. This is achieved by setting the FMN2SSDM macro parameter
  • The use of table locking options

    The File Manager Db2® editor has an option that allows a table to be locked for the duration of the editor session. The table can be locked in SHARED or EXCLUSIVE mode, see the description of the 'LOCK TABLE' statement in the SQL Reference manual for the appropriate version of Db2® for a description of these options.

    The use of either the SHARED or EXCLUSIVE options is inconsistent with the requirements for a high throughput online transaction processing environment. You can disable the editor table locking option, by Db2® subsystem. This is achieved by setting the FMN2SSDM macro parameters:
  • User specified SELECT statements and FM/Db2 editor choice

    Various FM/Db2 functions allow users to create and execute SELECT statements. The FM/Db2 editor is used to display the result table for these statements. There is a system option controlling whether the result table is displayed in browse, view or edit modes.

    FM/Db2 functions that process SELECT statements include the following:


    Prototype SELECT statements (basic)
    Prototype SELECT statements (advanced)
    Enter, execute and explain SQL statements
    Edit and execute SQL statements from a data set

    You can disable the use of the FM/Db2 editor, in edit mode, when processing the result table for user specified SELECT statements, by Db2® system. This is achieved by setting the FMN2SSDM macro parameter USER_SELECT_EDIT to NO. See USER_SELECT_EDIT.

    The FMN2SSDM macro is discussed further in Defining all Db2 systems that FM/Db2 will access in FMN2POPT (required).

  • The use of Cursor concurrency options

    The FM/Db2 editor has an option that allows a WITH clause to be added to the declaration for the cursor used to access Db2® data. See the description for isolation-clause in the SQL Reference manual for the appropriate version of Db2®.

    The default for this option is to not add any WITH clause, which results in Db2® taking locks based on system options, and options specified for the table spaces that contain the Db2® object. The following editor options are also relevant:


    Read-only access
    Commit after data fetch

    The Uncommitted read concurrency option can be specified when you need to avoid any locks being taken by FM/Db2 users when they read data prior to display in an editor session. You can force the use of uncommitted read, by Db2® system. This is achieved by setting the FMN2SSDM macro parameter FORCE_WITH_UR to Y. See FORCE_WITH_UR.

    The FMN2SSDM macro is discussed further in Defining all Db2 systems that FM/Db2 will access in FMN2POPT (required).

  • The PROD_EDIT parameter

    The FMN2SSDM macro parameter PROD_EDIT has multiple effects on the behavior of the File Manager Db2® editor, as described below:

    • An inactivity timer applies to all FM/Db2 editor sessions. If EDITOR_TIMEOUT=0 is specified in the same FMN2SSDM macro, this value is replaced with EDITOR_TIMEOUT=60.
    • Table locking is disabled. If TABLE_LOCKING=YES is specified in the same FMN2SSDM macro, this value is replaced with TABLE_LOCKING=NO. When a FM/Db2 user displays the Editor Locking option, the value is set to '1' (None) and cannot be altered. This prevents the user changing the option to either SHARED or EXCLUSIVE.
    • Large edit mode is disabled. If EDIT_MAX_ROWS=0 is specified in the same FMN2SSDM macro, this value is replaced with EDIT_MAX_ROWS=1000. When a FM/Db2 user enters 0 in the row count field on an editor-related function entry panel, the value is ignored and replaced with the value specified for EDIT_MAX_ROWS. This prevents the user accessing the FM/Db2 editor in large mode.
    • The Commit after data fetch option is fixed and is selected.
    • The Commit when save issued option is fixed and is selected.
    • The Commit when no save errors option is fixed and is not selected.

    The Commit after data fetch option ensures that any user of the FM/Db2 editor has no locks against Db2® when the data in the editor session is initially displayed, and the user cannot change the option. The other two options ensure that a Db2® commit is always issued when a user attempts to save changes made in the current editor session.

    The commit is always issued, whether there are save errors or not. When considered in combination, the PROD_EDIT behavior reduces locking against the Db2® object being edited to a minimum. The only time locks are held is when:

    • The data is initially accessed, prior to the display of the data.
    • Whenever changes made in the editor session are presented to Db2® for validation.
  • Restricting the use of the FM/Db2 editor and educating users

    Setting the FMN2SSDM parameters as described in above reduces the potential for unwanted locking or resource constraints arising from the use of the FM/Db2 editor, it does not eliminate it.

    The FM/Db2 editor is a powerful tool. You should consider carefully which users, if any, have access to the FM/Db2 editor in production Db2® environments.

    You should consider educating any users of the FM/Db2 editor in production environments. The main issues to be considered are:

    • Minimizing the number of rows of data retrieved for any editor session. The fewer rows that are retrieved, the smaller the number of locks that might be taken against production data bases.
    • Minimizing the duration of any editor sessions against production data. The Db2® unit of work concept is relevant here, it is recommended that any users of the FM/Db2 editor against production data are familiar with the concept, and potential problems that might arise when the elapsed time for a unit of work becomes long.

    The following example demonstrates the potential problem.

    The user starts a File Manager/Db2 editor session of a production table. They have the editor option uncommitted read on and use row selection criteria to limit the number of rows in the editor session to 1000.

    The user issues a change command that affects 800 rows. The user issues the SAVE command to validate the changes made. The editor option Commit when save issued is turned off. The changes are successful: all of the 800 rows that were changed are updated in Db2®.

    The user is called away, goes to lunch or finishes work for the day, leaving the editor session active.

    In this scenario the FM/Db2 editor session starts with no locks against the production database. When the SAVE command is issued, the 800 rows that are changed are marked in Db2® with update locks. The editor option Commit when save issued option is off, so there is no COMMIT issued and the Db2® unit of work remains active. The Db2® locks against the 800 rows remain active until the Db2® unit of work ends, which occurs when the user cancels the editor session, the user ends the editor session, or the user's TSO session is cancelled. This might be hours, even days, in the scenario outlined above. During that time other users of the production database may not be able to access those rows.

    The issues outlined in this scenario can be avoided if PROD_EDIT=YES is specified.

    Users of the FM/Db2 editor in production environments should be educated to minimize the elapsed time for any editor sessions that make changes to production data. This minimizes the duration of any locks taken against production data.