Improving ZDT/Db2 performance

ZDT/Db2 performance can be improved by creating additional indexes on various Db2® catalog tables. A sample job HFM2CIX0 is provided in HFM.SHFMSAM1 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 Z Data Tools 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 Z Data Tools 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 ZDT/Db2 functions that use the ZDT/Db2 editor to display data. The characteristics of the two editor modes are documented in the Z Data Tools User's Guide and Reference for Db2 Data.

The use of "large" editor mode may have negative Db2 performance implications. When the ZDT/Db2 editor operates in "large" mode, it uses a Db2 scrollable cursor for access to Db2 data. This minimizes the memory usage in the ZDT/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 ZDT/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 HFM2SSDM macro parameter EDIT_MAX_ROWS to a non-zero value. See EDIT_MAX_ROWS. The HFM2SSDM macro is discussed further in Defining all Db2 systems that ZDT/Db2 will access in HFM2POPT (required).