Help

 

Db2 DDF SQL Activity

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

The Db2 DDF SQL Activity report contains detailed reporting of SQL statements executing as remote SQL requests in a measured DDF address space during a STROBE measurement session. The report data is a comprehensive reporting of CPU consumption and elapse times as well as SQL statement metrics and Runtime Statistics. Further calling of Stored Procedures and invocation of Triggers are also reported.

The report is organized by package. Each package can be expanded for target SQL statement detail and each target SQL statement can be expanded for executing SQL statement detail.

The Db2 DDF SQL Activity report shows the distribution by Package of CPU and elapsed time used by SQL statements within a Package that ran in the DDF address space during a Strobe measurement session. The SQL statements are broken down by requester at the lowest level.

For performance profiles with large amounts of DDF activity, click Next page and Previous page to view more data.

If you notice detail missing from the DDF reports, check the DDF threshold parameters that appear on the Measurement Session Data report.

Report details

  • Package shows the name of the Package.

  • Bind Timestamp shows the date and time that the package was bound.

  • CONTOKEN (Consistency token) is an 8-byte hexadecimal value generated by the Db2 pre-compiler or co-processor that is placed into both the Package and object module as a part of the parameter list for each EXEC SQL call encountered in the source program.

  • Statement Count shows the count of all SQL activity execution in the package.

  • Elapsed time (sec) Avg shows the average elapsed time in seconds for all SQL activity execution for the package.

  • Elapsed Time (sec) Max shows the greatest amount of elapsed time in seconds by any one SQL statement in the package.

  • Elapsed Time (sec) Total shows total elapsed time in seconds for all SQL activity in the package.

  • Parallel Task CPU Distribution (sec) SP CPU shows the CPU time in seconds executed on a specialty processor such as a zIIP while in parallel processing for all of the SQL statements in the package.

  • Parallel Task CPU Distribution (sec) Total CPU shows the CPU time in seconds executed in parallel processing on all processors for all of the SQL statements in the package.

  • CPU Time (sec) Average  shows the total CPU time in seconds utilized by all SQL in this package, divided by the total SQL statement activity count for this package.

  • CPU Time (sec) Max shows the total CPU time in seconds used by any one of the SQL statement activity executions in this package.

  • CPU time (sec) Total shows total CPU time in seconds utilized by all SQL statement activity execution in this package.

  • CPU Time (sec) SP shows the total amount of Special Processor CPU time utilized by all SQL statement activity execution in this package.

Click next to a package to view execution details on the target SQL statements within the package. The target statement identifies the cursor or singleton statement that defines the SQL to be executed.

  • Target Statement identifies a target SQL statement including all of its executing components.

  • Statement Number:  For static non-cursor statements, the report displays the statement numbers that appear in the application code. For both static cursor statements and dynamic statements, the report shows the statement numbers that Strobe generates.

  • QUERYNO

  •  For STATIC SQL this is the QUERYNO from the Db2 system catalog

  •  For Dynamic SQL the value will be -1.

  • Statement Type shows whether the statement was:

  • Statement Count shows the number of times the statement executed during the measurement session.

  • Statement Count is a count of all SQL activity execution for the target SQL statement (activity such as Prepare, Open, Fetch, Close, Insert, Update, Delete).

  • Elapsed Time (sec) Average shows the average elapsed time in seconds of all SQL activity execution in the target SQL statement.

  • Elapsed Time (sec) Max shows the greatest amount of elapsed time in seconds used by any one executing SQL statements activity within the Target SQL statement.

  • Elapsed Time (sec) Total shows total elapsed time in seconds for all of the executing elements of the Target SQL statement.

  • Parallel Task CPU Distribution (sec) SP CPU shows the utilized CPU time in seconds executed on a specialty processor such as a zIIP while in parallel processing for the Target SQL statement.

  • Parallel Task CPU Distribution (sec) Total CPU shows the utilized CPU time in seconds executed in parallel processing on all processor types for the Target SQL statement.

  • CPU Time (sec) Average shows the average utilized CPU time in seconds of all SQL activity execution in the target SQL statement.

  • CPU Time (sec) Max shows the maximum utilized CPU time in seconds used by any one SQL activity execution of the target SQL statement.

  • CPU Time (sec) Total shows the total amount of CPU time in seconds utilized by all SQL activity execution of the target SQL statement.

  • CPU Time (sec) SP shows the total amount of Specialty Processor CPU time used all SQL activity execution of the target SQL statement.

Click next to a Target Statement to view the executing SQL statement detail and any invoked trigger or called stored procedure detail.

  • SQL Statement Text shows approximately the first 50 characters of the SQL statement text. It may be followed by a measurement determined qualifier (used by Strobe’s SQL Analysis feature (SQLAF) to qualify any unqualified objects prior to performing its dynamic explain) and the SQLAF report link. The complete SQL statement text can be viewed by clicking .

  • The measurement determined qualifier for static SQL will always be the QUALIFIER column, retrieved from the SYSIBM.SYSPACKAGE Db2 catalog table for the Package. Measurement does not parse static SQL. If the statement contains fully qualified SQL, the QUALIFIER value will still be shown.  However, SQLAF will not modify the SQL statement to use the QUALIFIER value prior to the dynamic explain.

  • The measurement determined qualifier for dynamic SQL, is based on a simple statement parse, or the DYNAMICRULES BIND parameter. If the simple statement parse determines that the statement is already fully qualified, then no QUALIFIER will be shown or used by SQLAF. Otherwise, the QUALIFIER for SQLAF to use will be determined based on the following chart:

DYNAMICRULES Type Qualifier

BIND

INVOKEBIND

DEFINEBIND

Non-Stored Procedures

QUALIFIER column, retrieved from the SYSIBM.SYSPACKAGE Db2 catalog table for the Package

RUN

INVOKERUN

DEFINERUN

Non-Stored Procedures

Stored Procedures

Authorization ID active at the time that the SQL statement was executed

BIND

Stored Procedures

QUALIFIER column, retrieved from the SYSIBM.SYSPACKAGE Db2 catalog table for the Package

INVOKEBIND

DEFINEBIND

Stored Procedures

OWNER column, retrieved from the SYSIBM.SYSROUTINES Db2 catalog table for the stored procedure

 There may be instances when the value in the QUALIFIER field is incorrect. For example, when the measurement was started with an ADD ACTIVE and the beginning of the Stored Procedure was missed.

Click SQLAF to view reports that provide detailed Db2 SQL statement access path explanations and resource consumption information. This button appears only if these reports were requested when the Strobe performance profile was created.

  • Executing statement detail

  • Executing Statement identifies the SQL activity (Prepare, Open, Fetch, Close, Insert, Update, Delete).

  • Statement Number displays the statement numbers that appears in the application code.

  • Statement Count shows the number of times this SQL statement activity executed.

  • Elapsed Time (sec) Average shows the average elapsed time in seconds of all executions of this SQL statement activity.

  • Elapsed Time (sec) Max shows greatest amount of elapsed time in seconds used by any one execution of this SQL statement activity.

  • Elapsed Time (sec) Total shows total elapsed time in seconds for all of the executions of this SQL statement activity.

  • Parallel Task CPU Distribution (sec) SP CPU shows the CPU time in seconds executed on a specialty processor such as a zIIP while in parallel processing for the SQL statement activity.

  • Parallel Task CPU Distribution (sec) Total CPU shows the CPU time in seconds executed in parallel processing on all processors for this SQL statement activity.

  • CPU Time (sec) Average shows the average utilized CPU time in seconds of all executions of this SQL statement activity.

  • CPU Time (sec) Max shows the maximum utilized CPU time in seconds used by any one execution of this SQL statement activity.

  • CPU Time (sec) Total shows the total amount of CPU time in seconds utilized by all executions of this SQL statement activity.

  • CPU Time (sec) SP shows the total amount of Specialty Processor CPU time used for all executions of this SQL statement activity.

Click next to an Executing Statement to view Runtime statistics or Metrics for the SQL statement activity. Metric data is produced for the CURSOR CLOSE and Singleton SQL.

  • Runtime statistics for scans executed by this SQL statement activity.

    • Type identifies the type of scan. INDX means index scan, SEQD means sequential data scan, and SEQW means sequential data work file scan.

    • Rows Processed lists the number of rows that were processed by the scan.

    • Rows Examined lists the number of rows that were examined by the scan. However, if Type is INDX, Rows Examined is the number of index entries scanned.

    • Rows-Q Stage 1 indicates how many rows qualified at stage 1.

    • Rows-Q Stage 2 indicates how many rows qualified at stage 2.

    • Rows Inserted lists how many rows were inserted.

    • Rows Deleted lists how many rows were deleted. MASS indicates a mass delete.

    • Rows Updated lists how many rows were updated.

    • Get Page Requests tells how many page requests the data manager sent to the buffer manager. The value for an index scan includes index pages scanned but not index subpages scanned.

    • RI Page Scans show the number of additional pages scanned (for referential integrity).

    • RI Page Deletes show the number of additional rows deleted (for referential integrity).

    • LOB Page Scans lists the number of additional pages that were scanned in an LOB table space.

    • LOB Page Updates lists the number of LOB pages that were updated by either SQL INSERT or SQL UPDATE.

Click next to Statement Metrics to view metrics:

  • In-DB2 times is time spent within Db2.

  • In-DB2 CPU(sec) SP+CP is the accumulated In_DB2 CPU time (central processor and IBM specialty engine) in seconds used by the SQL statement.

  • In-DB2 Elapsed (sec) is the accumulated In_DB2 elapsed time in seconds for the SQL statement.

  • Wait Times (sec) is the elapsed wait time in seconds that this SQL statement incurred within Db2.

  • Synchronous I/O is the accumulated wait time for synchronous I/O for the SQL statement.

  • Lock is the accumulated wait time for lock for the SQL statement.

  • Sync Execution Unit Switch is the accumulated wait time for synchronous execution switch for the SQL statement.

  • Global Locks is the accumulated wait time for global locks for the SQL statement.

  • Read by Competing Thread is the accumulated wait time for read activity done by another thread for the SQL statement.

  • Write by Competing Thread is the accumulated wait time for write activity done by another thread for the SQL statement.

  • Latch is the accumulated wait time for latch for the SQL statement.

  • Page Latch is the accumulated wait time for page latch for the SQL statement.

  • Drain Lock is the accumulated time for drain lock for the SQL statement.

  • Claim Release is the accumulated wait time for claim count for the SQL statement.

  • Log Writer is the accumulated wait time for log writer for the SQL statement.

  • Execution Statistic Counts is the count of events occurred by the SQL statement.

  • Synchronous Buffer Reads is the number of synchronous buffer reads for the SQL statement.

  • Getpages is the number of Getpage operations for the SQL statement.

  • Rows Examined is the number of rows examined for the SQL statement.

  • Rows Processed is the number of rows processed for the SQL statement.

  • Sorts is the number of sorts for the SQL statement.

  • Index Scans is the number of scans of an index for the SQL statement.

  • TableSpace Scans is the number of scan operations for table spaces performed by the SQL statement.

  • Buffer Writes is the number of buffer writes for the SQL statement.

  • Parallel Groups Created is the number of parallel groups created for the SQL statement.

  • RID Not Used – Exceeded Limits is the number of times the RID (Record ID) list was not used because the number of RIDs would have exceeded Db2 limits for the SQL statement.

  • RID Not Used – Unavailable storage is the number of times a RID (Record ID) list was not used because there was not enough storage available to hold the list of RIDs for the SQL statement.

Click next to Requesters to view additional detail about the source of the SQL that is executing in the DDF address space. One or two of the following values can appear for the requester on this report: location, correlation ID, authorization ID, end user’s USERID, end user’s workstation, or end user’s transaction.  If the requester is a location, its IP address or LU name in the SNA network is displayed.

  • Requester Location

  • Authorization ID

  • Correlation ID

  • End User ID

  • End User Workstation

  • End User Transaction

Click next to Triggers Invoked to view additional detail about the triggers invoked on behalf of this SQL statement:

  • Schema shows the trigger's schema name.

  • Trigger Name is the name of the trigger.

  • Activation indicates the trigger activation time (B for before, A for after).

  • Granularity indicates whether trigger granularity is for each row (R) or for each statement (S).

  • Condition is the trigger action condition evaluation. T means that the triggered action tested true, while F means that it tested false and N means that there was no action condition.

  • Nest Level lists the trigger's nesting level.

  • Type indicates the type of SQL statement that invoked the trigger. I means INSERT, U means UPDATE, and D means DELETE.

  • Count lists the number of times the trigger was invoked by the executing SQL statement during the measurement.

Click next to Stored Procedures Invoked to view additional detail about the procedures invoked on behalf of this SQL statement:

  • Schema shows the procedure's schema name.

  • Procedure Name is the name of the procedure.

  • Nest Level lists the procedure's nesting level.

  • Type indicates the type of SQL statement that invoked the procedure. P means procedure, F means function, and A means auth routine.

  • Count lists the number of time the stored procedure was invoked by the executing SQL statement during the measurement.

Analysis tip:  For additional information about analyzing Db2 Performance Profiles, see Db2 Application Performance Analysis.