Db2® performance and tuning

Various configuration options are available to assist you in optimizing performance for your environment.

Initial space allocation

This section is useful for the database administrator who must determine space requirements for Z Asset Optimizer . Listed in Initial space allocation for the product are guidelines for the initial spaces allocation based on the number of LPARs. The value for the SIZE parameter is specified in HZASCUST.

Table 1. Initial space allocation for the product
SIZE=	Initial space allocation	Number of LPARs
1	1600 cylinders	1-10
2	3750 cylinders	11-20
3	12600 cylinders	>20

In version 2.1, all table spaces and indexes are defined with ‘COMPRESS Y’. For estimated space requirements of the 3 largest table spaces, refer to PARMLIB member HZASSQ17.

Choosing a Db2® subsystem for this product: The Db2® resources required for this product do not need to be defined in a production Db2® subsystem in order to minimize competition for mainframe resources in the Db2® production environments. To avoid competing for mainframe resources, run the jobs for the Inquisitor Import and Usage Import during off-peak periods. In addition, run the utility Usage Deletion during off-peak periods.
Buffer pools: By allocating the appropriate buffer pool to the respective table spaces and indexes, as defined in HZASCUST, you can manage your system resources accordingly. For Db2® performance, first investigate the buffer pools. Check with your site specialist on the types and size of buffer pools that are defined for this product.
For sites using Db2 data sharing, group Bufferpools must be defined with the same names.
Space allocation and utilization: In terms of space utilization, -1 has been set for all SECQTY to enforce Sliding Secondary Extents. This enables Db2® to manage secondary extents efficiently, and minimizes extension failures. You need to extrapolate the PRIQTY for the table spaces and indexes for the large tables according to your requirements. Definitions for these Db2® objects are listed in the respective jobs in JCLLIB.
Repository tables with the biggest impact on performance due to size are TMODULE, TUSEMTD, and PRODUCT_USE_DETAIL. Data for the TMODULE table is populated during Inquisitor Import process. TUSEMTD, and PRODUCT_USE_DETAIL tables are populated during Usage Import. For example, you might have more than 300 million usage records in the TUSEMTD table, and more than 20 million modules identified in the TMODULE table. To minimize space utilization and improve SQL query performance, you must prune your usage and obsolete records by running the Usage Deletion job HZASUDEL and Physical Deletion job HZASPDEL.
Declared Global Temporary tables: Declared Global Temporary tables are used during the Asset Aggregator process. The Work file table spaces must be large enough to handle this process. When the Aggregator job step is run, indexes on declared global temporary tables are created. By default, the bufferpool used by the index is dependent on the bufferpool defined for the Work file database. Parameter IXBUFFERPOOL in PARMLIB member HZASAGP1 can be used to substitute the default value.
Work file database: When you run some of the SQL queries, they can produce a large amount of output. In order to avoid any excessive output, increase the number and size of the table spaces in the work file database.
Reorganization and RUNSTATS: It is important to run reorganization of the Repository table spaces periodically, especially after Inquisitor Imports, Usage Imports, and Usage Deletion After reorganization of the Repository table spaces, it is also a good idea to run RUNSTATS for these table spaces.