Improving FM/Db2 performance

FM/Db2 performance can be improved by creating additional indexes on various Db2® catalog tables. A sample job FMN2CIX0 is provided in FMN.SFMNSAM1 to help you do this. This job contains additional indexes that are useful in improving performance when connected to Db2® version 10 or later systems.

Take a copy of the job and make the changes described in the job. You should review the indexes to be created against the current indexes defined for the Db2® catalog tables. It is possible that some of the indexes to be defined already exist, having been created as part of the installation of other products, or during the installation of previous versions of File Manager Db2®.

After the indexes have been successfully created, run the RUNSTATS utility against database DSNDB06. See your site Db2® System Administrator for additional information on how to do this.

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 may 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 EDIT_MAX_ROWS to a non-zero value. See EDIT_MAX_ROWS. The FMN2SSDM macro is discussed further in Defining all Db2 systems that FM/Db2 will access in FMN2POPT (required).