2. USAGE GUIDELINES › 2.1 Data Analysis
2.1 Data Analysis
The CA MICS Analyzer Option for DB2 helps you do the
following:
o Establish service level goals and agreements.
o Account and charge for DB2 resources.
o Analyze trends, application system and program
performance, and DB2 database contention.
o Tune the DB2 subsystem.
o Create and modify DB2 application programs and DB2 plans.
These functions are described on the following pages.
Service Level Goals and Agreements
In establishing service level goals and agreements, use the
following DB2 Analyzer data:
+-------------------------------------------------------+
| For these agreements ... | use data from these files: |
+--------------------------+----------------------------+
| performance | System Activity (DB2DSY) |
| | |
| throughput | Database Activity (DB2DSD) |
| | (especially DSDDSOPN) |
| | |
| availability | System Activity (DB2DSY) |
| | (especially DSYUNAVL) |
+-------------------------------------------------------+
Resource Accounting and Chargeback
The DB2 Analyzer helps you recover the cost of DB2 resources
by providing input data to CA MICS Accounting and Chargeback,
discussed in Section 2.2.1, or to user-developed accounting
systems.
DB2 Analyzer accounting data is available from the following
sources:
o SMF type 100 statistics records
- Class 1 data includes system and database statistics, and
is useful for regularly monitoring system-wide DB2
activity.
o SMF type 101 accounting records
- Class 1 data includes TCB attach and detach time, and is
useful for tracking events from create thread through
terminate.
- Class 2 data is "in DB2" time, a subset of class 1 data,
and is useful for tracking work being done within DB2
address spaces.
- Class 3 data contains information about suspensions
within DB2, such as I/O or lock/latch suspensions, and is
not useful for chargeback.
Use DB2CONN (Connection Identifier) to identify the
environment from which the data originates. Then use the
chart below (Figure 2-1. Accounting for DB2 Activity) to
decide what data to use to recover your DB2 processing costs.
+-----------------------------------------------------------+
|To charge for DB2 | | |
|resources used in | You can charge | |
|this environment: | for: | Using data from: |
+------------------+------------------+---------------------+
| APPC | TCB CPU time | SMF type 101, |
| | | class 2 records |
| | | |
| | I/O | DB2 GETPAGE requests|
| | | (DSUSGPGR) |
| | | |
+------------------+------------------+---------------------+
| Batch | TCB CPU time | use the CA MICS |
| | | Batch and Operations|
| | | Analyzer |
| | | |
| | I/O | DB2 GETPAGE requests|
| | | (DSUSGPGR) |
| | | |
+------------------+------------------+---------------------+
| CICS | TCB CPU time | SMF type 101, |
| | | class 2 records |
| | | |
| | I/O | DB2 GETPAGE requests|
| | | (DSUSGPGR) |
| | | |
+------------------+------------------+---------------------+
| DB2 in a | TCB CPU time | SMF type 101, |
| distributed data-| | class 2 records |
| base environment | | |
| | I/O | DB2 GETPAGE requests|
| | | (DSUSGPGR) |
| | | |
+------------------+------------------+---------------------+
| IMS | TCB CPU time | use CA MICS Analyzer|
| | | Option for IMS |
| | | |
| | I/O | DB2 GETPAGE requests|
| | | (DSUSGPGR) |
| | | |
+------------------+------------------+---------------------+
| TSO | TCB CPU time |use CA MICS Batch and|
| | | Operations Analyzer |
| | | |
| | I/O | DB2 GETPAGE requests|
| | | (DSUSGPGR) |
+-----------------------------------------------------------+
Figure 2-1. Accounting for DB2 Activity
Keep the following in mind when using resource accounting and
chargeback:
o Attempt to recover only TCB (task control block) CPU time
because SRB (service request block) time includes time for
SRBs run in the user's address space. User processing,
unrelated to DB2 processing, is included in SRB time.
(This only applies to data centers running DB2 versions
older than V6R1M0, because, starting with DB2 Version 6,
Release 1, IBM no longer records SRB time related fields in
the SMF Type 101 records.)
o DB2 does not provide a true measurement for disk I/O; it
provides counts of I/O from buffer pools. If your site
makes user modifications to support buffer pools 4 through
59, you should add those elements' values to your recovery
algorithm. Use the DB2 Analyzer's DSUSGPGR (summarized
buffer pool) element.
o In a distributed database environment, you must always add
the CPU information from the remote (serving) DB2 subsystem
to the calling environment's accounting information.
For example, if an IMS transaction accesses DB2-A, which in
turn accesses DB2-B, the CPU times for DB2-B are not
included in the IMS transaction's CPU times. For a complete
accounting picture, whenever DB2THDTY has a value of 1, you
must add the accounting information at the remote DB2
subsystem to that of the calling environment. In other
words, the SMF data from all DB2 subsystems needs to be
input to the same DB2 Analyzer database unit. Although this
does not represent a change for the CICS accounting
strategy, it does imply a significant change for IMS, TSO,
and BATCH applications.
Trend Analysis and Standards Enforcement
To identify trends and enforce standards, compare
measurements taken in the current month with similar
measurements for the previous month.
+----------------------------------------------------+
| If your area | then use information from |
| of concern is ... | these files ... |
+-------------------+--------------------------------+
| | |
| CPU time | DB2 System Activity (DB2DSY) |
| | |
| I/O counts | Database Activity (DB2DSD) |
| | |
| deadlock | Database Activity (DB2DSD) |
| contention | |
| | |
| application | Database Activity (DB2DSD) |
| timeouts | |
| | |
+----------------------------------------------------+
Application Program and System Analyses
To analyze the performance of frequently used application
programs and their impact on the total workload, follow these
steps:
1. Identify the most frequently used application programs
using the Plan Performance and Plan Performance Summary
reports described in chapter 3 of this guide.
2. Analyze overall data center workload, looking for
programs that use more than their share of resources.
3. Review the components of each program's run time using
the Plan Performance reports and data from the CA MICS
Batch and Operations and the CA MICS Analyzer Option for
CICS, and look for ways to streamline the most
resource-intensive tasks.
DB2 Database Contention Analysis
To identify programs that experience resource contention
events such as deadlock, timeout, and DB2 resource manager
locking, use the Database Services and Plan Performance
reports described in chapter 3 of this guide.
You can use this information to help define new or existing
DB2 structures, select DB2 resource allocation strategies,
and identify problem SQL statements.
DB2 Subsystem Tuning
To optimize system storage and network workloads, monitor
vital subsystem parameters including thread queuing activity,
DB2 buffer pool activity, EDM pool usage, database activity,
and network activity attributable to distributed
conversations.
The DB2 System DDF Activity (DB2DDY), DB2 User DDF Activity
(DB2DDU), and DB2 Plan DDF Activity (DB2DDP) files provide
information about distributed database activity.
Application Program and DB2 Plan Development
When creating and modifying application programs and DB2
plans, use the DB2 Analyzer's files to quantify the number of
plans executed for a development project, their buffer pool
usage, and database access activity using information from
the following files:
o DB2 Plan Activity (DB2DSP)
o DB2 Plan DDF Activity (DB2DDP)
