Tuning a database with the Database Tuning Statistics

Activation of the optional Database Tuning Statistics during the sequential processing of a database or during a database unload provides statistics that make easy for you to:

• Evaluate and monitor the effectiveness of HDAM randomizing parameters (such as the size of the root addressable area, the number of RAPs, the block or CI size, or the bytes limit)
• Periodically monitor the need for reorganizing HDAM, HIDAM, or HISAM databases.

How is it possible to determine whether the HDAM randomizing parameters are efficient and whether a database needs to be reorganized? The Database Tuning Statistics provide an important indicator to use in answering this question. This key indicator is the average number of I/Os required to read at random all database segments of one database record. By looking at this number in the Database Tuning Statistics reports, the database administrator can quickly determine whether a database is well organized and well randomized.

Ideally, this number would be 1.00. If the number is larger than 1.30, then the database might be disorganized or inefficiently randomized.

Other key indicators in the Database Tuning Statistics can be used to determine the cause of the problem, for example, to determine:

• Whether the HDAM root addressable area is overcrowded and whether its size should be increased. The Database Tuning Statistics answer this question by printing the packing density of the HDAM root addressable area (this is the percentage of DASD space occupied by database segments in the HDAM root addressable area). Experience shows that, with efficient randomizer modules (for example, DFSHDC40, Sequential Subset Randomizer), a reasonable goal for the packing density is often in the range of 70% to 80%.
• Whether the number of HDAM RAPs is appropriate. The Database Tuning Statistics show the ratio between the number of RAPs and the number of roots. Experience shows that, with efficient randomizer modules (for example, DFSHDC40, Sequential Subset Randomizer), a reasonable ratio is around 1.5.
• Whether the block or CI size is appropriate for the average database record length and for the distribution of the database record length.
• Whether the HDAM bytes limit is appropriate for the average database record length and the distribution of the database record length.

The following topics contain an example of the Database Tuning Statistics output for an existing HDAM database, together with a short description of the counters in the output. Then, in further topics, you will see how you can use some general rules of thumb and the most important key indicators of the Database Tuning Statistics for the tuning of an HDAM, HIDAM, or HISAM database.