2. COST ACCOUNTING CONCEPTS FOR IS ORGANIZATIONS › 2.10 DB2 Accounting
2.10 DB2 Accounting
DB2 is IBM's relational database management system for the
z/OS operating system. It was designed by IBM to enable
users to develop and deploy applications in a consistent
manner across a variety of platforms. With thousands of
licensed users, it is one of the most popular relational
database products in use today.
The DB2 application environment is technically very complex,
but, with guidance, it is not difficult to implement cost
accounting for DB2 applications. The greatest challenges to
DB2 chargeback are to figure out the following:
o What resources to charge for, because users want
repeatability and predictability in their bills.
o What data source to select, because resource utilization
is reported by both DB2 and the application region.
o How to charge for DB2 overhead because much of the DB2
service usage cannot be attributed back to the user.
This section describes the DB2 application environment,
discusses the technical issues involved in resource
measurement, and defines a strategy for implementing
accounting and chargeback. Using this information, you can
easily implement the accounting strategy.
Background
----------
DB2 manages the relationships between the way an application
program or user wants to access data and how the data is
physically stored. It provides (almost) transparent access
to data in distributed databases, guarantees that units of
work completed by an application will be applied to the
database, enforces security and data-integrity rules and
makes intelligent decisions about how to access data
efficiently for each request that it receives. In short, DB2
handles much of the work needed to create database
applications and lets the programmer concentrate on how to
present or interpret the data and interact with the user to
solve business problems.
DB2 users are either end-users, submitting queries from a
terminal or workstation, or application programs running on
mainframes or workstations. DB2 applications present a
special set of problems for accounting and chargeback
systems. This section describes the components of a DB2
application, provides the guidance needed to capture
meaningful usage data, and recommends a chargeback strategy.
The DB2 System
--------------
DB2 has four major components, each of which executes as a
separate region in the z/OS operating system. The first
three are required while the fourth is needed only when
remote database access is required.
o System Services--handles system-wide tasks such as system
startup and shutdown, logging of data for restart and
recovery, controlling connections to other z/OS subsystems
such as CICS and TSO and collecting statistics for
performance analysis and accounting.
o Locking Services--controls concurrent access to data,
either permitting or preventing users from accessing the
same set of data depending on the operations requested.
o Database Services--supports the definition, update and
retrieval of data. It carries out the data requests from
users or programs and manages the DB2 system tables.
o Communication Services--also called the Distributed Data
Facility (DDF), is an optional component that implements
distributed access to data. It communicates with DB2
databases running under z/OS, OS/2, or AIX and other
non-DB2 database systems that use the IBM Distributed
Relational Data Access (DRDA) protocol.
A DB2 system, composed of these regions, can "own" or control
multiple databases and there can be multiple DB2 systems
active in a single z/OS environment.
The Application Environment
---------------------------
Users and programs communicate with DB2 using the SQL
language. SQL statements are translated into program calls
to DB2 that cause the various data functions to be performed.
The SQL statements may be imbedded in programs that execute
as CICS or IMS/DC transactions under TSO or in batch jobs.
SQL statements can also be entered directly from TSO sessions
and from PC workstations using one of a variety of front-end
software programs.
If the application is running in the same z/OS system as the
DB2 system that owns the database, then the call to DB2 is
performed directly, using cross-memory services. If the data
resides in a remote database, then the request is handled by
DDF.
Measurement Data
----------------
A great deal of performance and statistical data is recorded
for the application region and for the DB2 regions. The most
useful statistics for charging for DB2 resource usage by an
application or user are:
o Number of SQL statements executed by statement type
o CPU time used to execute the calls to DB2
o Number of getpage buffer requests used to process and
retrieve the requested data
The number of SQL statements executed by statement type is
available and may be used to apply a surcharge for DB2 usage.
However, SQL statements vary greatly in the amount of DB2
resources that they use, so they are not a good choice for
resource usage charges.
DB2 requests use TCB CPU time and the CPU time used to
execute the user's tasks is recorded as TCB time. The TCB
time used can be traced back to the user. Most I/O related
activity and general DB2 services, such as locking and queue
management, are in this category. This means that a
significant part of the resources used in a DB2 application
are recorded as overhead. They can be apportioned back to
users but cannot be measured at an individual level.
The number of getpage buffer requests is a logical I/O
measurement and a good indicator of the complexity of a DB2
request. It is the best element to use for recovering DB2
overhead costs.
TCB Time and Exceptions
-----------------------
TCB, or user-task, time for DB2 functions is recorded by the
calling region. This is usually the application region:
batch, TSO, IMS/DC, or CICS. It is therefore, normally
accounted for by standard usage charges. However, there are
two exceptions to this rule: CICS transactions and remote
database calls.
o CICS Considerations
After DB2 Version 6, and CICS CTS Version 2.2 and later,
CICS transactions use the Open Transaction Environment
(OTE) to connect with DB2. This means that the DB2 TCB
time is reported in the CICS transaction records and
billing from the CICS application region will account for
DB2 TCB time.
Prior to DB2 Version 6 and CICS CTS Version 2.2, CICS DB2
calls were executed under a separate z/OS subtask and a
separate TCB from the rest of the transaction. CICS
statistical routines did not track this time for a
transaction, resulting in the TCB time used by a CICS
transaction to perform DB2 functions not being recorded in
the CICS transaction data.
o Remote Database Calls
Data requests that are passed to a remote database by DDF
are executed in a DB2 region on the system where the
database resides. This TCB time is available only in the
resource statistics produced by the DB2 system that
executed the request.
The TCB time recorded in the DB2 statistical file for local
database requests by batch jobs, TSO sessions, IMS/DC, and
CICS (after DB2 Version 6 and CICS CTS 2.2) transactions
duplicates the time recorded by the calling region.
To accurately charge for TCB time used by a DB2 application,
you must apply charges to the following elements:
o TCB time recorded in the batch job, IMS/DC, TSO, and CICS
statistical files.
o TCB time recorded in the DB2 user activity file if the
caller was a CICS transaction (but only if you are running
DB2 Version 5 or earlier, or CICS CTS 2.1 or earlier).
o TCB time recorded in the DB2 user activity file for remote
database access. For CA MICS Accounting and Chargeback
Option, these records have a value of "1" in the DB2THDTY
field.
The DB2 Accounting Trace
------------------------
DB2 statistics are collected by the DB2 Instrumentation
Facility, a part of DB2 System Services, and may be written
to the SMF log or to a Generalized Trace Facility (GTF) data
set. The facility allows users to select several types of
trace records and the level of detail. The traces are
Statistics, Accounting, Performance and Global. The level of
detail is controlled by the Class of data requested. For
more information, see the "DB2 Performance Monitor Usage
Guide".
For accounting purposes, we are interested in the Accounting
trace, recorded in SMF type 101 records. These provide the
detailed information needed for accounting and chargeback.
They contain the TCB time used, counts of DB2 calls by type
and logical I/O counts. When the CA MICS Analyzer Option for
DB2 and CA MICS Accounting and Chargeback Option are used,
these statistics are available to the CA MICS Accounting and
Chargeback Option system as charging elements derived from
the DB2 User Activity (DB2DSU) file.
The DB2 Accounting Trace may be activated for various
classes. The DB2 installation default is Accounting Class 1.
However, Class 2 should be used instead to provide accurate
TCB times for DB2 calls.
NOTE: There has been much debate about using Class 1 or
Class 2 trace data for measuring CPU time. Most
recommendations from IBM have stated that Class 2 time
is a more accurate measurement of the "in DB2" time
used for a DB2 call. The additional overhead required
for the Class 2 trace has been significantly reduced
since DB2 Version 3. For a detailed discussion of
Accounting Class 1 and Class 2, see IBM's "CICS DB2
Guide."
Identifying DB2 Overhead
------------------------
At the system level, SMF type 30 records for the DB2 regions
identify the resources utilized by DB2 that cannot be traced
to an individual user. This includes CPU time, memory used,
and other measures associated with standard job accounting.
These statistics are available to CA MICS Accounting and
Chargeback Option as charging elements derived from the
BATJOB file. The charges associated with these elements for
the DB2 regions are usually summarized to a no-bill cost
center as a record of overhead costs.
Most organizations recover the overhead costs in charges to
their users. The overhead may be recovered either by
spreading the costs across all billed elements, thus sharing
the burden among all system users, or by applying a charge to
DB2 users only.
If the overhead is spread across all of the billed elements,
then the overhead costs are included in the rates charged for
CPU time, EXCPs, and so forth and no additional charges are
added for DB2 usage. In this case, DB2 users and
applications will be charged for the TCB time used for DB2
requests (at the standard rate) and other charges that apply
to the charging elements recorded for their local region. No
surcharge or add-on rate is applied for DB2 services.
If the overhead is charged only to users of DB2 services,
then an additional charge is added for DB2 usage. In this
case, the user is charged the standard rates for resources
used in their region, including the TCB time used for DB2
calls, plus a DB2 usage rate that is applied to elements
collected in the DB2 statistical file. Getpage buffer
requests is the preferred element for the add-on charge.
Strategies for DB2 Accounting
-----------------------------
We recommend that you charge for DB2 resource usage based on
Class 2 TCB time from the DB2 statistical records. If you
are running CICS CTS Version 2.2 (or higher) and DB2 Version
6 (or higher) then you should NOT charge for CICS CPU time,
and instead ONLY charge for remote data request CPU time.
There are other alternatives that may be more appropriate in
your environment. For example, if a DB2 system is dedicated
to a single application or business area, then the simplest
charging method is to ignore the DB2 usage statistics and
charge for DB2 resource utilization as a whole from the batch
job statistical file.
Another alternative is to charge each user a fixed price per
month for using an application.
Or you may be able to do transaction rather than resource-
based charging. Using transaction charging, you charge users
based on the number of business transactions processed. An
example is, charging based on the number of customer bills
produced by an accounts receivable system. This type of
charging benefits the end users because it enables them to
relate their information-technology costs to their actual
lines of business and thus better plan expenses. For more
information about implementing transaction charging (also
called transaction accounting), see section 2.4.
The rates for transaction and application charging are nearly
always based on the measured and rated cost of system
resources used by the transaction or application. Once you
establish a cost factor or rate for the resources used, it
may be relatively easy to translate the charges into values
that are more meaningful for the end users of the services.
Thus developing rates for resource usage is beneficial for
most charging strategies.
Tying DB2 Statistics Back to the User
-------------------------------------
In order to charge for DB2 usage from DB2 records, you must
identify the user to be charged. There are several ways to
approach this.
The statistical records written by the DB2 Accounting trace
seldom correspond one-to-one with the statistical records
from batch jobs, TSO sessions, CICS or IMS/DC transactions.
For batch jobs and TSO sessions, there will be one or more
DB2 records. For CICS and IMS/DC transactions, the DB2
statistics may represent part of a transaction or a
combination of several transactions. Therefore, we do not
recommend trying to match the DB2 records back to the records
generated by the caller in order to identify the DB2 user.
Instead, we recommend using authorization ID (AUTHID) and the
DB2 plan name to identify the DB2 user and assign the cost
center to be charged. For TSO sessions, these fields
normally identify a user and application area; for batch
jobs, CICS and IMS/DC usage, they should at least identify
the application used.
NOTE: This depends on an organization's use of the AUTHID
and DB2 plan structure. It is assumed that the AUTHID
used for TSO sessions is unique to the user or work
group and that DB2 plans do not include data access
modules from multiple application areas.
In addition to identifying the user, you also need to know
the calling environment (batch, TSO, CICS, etc.) and whether
the record represents a remote database access. The DB2
statistics should be summarized to minimize the amount of
data before calculating charges, but the fields needed to
identify the user, calling environment, and thread type must
be used as summarization keys.
Using the CA MICS Analyzer Option for DB2, you can control
the level of summarization based on the population of the
DB2DSU account fields because charging occurs at the DAYS
timespan. The contents of these fields are assigned during
CA MICS Analyzer Option for DB2 installation; the recommended
assignment statements are provided in Section 3.2.1,
Implementing DB2 Accounting, in the CA MICS Accounting and
Chargeback Option User Guide.
Summarizing charges in this way is most efficient for DB2
applications as well as the accounting and chargeback system.
Performance analysis can still be accomplished at a fine
level of granularity using statistical data in the CA MICS
Analyzer Option for DB2's DETAIL timespan files.
Summary
-------
Although the DB2 application environment is complex, it is
not difficult to implement cost accounting for DB2
applications. The TCB time used for DB2 calls is the only
directly measurable element available for standard usage
charges. For remote database (DDF) environments, TCB time
reported in the DB2 Accounting trace must be added to the
time reported for the application region. For CICS
transactions, TCB time reported in the DB2 Accounting trace
is added only for older releases of DB2 and CICS (DB2
pre-Version 6 and CICS CTS pre-version 2.2). DB2 overhead
may be spread across all system users, both DB2 and non-DB2
users or may be applied as an add-on cost to DB2 users only.
The methods that have been described are easy to implement
using the CA MICS Analyzer Option for DB2 and CA MICS
Accounting and Chargeback Option and add little additional
overhead to the chargeback system.
