Using the Index Component

The Index Component of 

SQL Performance for DB2

 extends the capability of 

BMC AMI Apptune for Db2

 object analysis by collecting and reporting on column usage data for SQL statements.

It also extends the capability of the Explain function by comparing access paths after making changes to indexes in a cloned database.

The Index Component offers users a way to obtain accurate, real-time performance information about Db2 indexes. Because the Index Component presents data at the object level, you can review the index access data to evaluate the performance of your indexes and identify candidates for index improvements. You can use the Index Component to analyze index activity from the following views:

  • Subsystem
  • Object
  • Application group

You can initiate Index Component reports by using an initial index report, or through the Object Analysis report (SQMCACTO) in BMC AMI Apptune for Db2. For more information, see Index-Component-reports.

Note

To use the Index Component, you must select Y for Object data in the filter option set.

For more information about the filter option set, see the System and SQL Performance for DB2 Administration.

Using SQL Performance, you can invoke Explain and What-If Index from any location in the product where SQL text is visible.

Dynamic Explain requests now use the ADMIN_EXPLAIN_MAINT IBM Db2 stored procedure. This stored procedure can create or update Explain tables to the correct format for the version of Db2 that you are running. It can also create the indexes recommended by IBM on the Explain tables.

Ensure that the ADMIN_EXPLAIN_MAINT stored procedure is installed correctly for successful Explains.

Installing the ADMIN_EXPLAIN_MAINT stored procedure

For Db2 Version 11 or later, use the following procedure to install the ADMIN_EXPLAIN_MAINT stored procedure:

  1. Create a JCL startup procedure for the IBM z/OS Workload Manager (WLM) environment, if you do not already have one.
  2. Set up the IBM stored procedure using the DSNTESR member from the Db2 DSNSAMP data set. Perform the following steps:
    1. Create the following global temporary tables:
      • SYSIBM.EXPLAIN_MAINT_SUMMARY
      • SYSIBM.EXPLAIN_MAINT_SQL
      • SYSIBM.EXPLAIN_MAINT_TB_NOT_UPGRADED
    2. Define the SYSPROC.ADMIN_EXPLAIN_MAINT stored procedure by specifying your WLM environment and the COLLID for the DSN% packages.
    3. Issue a GRANT ALL to PUBLIC authority to the global temporary tables.
    4. Issue a GRANT EXECUTE to PUBLIC authority to the stored procedure.
    5. Remove the comments from the bind statement and run the bind for the DSNADMEM package, specify the DSNDBRM Db2 library, and perform the bind.
  3. Activate the WLM environment.
  4. Start the stored procedure.

For more information, see the IBM Db2 for z/OS installation documentation.

For information about the authorities you require for Workload Advisor Explains, see Required-authorities-for-Workload-Advisor-Explains-and-the-Index-Advisor-feature.

The tasks in this section demonstrate different approaches to analyzing index performance, as follows:

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*