DDF Reports
Strobe for Db2 DDF provides the following reports:
- DDF SQL CPU Usage Summary report shows the distribution of in_DB2 SQL CPU and execution time among DBRMs running in the DDF address space.
- DDF CPU Usage by SQL Statement report shows the distribution of in_DB2 SQL CPU and execution time among SQL statements in a DBRM running in the DDF address space.
- DDF SQL Statement by DBRM by Requester shows the distribution of in_DB2 SQL CPU and execution time among SQL statements in a DBRM by requester. For this report, you can specify one or two of the following requesters: location, correlation ID, authorization ID, End User USERID, End User Workstation, or End User Transaction. See Submitting Measurement Requests for DDF Address Spaces for information on how to assign these values.
Strobe for Db2 DDF automatically suppresses all attribution reporting.
The Strobe for Db2 DDF reports differ from the Strobe for Db2 reports as follows:
- The DDF reports show absolute times instead of percentages.
- The DDF reports do not use histograms.
- The DDF reports use unique SQL statement numbers that are generated while the remote SQL is observed in the DDF address space. The statement numbers assigned at bind time are not always available to Strobe for Db2 DDF and will not guarantee a unique identifier for each SQL statement.
DDF SQL CPU Usage Summary Report
The DDF SQL CPU Usage Summary report shows the total execution (elapsed time) and in_DB2 SQL CPU times used by each DBRM. It breaks down CPU and execution times into average and maximum times.
Execution (elapsed) time is the amount of wall-clock time used by the DBRM or SQL statement. CPU time is the amount of in_DB2 SQL CPU time used by the DBRM or SQL statement. All SQL statement in_DB2 SQL CPU time includes any parallel in_DB2 SQL CPU execution as well as execution on both central and special processors such as a zIIP.
You use this report to identify which DBRMs are using the most in_DB2 SQL CPU or execution time. It summarizes information from the DDF CPU Usage by SQL Statement report (DDF SQL CPU Usage By SQL Statement Report), which provides SQL statement level information, including SQL text.
DDF SQL CPU Usage Summary Report
The DDF CPU Usage by SQL Statement report shows the following fields for each DBRM:
STMT CNT
The total number of SQL statements that ran in the DBRM during the measurement.
AVG EXECUTION TIME
The AVG EXECUTION TIME from the DDF CPU Usage by SQL Statement report for the DBRM. It is the total execution time in seconds for the DBRM divided by the total SQL statement count for the DBRM.
MAX EXECUTION TIME
The MAX EXECUTION TIME from the DDF CPU Usage by SQL Statement report for the DBRM. This is the greatest amount of execution time in seconds used by any one of the SQL statements in the DBRM.
TOTAL EXECUTION TIME
The TOTAL EXECUTION TIME from the DDF CPU Usage by SQL Statement report for the DBRM. It is the AVG EXECUTION TIME in seconds for the DBRM multiplied by the total SQL statement count for the DBRM.
AVG CPU TIME
The AVG CPU TIME from the DDF CPU Usage by SQL Statement report for the DBRM. It is the total in_DB2 SQL CPU time in seconds for the DBRM divided by the total SQL statement count for the DBRM.
MAX CPU TIME
The MAX CPU TIME from the DDF CPU Usage by SQL Statement report for the DBRM. This is the greatest amount of in_DB2 SQL CPU time in seconds used by any one of the SQL statements in the DBRM.
TOTAL CPU TIME
The in_DB2 SQL CPU time in seconds used by this DBRM during the measurement. This includes the in_DB2 SQL CPU time from all parallel execution on both central and special processors.
The TOTALS line shows the following information:
TOTALS STMT CNT
The TOTALS STMT CNT from the DDF CPU Usage by SQL Statement report for all DBRMs. This is the total number of times all SQL statements execute in all of the DBRMs.
TOTALS AVG EXECUTION TIME
The TOTALS AVG EXECUTION TIME from the DDF CPU Usage by SQL Statement report for all DBRMs. It is the total execution time in seconds for all DBRMs divided by the total SQL statement count for all DBRMs.
TOTALS MAX EXECUTION TIME
The TOTALS MAX EXECUTION TIME from the DDF CPU Usage by SQL Statement report for all DBRMs. This is the greatest amount of execution time in seconds used by any one of the SQL statements in all of the DBRMs.
TOTALS TOTAL EXECUTION TIME
The TOTALS TOTAL EXECUTION TIME from the DDF CPU Usage by SQL Statement report for all DBRMs. It is the total execution time in seconds for all DBRMs multiplied by the total SQL statement count for all DBRMs.
TOTALS AVG CPU TIME
The TOTALS AVG CPU TIME from the DDF CPU Usage by SQL Statement report for all DBRMs. It is the total in_DB2 SQL CPU time in seconds for all DBRMs divided by the total SQL statement count for all DBRMs.
TOTALS MAX CPU TIME
The TOTALS MAX CPU TIME from the DDF CPU Usage by SQL Statement report for all DBRMs. This is the greatest amount of in_DB2 SQL CPU time in seconds used by any one of the SQL statements in all of the DBRMs.
TOTALS TOTAL CPU TIME
The in_DB2 SQL CPU time in seconds used by all DBRMs during the measurement. This includes the in_DB2 SQL CPU time from all parallel execution on both central and special processors.
DDF CPU Usage by SQL Statement Report
The DDF CPU Usage by SQL Statement report (See the following figure) shows a detailed account of execution and in_DB2 SQL CPU time usage by SQL statements by DBRM.
For each DBRM that was active during the measurement, this report shows: the DBRM name, the execution time (the first time Strobe detected an SQL statement executing in the DDF address space from the DBRM), the SQL statement counts, as well as the total execution and in_DB2 SQL CPU times used by each SQL statement in a DBRM. It breaks down CPU and execution times into average and maximum times.
You use this report to identify which SQL statements are using the most CPU or execution time.
DDF SQL CPU Usage By SQL Statement Report
When a CALL statement is reported, the fully qualified stored procedure name can be seen in the target statement text. See DDF CPU Usage By SQL Statement Report Showing Stored Procedures for an example of a DDF CPU Usage By SQL Statement report showing stored procedures.
Db2 clients require Db2 system packages to connect to a Db2 server and to perform tasks. On certain client platforms, IBM command-line processors might use a system stored procedure that was distributed as part of the Db2 system packages to execute a user's stored procedure. In these cases, the name of the Db2 system stored procedure and not the name of the user's stored procedure appears as the target name. The code displayed is the code from the Db2 system stored procedure and not the user’s code.
DDF CPU Usage By SQL Statement Report Showing Stored Procedures
SQL Statement Identification
The report lists SQL statements in the DBRM under headings by type:
- Static
- Non-cursor SQL
- Static, Cursor SQL
- Dynamic, Non-cursor SQL
- Dynamic, Cursor SQL.
Strobeuses the actual statement number corresponding to the contents of the Db2 catalog in the STMT NUMBER column, but will generate a unique statement number for the associated SQL Text. Strobe does this to ensure a unique identifier for each SQL statement. This statement number is based on the sequence in which Strobe detected activity for the statement. Strobe for Db2 DDF uses this generated number to correlate an executable statement--for example, OPEN or FETCH--with a corresponding unique number of a DECLARE CURSOR target statement.
Strobe also replaces any literals in the WHERE predicate of dynamic statements and aggregates activity in each SQL statement that has the same row/column name and operand. Literals are replaced with values determined by literal type as follows:
Numeric literals | ???? |
String literals | ‘????’ |
String literals beginning with % | ‘%????’ |
Date literals | ‘????-??-??’ |
Time literals | ‘??:??:??’ |
Timestamp literals and timestamps with timezone | ‘????-??-??-??.??.??.??????’ |
Timezone literal | ‘+/-??:??’ |
All Date and Time formats encountered--that is, ISO/EUR/USA/JIS--are translated to the above format. For example, a USA '12/01/2014' and a EUR '01.12.2014' are both translated to '????-??-??'.
For example, Strobe combines activity in the statements SELECT…WHERE NAME='William' and SELECT…WHERE NAME='Paul', and reports it as SELECT…WHERE NAME='????'. Literals found within an IN clause are reduced to improve aggregation; that is, WHERE NAME IN ('William', 'Paul') and WHERE NAME IN ('Bob', 'Mark', 'Joe') are both reduced to WHERE NAME IN ('????').
Optionally, Strobe retains the literal data (for the most CPU intensive occurrence of each SQL statement) that it replaced when the Strobe PARMLIB option COLLECT_DB2_PRED_DATA is set to ENABLE and L is entered into the Collect DB2 Predicate Data field on the Db2/DDF Data Collector Capture Option ISPF or the Collect DB2 Predicate Data box is checked on the iStrobe Db2 Measurement option window. If enabled and requested, the literal information that is retained is put back into its original place within the SQL text prior to SQL Analysis and iStrobereporting. This feature enables Strobe's SQL Analysis Explain feature (SQLAF) to obtain a more accurate access path by using the data that was actually present at execution time. Use care when enabling this feature as sensitive customer data may be captured and there is no built-in security in Strobe or iStrobe.
CPU and Execution Times
The following sections describe the CPU and Execution Time fields of the DDF CPU Usage by SQL Statement report.
STMT NUMBER
The statement number of the SQL statement, which Strobe generates.
STATEMENT TEXT
The SQL verb of the target statement.
STMT CNT
The number of times the SQL statement ran in this DBRM.
AVG EXECUTION TIME
The total execution time in seconds for this SQL statement divided by the total SQL statement count for this SQL statement in this DBRM.
MAX EXECUTION TIME
The greatest amount of execution time in seconds used by this SQL statement in this DBRM.
TOTAL EXECUTION TIME
The AVG EXECUTION TIME in seconds for this SQL statement multiplied by the total SQL statement count for this DBRM.
AVG CPU TIME
The total in_DB2 SQL CPU time in seconds for this SQL statement divided by the total SQL statement count for this SQL statement in this DBRM.
MAX CPU TIME
The greatest amount of in_DB2 SQL CPU time in seconds used by this SQL statement in this DBRM.
TOTAL CPU TIME
The in_DB2 SQL CPU time in seconds used by this SQL statement during the measurement. This includes the in_DB2 SQL CPU time from all parallel execution on both central and special processors.
DBRM TOTALS STMT CNT
The cumulative number of times all remote SQL statements executed for this DBRM.
DBRM TOTALS AVG EXECUTION TIME
The total execution time in seconds for this DBRM divided by the total SQL statement count for this DBRM.
DBRM TOTALS MAX EXECUTION TIME
The greatest amount of execution time in seconds used by any one of the SQL statements in this DBRM.
DBRM TOTALS TOTAL EXECUTION TIME
The total execution time in seconds for this DBRM multiplied by the total SQL statement count for this DBRM.
DBRM TOTALS AVG CPU TIME
The total in_DB2 SQL CPU time in seconds for this DBRM divided by the total SQL statement count for this DBRM.
DBRM TOTALS MAX CPU TIME
The greatest amount of in_DB2 SQL CPU time in seconds used by any one of the SQL statements in this DBRM.
DBRM TOTALS TOTAL CPU TIME
The total in_DB2 SQL CPU time in seconds used by this DBRM during the measurement. This includes the in_DB2 SQL CPU time from all parallel execution on both central and special processors.
DDF SQL Statement by DBRM by Requester Reports
You can produce the DDF SQL Statement by DBRM by Requester report in several different formats, depending on the values specified for the requester when Strobe was installed or a measurement request was submitted. The requester is the source of the SQL that is executing in the DDF address space. You can produce a report using either one or two of the following types of requesters:
- Location
- Correlation ID
- Authorization ID
- End User’s Workstation
- End User’s Transaction
- End User’s USERID.
See Submitting Measurement Requests for DDF Address Spaces about how to specify values for requesters.
For each requester that was active during the measurement, this report shows: the DBRM name, the execution time, the SQL statement counts, as well as the total execution and in_DB2 SQL CPU times used by each SQL statement in a DBRM. It breaks down CPU and execution times into average and maximum times.
When two requester types are specified, the report treats the combination as a single unique requester. The report provides totals for each DBRM as well as totals for all the DBRMs from the combined requesters. For an example of a requester report with two requester types, see .
When a CALL statement is reported, the fully qualified stored procedure name can be seen in the target statement text.
The following sections give examples of the SQL Statement by DBRM by Requester reports and describe the fields common to the reports.
DDF SQL Statement by DBRM by Requester Report by Location
The DDF SQL Statement by DBRM by Requester report by location (See the following figure) shows a detailed account of the execution and in_DB2 SQL CPU time usage for each SQL statement by DBRM and location. The location is the IP address or LU name of the requester executing the SQL in the DDF address space.
DDF SQL Statement by DBRM by Requester by Location Report
DDF SQL Statement by DBRM by Requester Report by Correlation ID
The DDF SQL Statement by DBRM by Requester report by correlation ID (See the following figure) shows a detailed account of the execution and in_DB2 SQL CPU time usage for each SQL statement by DBRM and correlation ID. The correlation ID is the correlation ID of the requester executing the SQL in the DDF address space. The report displays only the printable characters of a correlation ID.
DDF SQL Statement by DBRM by Requester Report by Correlation ID
DDF SQL Statement by DBRM by Requester Report by Authorization ID
The DDF SQL Statement by DBRM by Requester report by authorization ID (See the following figure) shows a detailed account of the execution and in_DB2 SQL CPU time usage for each SQL statement by DBRM and authorization ID. The authorization ID is the ID of the requester executing the SQL in the DDF address space.
DDF SQL Statement by DBRM by Requester Report by Authorization ID
DDF SQL Statement by DBRM by Requester Report by End User’s USERID
The DDF SQL Statement by DBRM by Requester report by end user’s USERID (See the following figure) shows a detailed account of the execution and in_DB2 SQL CPU time usage for each SQL statement by DBRM and end user’s USERID. The end user’s USERID is the ID of the requester executing the SQL in the DDF address space.
DDF SQL Statement by DBRM by Requester Report by End User’s USERID
DDF SQL Statement by DBRM by Requester Report by End User’s Workstation
The DDF SQL Statement by DBRM by Requester report by end user’s workstation (See the following figure) shows a detailed account of the execution and in_DB2 SQL CPU time usage for each SQL statement by DBRM and end user’s workstation. The end user’s workstation is the workstation of the requester executing the SQL in the DDF address space.
DDF SQL Statement by DBRM by Requester Report by End User’s Workstation
DDF SQL Statement by DBRM by Requester Report by End User’s Transaction
The DDF SQL Statement by DBRM by Requester report by end user’s transaction (See the following figure) shows a detailed account of the execution and in_DB2 SQL CPU time usage for each SQL statement by DBRM and end user’s transaction. The end user’s transaction is the transaction issued by the requester executing the SQL in the DDF address space.
DDF SQL Statement by DBRM by Requester Report by End User’s Transaction
DDF SQL Statement by DBRM by Requester Report with Requester Location and Correlation ID
The DDF SQL Statement by DBRM by Requester report with Requester Location and Correlation ID (See the following figure) shows a detailed account of the execution and in_DB2 SQL CPU time usage for each SQL statement in a DBRM for the requester, as well as totals for all the DBRMs from the combined requesters: location and correlation ID.
DDF SQL Statement by DBRM by Requester Report with Requester Location and Correlation ID
DDF SQL Statement by DBRM by Requester Report Fields
The following sections describe the fields that appear in all of the DDF SQL Statement by DBRM by Requester reports.
SQL Statement Identification
The DDF SQL Statement by DBRM by Requester report does not provide any SQL statement text. To identify SQL statement text, see .
STMT NUMBER
The statement number of the SQL statement as generated by Strobe for DB2 DDF.
STMT TEXT
The SQL verb of the target statement.
STMT CNT
The number of times the SQL statement ran in this DBRM from this requester.
CPU and Execution Times
The following sections describe the CPU and Execution Time fields of the DDF SQL Statement by DBRM by Requester report.
AVG EXECUTION TIME
The total execution time in seconds for this SQL statement divided by the total SQL statement count for this SQL statement in this DBRM from this requester.
MAX EXECUTION TIME
The greatest amount of execution time in seconds used by this SQL statement in this DBRM from this requester.
TOTAL EXECUTION TIME
The AVG EXECUTION TIME in seconds for this SQL statement multiplied by the total SQL statement count for this SQL statement in this DBRM from this requester.
AVG CPU TIME
The total in_DB2 SQL CPU time in seconds for this SQL statement divided by total SQL statement count for this SQL statement in this DBRM from this requester.
MAX CPU TIME
The greatest amount of in_DB2 SQL CPU time in seconds used by this SQL statement in this DBRM from this requester.
TOTAL CPU TIME
The in_DB2 SQL CPU time in seconds used by this statement from this requester during the measurement. This includes the in_DB2 SQL CPU time from all parallel execution on both central and special processors.
DBRM TOTALS STMT CNT
The cumulative number of times all remote SQL statements executed for this DBRM.
DBRM TOTALS AVG EXECUTION TIME
The total execution time in seconds for this DBRM divided by the total SQL statement count for this DBRM from this requester.
DBRM TOTALS MAX EXECUTION TIME
The greatest amount of execution time in seconds used by any one of the SQL statement in this DBRM from this requester.
DBRM TOTALS TOTAL EXECUTION TIME
The total execution time in seconds for this DBRM multiplied by the total SQL statement count for this DBRM from this requester.
DBRM TOTALS AVG CPU TIME
The total in_DB2 SQL CPU time in seconds for this DBRM divided by the total SQL statement count for this DBRM from this requester.
DBRM TOTALS MAX CPU TIME
The greatest amount of in_DB2 SQL CPU time in seconds used by any one of the SQL statements in this DBRM from this requester.
DBRM TOTALS TOTAL CPU TIME
The total in_DB2 SQL CPU time in seconds used by this DBRM from this requester during the measurement. This includes the in_DB2 SQL CPU time from all parallel execution on both central and special processors.
REQ TOTALS STMT CNT
The cumulative number of times all remote SQL statement executed from this requester.
REQ TOTALS AVG EXECUTION TIME
The total execution time in seconds for all DBRMs divided by the total SQL statement count for all DBRMs from this requester.
REQ TOTALS MAX EXECUTION TIME
The greatest amount of execution time in seconds used by any one of the SQL statements across all of the DBRMs from this requester.
REQ TOTALS TOTAL EXECUTION TIME
The total execution time in seconds for all DBRMs multiplied by the total SQL statement count for all DBRMs from this requester.
REQ TOTALS AVG CPU TIME
The total in_DB2 SQL CPU time in seconds for all DBRMs divided by the total SQL statement count for all DBRMs from this requester.
REQ TOTALS MAX CPU TIME
The greatest amount of in_DB2 SQL CPU time in seconds used by any one of the SQL statements across all of the DBRMs from this requester.
REQ TOTALS TOTAL CPU TIME
The total in_DB2 SQL CPU time in seconds used by all DBRMs from this requester during the measurement. This includes the in_DB2 SQL CPU time from all parallel execution on both central and special processors.