6. DATA SOURCES › 6.4 Query CP Parallelism Considerations › 6.4.2 Parallel Tasks Data Analysis › 6.4.2.2 Data Analysis Guidelines › 6.4.2.2.1 Reporting on User Activity
6.4.2.2.1 Reporting on User Activity
DB2 user activity reporting does not generally require access
to the most detailed level of data. So, by default, the CA
MICS User/Plan Activity files (DB2DSU and DB2DSP) are
inactivated in the DETAIL timespan. However, data
summarization in higher levels of granularity must be
accurate and prevent data duplication, overlapping, or
incomplete measurements.
But, how can you determine if any query has been enabled for
CP parallelism (at any point within the reporting interval),
if you don't have access to DB2 system information or
parameters?
If you have activated the DETAIL timespan for the user
activity files and want to report on this granularity level,
each observation can be identified using the Special
Transaction Type Identifier (DB2PTASK) data element.
- A value of 'NONE' indicates that the observation represents
the activity of a sequential query.
- A value of 'ACUMAC' indicates a record representing the DB2
ACCUMACC summarization of two or more DDF or RRSAF queries.
- A value of 'PARENT' indicates that the observation
represents only the activity of the originating task of a
parallel query.
- A value of 'CHILD' indicates that the observation
represents the activity of a single parallel subtask
(individual child records are generated when the DB2 system
parameter PTASKROL is set to NO).
- A value of 'ROLLUP' indicates that the observation
represents the summarized activity of all parallel child
subtasks (summarized child roll-up records are generated
when the DB2 system parameter PTASKROL is set to YES).
If you report on higher levels than DETAIL, then DB2PTASK is
no longer meaningful. However, the database maintains an
accumulated counter containing the number of parallel tasks
created, DSUPCNT. If all the queries are sequential, DSUPCNT
will be equal to zero. On the other hand, if DSUPCNT is
positive, at least one query in the reporting interval used
CP parallelism.
The following guidelines are intended to assist you by using
correct CA MICS data elements under various circumstances.
1 - Queries That Do Not Use CP Parallelism
2 - Queries Enabled for CP Parallelism
6.4.2.2.1.1 Queries That Do Not Use CP Parallelism
When CP parallelism is not used and the DB2DSU file in the
DAYS timespan has been correctly summarized by DAY, then
producing the following summary daily statistics for a given
user ID is straightforward. However, you will see that it is
more complex with parallelism involved.
The following examples illustrate a few basics of user
reporting, but they can easily be extended to other metrics:
1. Number of threads executed
--------------------------
Element or Computation: DSUNTHRD
Explanation: In DB2, each thread executed generates
its own accounting SMF type 101 record.
When CA MICS processes this data, it sets
the accumulated DSUNTHRD data element to
1 each time it encounters a new record.
Therefore, you only need to print the
value of DSUNTHRD to get the number of
threads executed.
2. Total elapsed time for all threads
----------------------------------
Element or Computation: DSUELPTM or DSUTELPT
Explanation: DSUELPTM and DSUTELPT are only different
when at least one thread (in the period
covered by measurement interval) used CP
parallelism because DSUTELPT would
contain the parent task elapsed time and
all the child subtasks' elapsed times.
This example does not use parallelism and
there are no child subtasks. Therefore,
DSUELPTM contains the same value as
DSUTELPT.
3. Average elapsed time per thread
-------------------------------
Element or Computation: DSUELPTM / DSUNTHRD
or
DSUTELPT / DSUNTHRD
Explanation: Since DSUELPTM and DSUTELPT contain the
accumulated elapsed times for all threads
executed, simply divide one of these
variables by DSUNTHRD to get the average
elapsed time for a single thread.
4. Total elapsed time spent in DB2 for all threads
-----------------------------------------------
Element or Computation: DSUET2TM or DSUTET2T
Explanation: In-DB2 elapsed time is a subset of the
total thread elapsed time. DSUET2TM and
DSUTET2T are only different when at least
one thread (in the period covered by
measurement interval) used CP parallelism
because DSUTET2T would contain the parent
task in-DB2 elapsed time and the child
subtasks in-DB2 elapsed times. This
example does not use parallelism and
there are no child subtasks. Therefore,
DSUET2TM contains the same value as
DSUTET2T.
5. Average in-DB2 elapsed time per thread
--------------------------------------
Element or Computation: DSUET2TM / DSUNTHRD
or
DSUTET2T / DSUNTHRD
Explanation: Since DSUET2TM and DSUTET2T contain the
accumulated in-DB2 elapsed times for all
threads executed, simply divide one of
these variables by DSUNTHRD to get the
average time for a single thread.
6. Total CPU time consumed by all threads
--------------------------------------
Element or Computation: DSUTCBTM or DSUCPUTM
Explanation: DSUCPUTM once contained the sum of TCB
time (DSUTCBTM) and SRB time (DSUSRBTM).
However, since Version 6 Release 1, DB2
does not set SRB times, hence CA MICS no
longer captures SRB related data
elements. Consequently, DSUTCBTM and
DSUCPUTM contain the same value.
7. Average CPU time per thread
---------------------------
Element or Computation: DSUTCBTM / DSUNTHRD
or
DSUCPUTM / DSUNTHRD
Explanation: Since DSUTCBTM and DSUCPUTM contain the
accumulated CPU times for all threads
executed, simply divide one of these
variables by DSUNTHRD to get the average
time for a single thread.
8. Total in-DB2 CPU time consumed by all threads
---------------------------------------------
Element or Computation: DSUTC2TM
Explanation: In-DB2 CPU time is a subset of the total
thread CPU time. It quantifies the TCB
time actually spent on performing DB2
functions. Simply use this accumulated
data element.
9. Average in-DB2 CPU time per thread
----------------------------------
Element or Computation: DSUTC2TM / DSUNTHRD
Explanation: Since DSUTC2TM contains the accumulated
in-DB2 CPU times for all threads
executed, simply divide one of these
variables by DSUNTHRD to get the average
time for a single thread.
10. Total wait time for all threads
-------------------------------
Element or Computation: DSUIOETM + DSUALLTM
Explanation: DSUIOETM represents the time all threads
waited for I/Os in DB2 and DSUALLTM
represents the time they waited for locks
and latches.
11. Average wait time per thread
----------------------------
Element or Computation: DSUIOETM + DSUALLTM
-------------------
DSUNTHRD
Explanation: Just divide the total wait time by the
number of threads executed.
6.4.2.2.1.2 Queries Enabled For CP Parallelism
As soon as CP parallelism is enabled for a DB2 query,
questions about measurements arise. Do the subtasks' elapsed
times overlap with the originating parent task? If so, how
can we accurately translate the user perception of the
response time? On the other hand, if there is no overlap in
elapsed times and the subtasks are processed concurrently, we
may see CPU times greater than elapsed times. How can we
correlate them?
The following section answers a few common questions about
queries enabled for CP parallelism. Note that the elements
used or the computations performed are similar to the
previous section. This illustrates that you can use the same
variables under different circumstances and still be safe.
For more information on specific performance statistics of
parallel processing, see Section 6.4.2.2.2 Monitoring Query
CP Parallelism.
1. Number of threads executed
--------------------------
Element or Computation: DSUNTHRD
Explanation: When a query is enabled for CP
parallelism, instead of creating a
unique SMF type 101 record for the
complete thread, DB2 generates one record
for the originating parent task and
either one additional rollup record
containing summary information for all
the parallel child subtasks (when the DB2
parameter PTASKROL is set to YES), or one
record per individual parallel subtask
executed (when the DB2 parameter PTASKROL
is set to NO). When CA MICS detects that
a record belongs to a parallel query, it
sets DSUNTHRD to one for the parent
record and to zero for the subtasks'
records. By taking these measures,
even if DB2 internally created 12
parallel tasks, the query that the user
executed will only count for one thread.
This is very important when you have to
derive average measurements per thread.
2. Total elapsed time for all threads
----------------------------------
Element or Computation: DSUELPTM
Explanation: DSUELPTM is the only element to use to
get the user view of elapsed time when a
query is enabled for CP parallelism. To
avoid overlap between the originating
task (which lasts until the end of all
parallel tasks) and the child subtasks,
DSUELPTM is only populated for the parent
task and is set to zero for all child
subtasks. This reflects the user
response time.
3. Average elapsed time per thread
-------------------------------
Element or Computation: DSUELPTM / DSUNTHRD
Explanation: This computation provides the actual
average thread response time, as
perceived by the user, which should be
much smaller for CP parallelism enabled
queries than for sequential processing
since parallel subtasks are not accounted
for in any of these two data elements.
4. Total elapsed time spent on DB2 for all threads
-----------------------------------------------
Element or Computation: DSUET2TM
Explanation: In DB2 elapsed time is a subset of the
total thread elapsed time. When a query
is enabled for CP parallelism, DSUET2TM
is the only element that can be used to
get the user view of in-DB2 elapsed time.
In order to avoid overlap between the
originating task (which lasts until the
end of all parallel tasks) and the child
subtasks, DSUET2TM is only populated for
the parent task and is set to zero for
all child subtasks.
5. Average in-DB2 elapsed time per thread
--------------------------------------
Element or Computation: DSUET2TM / DSUNTHRD
Explanation: Since DSUET2TM contains the accumulated
in-DB2 elapsed times for all threads
executed, simply divide one of these
variables by DSUNTHRD to get the average
time for a single thread. Again, only
parent tasks in-DB2 times are accounted
for in this derivation.
6. Total CPU time consumed by all threads
--------------------------------------
Element or Computation: DSUTCBTM or DSUCPUTM
Explanation: In versions prior to Version 6 Release 1,
DSUCPUTM contained the sum of TCB time
(DSUTCBTM) and SRB time (DSUSRBTM).
However, since Version 6 Release 1, DB2
does not set SRB times, hence CA MICS no
longer captures related data elements.
Consequently, DSUTCBTM and DSUCPUTM
contain the same value.
These data elements contain the sum of
the parent task plus all child subtasks'
TCB times; therefore, they can be greater
than DSUELPTM (the elapsed time), which
only accounts for parent tasks. This
should not cause a problem because these
statistics serve two different purposes.
Elapsed time is a service level indicator
and TCB time is a charging element that
must account for the total CPU used by
all tasks and subtasks executed.
If you need to correlate elapsed time to
TCB time, you will use other CA MICS data
elements including both the parent and
child tasks' elapsed times, like
DSUTELPT, Total Elapsed Time.
7. Average CPU time per thread
---------------------------
Element or Computation: DSUTCBTM / DSUNTHRD
or
DSUCPUTM / DSUNTHRD
Explanation: Since DSUTCBTM and DSUCPUTM contain the
accumulated CPU times for all threads
executed, simply divide one of these
variables by DSUNTHRD to get the average
time for a single thread.
8. Total in DB2 CPU time consumed by all threads
---------------------------------------------
Element or Computation: DSUTC2TM
Explanation: In-DB2 CPU time is a subset of the total
thread CPU time. It quantifies the TCB
time actually spent on performing DB2
functions. Simply use this accumulated
data element.
For more information on in-DB2 TCB times,
as it relates to parallelism, see item 6.
9. Average in-DB2 CPU time per thread
----------------------------------
Element or Computation: DSUTC2TM / DSUNTHRD
Explanation: Since DSUTC2TM contains the accumulated
in-DB2 CPU times for all threads
executed, simply divide one of these
variables by DSUNTHRD to get the average
time for a single thread.
10. Total wait time for all threads
-------------------------------
Element or Computation: DSUIOETM + DSUALLTM
Explanation: DSUIOETM represents the time all threads
waited for I/Os in DB2, while DSUALLTM
represents the time they waited for locks
and latches. These data elements contain
the sum of the parent task, plus all
child subtasks' wait times because there
is no overlap between the SMF type 101
records for TCB time and most other
accounting measurements.
11. Average wait time per thread
----------------------------
Element or Computation: DSUIOETM + DSUALLTM
-------------------
DSUNTHRD
Explanation: Just divide the total wait time by the
number of threads executed.
