Help

 

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 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 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.

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.