Understanding the status of DEDB areas

The first step in tuning databases is to understand the status of DEDB areas from the results of a policy evaluation.

Before you begin

This task helps you understand the database exceptions that were detected and notified by Policy Services.

This topic assumes that you have received Policy Services exceptions or an Autonomics Director recommendation list as a result of policy-based database monitoring.

To set up policy-based database monitoring and to receive monitoring results, see Scenario: Policy-based database monitoring and tuning.

About this task

This topic and subsequent topics provide a tutorial that guides you through the steps for reviewing the state of DEDB areas.

If, during a policy evaluation, a DEDB area is determined to be in an exceptional state, you must review the area. You must understand the exceptions that were detected, and identify the utility reports that provide you the information to help you analyze the exceptional state of the area.

Various reports that are generated by the IMS Fast Path Solution Pack utilities provide information about space utilization problems, randomizing problems, and the I/O efficiency of root segments and database records. This tutorial explains how each of the utility reports is related to certain exceptions and how you can use these reports to analyze and tune a DEDB area.

The following table summarizes the exception categories and related reports that can help you analyze the exceptions in DEDB areas.

Table 1. Exception categories and the corresponding analysis reports
Exception category Corresponding analysis reports
Free space fragmentation UOW Exception report
Overflow usage Freespace Analysis report
Free space availability Freespace Analysis report
Free space relationship Freespace Analysis report (IOVF and DOVF usage analysis sections)
Root I/O
  • DB Record Profile Analysis report
  • DB Record Placement Analysis report
  • Segment I/O Analysis report
  • Segment Placement Analysis report
Record I/O
  • DB Record Profile Analysis report
  • DB Record Placement Analysis report
  • Segment I/O Analysis report
  • Segment Placement Analysis report
Synonym chain Synonym Chain Analysis report

Consider reviewing not only the current state of the area, but also historical trends for the area. To do so, you can store the utility reports from each run of the utility jobs in the Output repository of IMS Tools Knowledge Base and retain these reports for historical analysis. By storing reports in the Output repository, you can easily locate the list of historical reports for a specific DEDB area.

For more information about reports, see the following resources:
  • For details about the reports that can be generated by each utility and that can be stored in the Output repository, see the topic "Generating the analysis reports" in the IMS Fast Path Solution Pack: IMS High Performance Fast Path Utilities User's Guide.
  • For details about searching the reports by using the IMS Tools Knowledge Base ISPF user interface, see the IMS Tools Base IMS Tools Knowledge Base User's Guide.
  • You can also search the reports by using the graphical interface provided by Management Console. For details about using Management Console, see the Management Console User's Guide.

Procedure

The following steps explain the exceptions in each exception category, and explain how to obtain information that will help you understand why the exceptions occurred.

  1. Determine if the free space in the area is fragmented.
    The following table lists the exceptions that are related to free space fragmentation.
    Table 2. Exceptions that are related to free space fragmentation
    Exception category Exception and exception detection rule Meaning of the exception
    Free space fragmentation DEDB_EXCESSIVE_UOWS_MATCH_COND
    Rule: IBM.DEDB_RFS.10    		 The number of UOWs that match the RBASEFS or the RDOVFFS condition exceeded a threshold.
    DEDB_EXCESS_PCT_UOWS_MATCH_COND
    Rule: IBM.DEDB_RFS.20    		 The percentage of UOWs that match the RBASEFS or the RDOVFFS condition exceeded a threshold.

    The RBASEFS and the RDOVFFS conditions are specified in the database sensor job, and the thresholds are specified in the policy that was used for sensor data evaluation. If you receive one or more of the exceptions that are listed in this table, review the UOW Exception report that was generated when the sensor data was collected.

    Requirement: Rules IBM®.DEDB_RFS.10 and IBM.DEDB_RFS.20 are not included in the IBM provided default policy SYS.DBDTYPE.DEDB. You must add either or both of the rules or create your own policy to use these rules. See the topic "Using Policy Services" in the IMS Tools Base Policy Services User's Guide to customize an existing policy or create a new policy.

    See Analyzing DEDB areas at the UOW level to understand how to interpret each line of the UOW Exception report. Also use the report to identify the UOWs that are were recognized as fragmented.

  2. Determine if the exceptions that you received are related to overflow usage and free space availability.
    The following table lists the exceptions that are related to overflow usage and free space availability.
    Table 3. Exceptions that are related to overflow usage and free space availability
    Exception category Exception and exception detection rule Meaning of the exception
    Overflow usage DEDB_EXCESS_PCT_UOWS_USING_DOVF
    Rule: IBM.DEDB_OVERFLOW.10    		 The percentage of UOWs that are using DOVF exceeded a threshold.
    DEDB_EXCESS_PCT_UOWS_USING_IOVF
    Rule: IBM.DEDB_OVERFLOW.20    		 The percentage of UOWs that are using IOVF exceeded a threshold.
    DEDB_EXCESS_NUM_UOWS_USING_IOVF
    Rule: IBM.DEDB_OVERFLOW.30    		 The number of UOWs that are using IOVF CIs exceeded a threshold.
    DEDB_EXCESS_AVG_IOVF_CI_PER_UOW
    Rule: IBM.DEDB_OVERFLOW.40    		 The average use of IOVF CIs per UOW exceeded a threshold.
    DEDB_UOW_USING_EXCESSIVE_IOVF_CI
    Rule: IBM.DEDB_OVERFLOW.50    		 The maximum use of IOVF CIs by a UOW exceeded a threshold.
    DEDB_EXCESS_MIN_IOVF_CI_PER_UOW
    Rule: IBM.DEDB_OVERFLOW.60    		 The minimum use of IOVF CIs by a UOW exceeded a threshold.
    DEDB_EXCESSIVE_IOVF_CI_USED
    Rule: IBM.DEDB_OVERFLOW.70    		 The percentage of the number of IOVF CIs used exceeded a threshold.
    DEDB_EXCESS_RAP_CI_USING_OVFLOW
    Rule: IBM.DEDB_OVERFLOW.80    		 The percentage of RAP CIs that are using overflow exceeded a threshold.
    DEDB_EXCESSIVE_DBREC_USING_IOVF
    Rule: IBM.DEDB_OVERFLOW.90    		 The percentage of DB records that are using IOVF exceeded a threshold.
    Free space availability DEDB_FREE_SPACE_AVAIL_IN_RAA
    Rule: IBM.DEDB_FS.10    		 The percentage of free space in RAA BASE fell below a threshold.
    DEDB_FREE_SPACE_AVAIL_IN_DOVF
    Rule: IBM.DEDB_FS.20    		 The percentage of free space in DOVF fell below a threshold.
    DEDB_FREE_SPACE_AVAIL_IN_IOVF
    Rule: IBM.DEDB_FS.30    		 The percentage of free space in IOVF fell below a threshold.
    DEDB_FREE_SPACE_AVAIL_IN_SDEP
    Rule: IBM.DEDB_FS.80    		 The percentage of free space in SDEP fell below a threshold.
    Free space relationship DEDB_FREE_SPACE_IN_RAA_VS_DOVF
    Rule: IBM.DEDB_FS.40    		 Free spaces in RAA BASE and DOVF are used inefficiently.
    DEDB_FREE_SPACE_IN_RAA_VS_IOVF
    Rule: IBM.DEDB_FS.50    		 Free spaces in RAA BASE and IOVF are used inefficiently.
    DEDB_FREE_SPACE_AVAIL_IN_OVFLOW
    Rule: IBM.DEDB_FS.60    		 Free spaces in DOVF and IOVF are used inefficiently.
    DEDB_FREE_SPACE_IN_RAA_VS_OVFLOW
    Rule: IBM.DEDB_FS.70    		 Free spaces in RAA, DOVF, and IOVF are used inefficiently.

    If you receive one or more of the exceptions that are listed in this table, review the free space utilization in the Freespace Analysis report that was generated when the sensor data was collected.

    See Analyzing free space usage to understand how to interpret the Freespace Analysis report and to understand the overflow usage and free space availability of the area.

  3. Determine if the exceptions that you received are related to root and record I/Os.

    The following table lists the exceptions that are related to root and record I/Os.

    Table 4. Exceptions that are related to root and record I/Os
    Exception category Exception and exception detection rule Explanation
    Root I/O DEDB_EXCESSIVE_AVG_NUM_ROOT_IO
    Rule: IBM.DEDB_ROOT_IO.10    		 The average number of I/Os that are required to read a root segment exceeded a threshold.
    DEDB_ROOT_SEGMENT_WITH_EXCESS_IO
    Rule: IBM.DEDB_ROOT_IO.20    		 The maximum number of I/Os that are required to read a root segment exceeded a threshold.
    Record I/O DEDB_EXCESSIVE_AVG_NUM_RECORD_IO
    Rule: IBM.DEDB_DBREC_IO.10    		 The average number of I/Os that are required to read a database record exceeded a threshold.
    DEDB_DBRECORD_WITH_EXCESSIVE_IO
    Rule: IBM.DEDB_DBREC_IO.20    		 The maximum number of I/Os that are required to read a database record exceeded a threshold.

    If you receive one or more of the exceptions that are listed in this table, review the database record profile and other relevant reports that were generated when the sensor data was collected.

    See Analyzing database record profile to understand the cause of these I/O exceptions.

  4. Determine if the exceptions that you received are related to synonym chains.

    The following table lists the exceptions that are related to synonym chains.

    Table 5. Exceptions that are related to synonym chains
    Exception category Exception and exception detection rule Meaning of the exception
    Synonym chain DEDB_EXCESSIVE_AVG_LEN_SYNONYMS
    Rule: IBM.DEDB_SYN_LEN.10    		 The average length of RAP synonym chains exceeded a threshold.
    DEDB_LONG_SYNONYM_CHAIN
    Rule: IBM.DEDB_SYN_LEN.20    		 The maximum length of RAP synonym chains exceeded a threshold.

    If you receive one or more of the exceptions that are listed in this table, review the distribution of randomizing synonyms, the relationship with segment overflow, and the effects on I/O efficiency in the Synonym Chain Analysis report that was generated when the sensor data was collected.

    See Analyzing the randomizing routine to understand how to interpret the Synonym Chain Analysis report and to understand the randomizing efficiency.

  5. Optional: Analyze the DEDB area at unit-of-work (UOW) level.

    For more information about free space utilization and database record length at UOW level, generate and review the UOW report.

    See Analyzing DEDB areas at the UOW level to understand how to interpret the UOW report and to learn more about the area at UOW level.

What to do next

After you have reviewed the status of the DEDB area from the utility reports and have identified the causes of the exceptions, the next step is to determine the actions that will resolve the exceptions. Because exceptions tend to be interrelated, you might have received exceptions from multiple categories. When you are determining the actions to resolve these exceptions, consider these exceptions, not as separate exceptions, but rather as a group of interrelated exceptions. See Determining actions to resolve exceptions.