r7 - 2023-03-28 - 19:04:25 - PaulEllisYou are here: TWiki >  Deployment Web > DeploymentTroubleshooting > PerformanceTroubleshooting > CollectingDB2SectionActualsUsingRepodebug

Collecting Section Actuals Using Repodebug new.png

Authors: GaryJohnston
Build basis: Engineering Lifecycle Management products 7.0.2SR1, 7.0.1 ifix 18

This document describes the new functionality that went into ELM 7.0.3 Sprint 19 via Task DB2: Enable collection of section actuals for SQL executed from ELM (549317). This functionality was backported to ELM 7.0.2 ifix015/SR1 and also 7.0.1 ifix 18 Note: Much of this new functionality is available for all supported database types, not just DB2. Functionality available only for DB2 is marked “[DB2 ONLY]”.

The Db2MustGather page describes how to generate query execution plans on DB2 and get the actual row counts for the operators in the execution plan manually. We have now streamlined this in a way that better integrates with the SQL statement activity event monitoring provided by the queryStats page in the repodebug facility. This allows IBM Support and/or Development to more easily get DB2 query execution plans with actual row counts and cardinalities from ELM deployments without first having to manually construct standalone SQL files (including bind parameter values, etc.).

Note: This article assumes that you have enabled repodebug.

It is expected that you are using this article in conjunction with IBM Support for IBM Engineering Lifecycle Management (ELM). A DBA is expected to be assisting with the use of any additional monitoring output from Oracle, for example using the Oracle mustgather fopr SQL Monitor reports. A Db2 DBA may be required for the Using Db2 batch.

Summary of New Functionality

The new functionality is provided through the application's queryStats repodebug page. You can get to the queryStats page from the top-level repodebug page at https://server:9443/application/repodebug (where server is the hostname of the application server and application is the application (e.g., rm, qm, etc.)) by clicking the database link and then the queryStats link.

On the queryStats Main Page

If query stats collection has been started and some query stats have been collected, the statRecordsEnabled column indicates whether or not “stat records” are being collected for each listed query when it is executed. A “stat record” captures details about a single execution of a query, including its parameter values. It supports being able to re-execute the query from repodebug (as described below). Initially, no query has stat records enabled. It must be enabled individually for each query of interest. From the queryStats main page, you can get to the queryStats page for an individual query by clicking on its hash value link.

On the queryStats page for an individual query


The statRecordsEnabled value indicates whether or not stat records are being collected for the query when it is executed. The statRecords table summarizes the stat records that have been collected for the query. The table has the following columns:

  • executeComplete – a timestamp indicating when the query execution completed.
  • executeTime – the number of milliseconds it took the query to execute.
  • fetchTime – the number of milliseconds it took to fetch the query’s results (if applicable).
  • totalTime – the sum of the executeTime and fetchTime.
  • rowCount – the number of rows processed by the query (if applicable).
The statRecords table will contain rows for up to the 10 most recent stat records collected. The ENABLESTATRECORDS and DISABLESTATRECORDS links above the table can be used to enable or disable, respectively, stat records being collected for the query when it is executed. You can click on a statRecords executeComplete timestamp link to get to the stat record page for a specific query execution.

On the stat record page for a specific query execution


This page shows the stat record values that were collected for the query execution:

  • hash – the hash value of the query.
  • executeComplete – a timestamp indicating when the query execution completed.
  • sql – the full SQL statement that was executed.
  • read-only – the read-only state of the database connection at the time the query was executed.
  • transactionIsolation – the transaction isolation level of the database connection when the query was executed (one of read uncommitted, read committed, repeatable read, or serializable).
  • executeTime – the number of milliseconds it took the query to execute.
  • fetchTime – the number of milliseconds it took to fetch the query’s results (if applicable).
  • totalTime – the sum of the executeTime and fetchTime.
  • rowCount – the number of rows processed by the query (if applicable).
  • parameterMetaData – information about the data type of each parameter. May not be available for some queries on some databases.
  • parameters – the values of the parameters passed for this execution of the query.

The RE-EXECUTE link above the table can be used to re-execute the query with the same parameter values.
[DB2 ONLY] The RE-EXECUTE AND COLLECT ACTUALS link can be used to re-execute the query with the same parameter values and have DB2 collect section actuals for it when it executes. This information can be used to have DB2 generate a query execution plan that contains actual row counts and intermediate cardinalities (in addition to the estimates normally provided).

[DB2 ONLY] On the page resulting from RE-EXECUTE AND COLLECT ACTUALS


This page displays the results from re-executing the query with DB2 collecting section actuals for it. The hash, executeTime, fetchTime, totalTime, and rowCount have the same meanings as elsewhere. The DB2 activity event monitor summary row shows a summary of the information collected by the event monitor for the query execution. The DB2 commands to get explain information row shows the two DB2 commands that can be executed (at a command prompt that is connected to the database) to generate a file containing the formatted query execution plan used for this execution of the statement, one that includes actual row counts and cardinalities (in addition to the estimates normally provided). The intent is that the two commands can easily be copy & pasted to the command prompt to execute them.

Usage example

Let’s assume that you have already identified a query of interest (typically, because it is unexpectedly slow) using queryStats or some other tool. Go to the queryStats page, click START to start collecting query statistics.
The query must be executed so that it shows up on this page. Perform (or have the customer perform) whatever action causes the query of interest to be executed. Then, refresh the queryStats page and find the query. Let’s say we’re interested in the query whose hash is 4003102C.
Enable the collection of stat records for the query as follows. (This is similar to enabling collecting stacks for a query.) Click on the hash value to get to the query stats details for the query. Initially, statRecordsEnabled will be false and there will be no statRecords.


Click ENABLESTATRECORDS. Note that the value of statRecordsEnabled will change to true. But there will still be no statRecords because the query has not been executed since stat records were enabled just now.
Now, whenever this query executes, statRecords will be collected for it and be displayed on this page (when you refresh it). Cause the query to be executed again and refresh the page. The statRecords row will show (up to 10 of) the most recent executions of the query.

Click on one of the executeComplete timestamps to see that execution’s stat record.
[DB2 ONLY] Click on RE-EXECUTE AND COLLECT ACTUALS. This will re-execute the query in such a way that a DB2 activity event monitor will have captured section actuals for the query, as well as the DB2 commands that you can execute to get a query explain plan that includes the actuals.
In a command prompt from which you can connect to the DB2 database (named JTS in this example), connect to the database and then execute each of the two commands shown in the DB2 commands to get explain information row.
The resulting output file (db2exfmt_5378_1.out in this example) will contain the formatted query plan containing actual cardinalities and row counts (in addition to the estimates). For each node in the plan tree, the second row will show the actual row count or cardinality (which the following picture points out for just the first few levels).

Known Issues

HTTP Error 500: Internal Server Error while clicking on "RE-EXECUTE AND COLLECT ACTUALS"

Solution: Ensure the actuals_mon event monitor commands are executed with the same user that connects to application DB.
Use the Db2 technote to remove the explain tables, where the command:
$ db2 connect to RM702
$ db2 "call SYSPROC.SYSINSTALLOBJECTS( 'EXPLAIN', 'D', ' ', ' ' )"

The error states that an activity monitor must exist as a specific userID. In the example above, DB2ADMIN does not own the ACTIVITY_ACTUALS_MON event monitor.

In order to create the Event Monitor as the correct user, you can use these steps:

db2 connect to RM702 user db2admin using PassWord
db2 create tablespace actuals_mon

As db2admin, list the tables and ensure the ACTIVITY tables exist, then turn on the event monitor:
 db2 list tables  |grep ACTIVITY
ACTIVITYSTMT_ACTUALS_MON            DB2ADMIN        T     2021-10-21-
ACTIVITYVALS_ACTUALS_MON            DB2ADMIN        T     2021-10-21-
ACTIVITY_ACTUALS_MON                    DB2ADMIN        T     2021-10-21-
Follow the steps to create the actuals tables
db2 update database configuration using section_actuals base
db2 create event monitor actuals_mon for activities write to table
db2 set event monitor actuals_mon state 1
db2 "create workload actuals current client_acctng('actuals') collect activity data with details,section"
db2 "grant usage on workload actuals to public"
Finally, check to see that the event monitor has is active

EVMONNAME            2
-------------------- --------
ACTUALS_MON              Active     <====

As activity monitors consume resources, you should set any unwanted monitors to inactive, for example:

db2 connect user ANOther using Pa55Word
db2 set event monitor actuals_mon state 0
Also see Disabling the event monitor after use.

Related topics: Deployment web home, Db2 MustGather, DOORS Next MustGather

External links:

Additional contributors: PaulEllis, BhagathPB

Topic attachments
I Attachment Action Size Date Who Comment
Pngpng Picture_1.png manage 190.6 K 2022-09-13 - 20:29 PaulEllis  
Pngpng Picture_10.png manage 258.4 K 2022-09-13 - 21:15 PaulEllis  
Pngpng Picture_11.png manage 422.4 K 2022-09-13 - 21:16 PaulEllis  
Pngpng Picture_12.png manage 344.1 K 2022-09-13 - 21:18 PaulEllis  
Pngpng Picture_2.png manage 224.8 K 2022-09-13 - 20:42 PaulEllis  
Pngpng Picture_3.png manage 103.9 K 2022-09-13 - 21:01 PaulEllis  
Pngpng Picture_4.png manage 315.2 K 2022-09-13 - 21:03 PaulEllis  
Pngpng Picture_5.png manage 235.3 K 2022-09-13 - 21:05 PaulEllis  
Pngpng Picture_6.png manage 172.2 K 2022-09-13 - 21:08 PaulEllis  
Pngpng Picture_7.png manage 179.1 K 2022-09-13 - 21:10 PaulEllis  
Pngpng Picture_8.png manage 192.9 K 2022-09-13 - 21:11 PaulEllis  
Pngpng Picture_9.png manage 512.9 K 2022-09-13 - 20:24 PaulEllis  
Edit | Attach | Printable | Raw View | Backlinks: Web, All Webs | History: r7 < r6 < r5 < r4 < r3 | More topic actions
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Contributions are governed by our Terms of Use. Please read the following disclaimer.