Help

 

Db2 Activity by Package

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

The Db2 Activity by Package report contains detailed reporting of SQL statements within all Packages that are invoked by SQL executing in the measured 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. Calling of Stored Procedures and invocation of Triggers are also reported.

Report details

  • Package shows the name of the Package.

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

  • 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 is the count of all SQL activity execution in the Package.

  • Elapsed Time (sec) Average 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 activity execution 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 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 SQL statement activity execution in this Package.

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

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

  • CPU % is a percentage of this Package’s CPU Time (sec) Total to the CPU Time (sec) utilized by all SQL statements in this measurement.

  • Wait % shows the percentage of wait time samples associated with the Package as a percentage of all wait time samples associated with the measured job step.

Click next to a Package name to view execution details on the target SQL statements within a Package. The target statement identifies the cursor or singleton statement that defines the SQL to be executed. The executing statement refers to the actual SQL statement activity for the target statement, such as PREPARE, OPEN, FETCH, CLOSE, INSERT, etc. For a static singleton statement, the target and the executing statement are the same.

  • 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 is -1.

  • Statement Type shows whether the statement was:

  • Statement Count is the count of all SQL activity execution for the target SQL statement (e.g. 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 processors 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.

  • CPU % is a percentage of this Target SQL statement CPU Time (sec) Total to the CPU Time (sec) utilized by all SQL statements in this measurement.

  • Wait % shows the percentage of wait time samples associated with the Target SQL statement as a percentage of all wait time samples associated with the measured job step.  

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 appear 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 all executions of this SQL statement activity.

  • CPU % is a percentage of this SQL statement activity CPU Time (sec) Total to the CPU Time (sec) utilized by all SQL statements in this measurement.

  • Wait % shows the percentage of wait time samples associated with this SQL statement activity a percentage of all wait time samples associated with the measured job step.

Click next to an Executing Statement to view any Runtime statistics or Metrics for the SQL statement activity. Metric data is only 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 Services Invoked to view details about the services invoked by the statement. [MORE]

Click next to Statement Metrics to view statement 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 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 = Procedure

    • F = Function

    • A = AuthLE 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.