Skip to Content

How Workload Statistics Advisor works

Statistics Advisor runs as a three-step batch job. The following figure illustrates the process.

GUID-3A5C984C-8D26-491E-A82C-7D28C1A9D780-low.png

Following is a summary of each step in the process:

  1. Extract—Statistics Advisor extracts a workload and compiles a collection of SQL statements into a SQLTXT file. The workload source can be any of the following items:
    • Performance Advisor Database (PADB)
    • Apptune archive
    • Db2 catalog
    • DBRM library
    • Db2 statement cache
    • Text file

  2. Explain—Statistics Advisor interacts with Db2 to:

    • Perform Explains
    • Collect current catalog and real-time statistics for the statements to the workload

    Statistics Advisor stores the Explain results in the:

    • PATH file—containing access path and cost estimates
    • (Db2 Versions 11 and later) FDBK file—containing statistics recommendations from the optimizer
    • OBJECT file—containing statistics for the objects participating in the workload

  3. Recommend—Statistics Advisor:

    • Generates the Performance Advisory
    • Recommends catalog statistics updates that would benefit the workload

    The Performance Advisory appears in the batch output. Its results are incorporated into the PADB table PERF_ADVISORY.

    The recommendations appear as:

    • BMC AMI Stats for Db2 and IBM RUNSTATS utility statements in the RSTAT file
    • BMCSTATS statements in the BMCSTATS file
    • Rows in the STATS_CANDIDATE table

GUID-47E5A770-A760-442A-9F5A-06872981813D-low.png

For more information, view the Quick Course BMC Performance for DB2 SQL—Workload Statistics Advisor.

Related topic

Workload-Statistics-Advisor-component


 

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

SQL Performance for DB2 12.1