Advisor DB2 Tab

This section describes the Db2 Tab.

All SQL execution values are provided by Db2 and captured as SQL statements are executing. They are not a product of sampling.

The Strobe Advisor for Db2 report displays recommendations for the 10 top CPU consuming SQL Statements by CPU Time. Only the top 10 statements will be reported. Each is identified by a heading line with identifying information. By clicking on the statement heading line, the report for that statement will be expanded or collapsed.

You can create a note for this particular report and profile by simply clicking notes-none.gif at the top of the page. The note created is exclusive to this report and can only be accessed from this report and profile. Other users can edit the note.

Click report.png on an SQL ribbon to view the appropriate Db2 Profile report.

The report has five sections:

SQL Activity Detail includes these data elements:

  • Package identifies the name of the package.
  • Trigger/Stored Procedure identifies the trigger or stored procedure name if the SQL statement belongs to a trigger or stored procedure.
  • Statement Number identifies the SQL statement number.
  • QUERYNO identifies the query number from the catalog for STATIC SQL else it is -1.
  • Statement Type identifies whether the statement was:
    • SNC = Static non-cursor
    • SC = Static cursor
    • DNC = Dynamic non-cursor
    • DC = Dynamic cursor
  • Statement Verb is the work which the SQL statement is executing (PREPARE, OPEN, FETCH, CLOSE, INSERT, DELETE, UPDATE).
  • Statement Count identifies the count of this SQL statement over the entire measurement.
  • Total elapsed time identifies the elapsed time this SQL statement was executing over the entire measurement.
  • Total CPU time identifies the CPU time used by this SQL statement over the entire measurement.
  • COST_CATEGORY identifies values where A indicates that Db2 has enough information to make a cost estimate and B indicates that some conditions exist for which Db2 was forced to use default values.
  • PROCMS identifies the estimated processor cost in milliseconds, for the SQL statement. The estimate is rounded up to the next integer value. The maximum value for this cost is 2147483647 milliseconds, which is equivalent to approximately 24.8 days. If the estimated value exceeds this maximum, the maximum value is reported. If an accelerator is used, the difference is reflected in this value.
  • PROCSU identifies the estimated processor cost in service units, for the SQL statement. The estimate is rounded up to the next integer value. The maximum value for this cost is 2147483647 service units. If the estimated value exceeds this maximum, the maximum value is reported. If an accelerator is used, this value represents the estimated cost including any impact of acceleration.
  • REASON identifies a value that indicates the reasons for putting an estimate into cost category B.
  • TOTAL_COST identifies the overall estimated cost of the statement. If an accelerator is used, the benefit is reflected in this value. Use this value for reference purposes only.

SQL text identifies the SQL text as captured by Strobe.

Strobe Advisor recommendations identifies those recommendations obtained from available sources:

  • Access path obtained from the EXPLAIN and Db2 Statement Metrics if the Db2 release is Db2 V10 or greater.
  • List of available indexes obtained from the catalog.
    • Indexes that are not part of the system catalog, but created in conjunction with optimization tools, will not be listed even though they may be part of the Db2 EXPLAIN output.
  • List of recommendations obtained from the Db2 real-time catalog statistics.
    • Optimization tool indexes are not considered for recommendations.
  • Generated predicate information.
  • Recommendations to execute RUNSTATS based on EXPLAIN feedback.
  • Notification of failure when an attempt is made to run a query on an accelerator.
  • Information and error messages reporting Strobe’s SQL Analysis (SQLAF) issues trying to qualify unqualified objects.

EXPLAIN output for the SQL statement contains Plan Table Rows, Predicate Table Rows, and Catalog Statistics Feedback Table Rows. It is identical to the output seen in the SQLAF Explain report and each data element description can be seen in help for EXPLAIN Output for SQL statement.

Db2 metrics for the SQL statements. It is identical to the Statement Metrics section for all SQL Activity reports. The SQL Activity report help panel describes the various data elements.

 

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