Filter
Top Products
© Copyright IBM Corp. 1999 141
Chapter 12. Case Study
The case study applies all previously described monitoring facilities in a common
project from the DB2 and storage perspectives. This approach introduces some
redundancy. The good news is that redundancy allows cross-checking
information among various sources.
The environment is a very large DB2 query on partitioned table spaces over two
RVA storage servers. Activity is exclusively read oriented. Only one DB2 LPAR
accesses the data; there is no data sharing.
Reports generated by DB2 PM, RMF, and IXFP have been pruned to extract the
relevant data and to focus on overall activity only. The reports are shown in the
appendixes.
12.1 DB2 Case Study Analysis
From the DB2 point of view, the first step in analysis is to examine the accounting
reports generated by DB2 PM to establish the elapsed and CPU times of the case
study. The complete DB2 PM reports are shown in Appendix C, “DB2 PM
Accounting Trace Report” on page 201 and Appendix D, “DB2 PM Statistics
Report” on page 205.
12.1.1 General Analysis
12.1.1.1 Elapsed and CPU Time
For any application, the first analysis is the elapsed time and the CPU time. This
information is obtained from the class 1 and class 2 times of the accounting
report. This is shown in Figure 57 on page 142.
Line
A shows the elapsed time of the application is 37 minutes and 41 seconds.
The CPU time is 1 hour, 6 minutes and 21.58 seconds (
B). The CPU time is much
higher than the elapsed time, because multiple CPUs are being used in parallel.
This is shown in the breakup of the CPU time, in TCB time (
C), stored procedure
time (TCB-STPROC) and parallel CPU time (PAR.TASKS,
D).
12.1.1.2 SQL Statements
The CPU and elapsed values indicate a heavy process, either a batch calculation
affecting many rows, or a CPU bound query. Figure 58 on page 142 helps to
establish this. The number of SQL calls is small; it shows one dynamic SQL
statement (
J in Figure 58) which returns 100 rows (K in Figure 58). An extra
FETCH is required to establish that there are no more rows. This example is a
complex query.