Appendix D. IBM OMEGAMON XE for DB2 performance database

Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

IBM OMEGAMON XE for DB2 performance database

OMEGAMON XE for DB2 provides a performance database (PDB), which you can use to store historical information in DB2 tables. Using these tables can be useful for problem determination, application profiling, KPI monitoring, and capacity planning.

In this appendix, we introduce the OMEGAMON PDB, and outline how to create the PDB database, and extract, transform, and load (ETL) DB2 trace information into the PDB tables. We used this functionality to implement the activity that is described at 4.4, “Tivoli OMEGAMON XE for DB2 Performance Expert for z/OS” on page 201.

The appendix covers the following topics:

•Introduction

•Creating the performance database

•Extracting, transforming, and loading accounting and statistics data

•Additional information

D.1 Introduction

The PDB consists of a set of tables that you can populate with information from DB2 statistics, accounting, performance, locking, and audit traces. The population process is also referred to as extract, transform, and load (ETL). We provide an overview of the PDB ETL process in Figure D-1.

Figure D-1 OMEGAMON PDB ETL overview

As indicated in Figure D-1, ETL processes non-aggregated (FILE format) and aggregated (SAVE format) information.

•Aggregated information

Several records are summarized by specific identifiers. In a report, each entry represents aggregated data. You run the SAVE subcommand to generate a VSAM data set that contains the aggregated data. When the data is saved, you use the Save-File utility to generate a DB2 -loadable data set. As you might have noticed in Figure D-1, this format is supported only for statistics and accounting trace information. This option is useful if you must process huge volumes of accounting information.

•Non-aggregated information

For non-aggregated data, each record is listed in the order of occurrence. In a trace, each entry represents non-aggregated data. You run the FILE subcommand to generate a data set that contains non-aggregated data. This format is supported for all DB2 trace information. Analyzing non-aggregated accounting information can be useful if you want to use the report capabilities of SQL to drill down on thread level accounting information. In our scenario, the volume of DB2 trace information is not expected to be large. We therefore decided to load the PDB tables with non-aggregated information.

With PDB ETL, you can process DB2 trace data of the following input formats:

•System Measurement Facility (SMF) record types 100 (statistics), 101 (accounting), and 102 (performance and audit).

•Generalized Trace Facility (GTF).

•OMPE ISPF interface (collect report data).

•Batch program FPEZCRD. For an example of how to run program FPEZCRD in batch, refer the JCL sample that is provided in the RKO2SAMP library, member FPEZCRDJ.

•Near term history sequential data sets.

In our DB2 environment, we processed DB2 traces that we collected through SMF and GTF.

D.1.1 Performance database structure

The PDB database design is provided by OMEGAMON and comes with a set of tables to store DB2 trace data of the following information categories:

•Accounting

•Audit

•Exceptions

•Locking

•Record trace

•Statistics

•System parameters

For this book, we focused on using non-aggregated accounting and statistics information. If you need details about using the PDB for the other information categories, see Chapter 5, “Advanced reporting concepts. The Performance Database and the Performance Warehouse”, in IBM Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS Reporting User's Guide, SH12-6927.

Accounting tables

Figure D-2 shows the accounting table categories that are provided by the performance database. PDB stores each data type in its own DB2 table.

Figure D-2 PDB structure accounting tables

OMPE provides two sets of accounting tables:

•FILE accounting tables, which detailed information so that you can use SQL to query accounting information on a thread level.

•SAVE accounting tables, which store aggregated data so that you can use SQL to query summarized accounting information of the time interval boundary.

FILE accounting tables

Each table type that is shown in Figure D-2 stores the following information:

•General data: General accounting information (one row per thread)

•Group buffer pool: For each thread, one row per group buffer pool that is being used

•Package data: For each thread, one row per package that is being used

•Buffer pool: For each thread, one row per buffer pool that is being used

•Resource limit facility: One row per resource limit type that is encountered

SAVE accounting tables

Each table type that is shown in Figure D-2 on page 529 stores the following
aggregated information:

•General data: General accounting information, one row per aggregation interval

•Group buffer pool: For each aggregation interval, one row per group buffer pool that is being used

•Package data: For each aggregation interval, one row per package that is being used

•Buffer pool: For each aggregation interval, one row per buffer pool that is being used

Accounting table DDL and load statements

OMPE provides sample create table DDL, load utility control statement templates, and table metadata descriptions in the RKO2SAMP library members that are shown Table D-1 and Table D-2. We used these templates to create and load these accounting tables.

Table D-1 FILE accounting table DDL and load statements

Table name	Type	RKO2SAMP create table DDL	RKO2SAMP load utility statements	RKO2SAMP table metadata documentation
DB2PMFACCT_BUFFER	Buffer pool data	DGOACFBU	DOGALFBU	DGOABFBU
DB2PMFACCT_GENERAL	General data	DGOACFGE	DGOALFGE	DGOABFGE
DB2PMFACCT_GBUFFER	Group buffer pool	DGOACFGP	DGOALFGP	DGOABFGP
DB2PMFACCT_PROGRAM	Package data	DGOACFPK	DGOALFPK	DGOABFPK
DB2PMFACCT_DDF	DDF data	DGOACFDF	DGOALFDF	DGOABFDF

Table D-2 SAVE accounting table DDL and load statements

Table name	Type	RKO2SAMP create table DDL	RKO2SAMP load utility statements	RKO2SAMP table metadata documentation
DB2PMSACCT_BUFFER	Buffer pool data	DGOACSBU	DOGALSBU	DOGABSBU
DB2PMSACCT_GENERAL	General data	DGOACSGE	DOGALSGE	DOGABSGE
DB2PMFACCT_GBUFFER	Group buffer pool	DGOACSGP	DOGALSGP	DOGABSGP
DB2PMFACCT_PROGRAM	Package data	DGOACSPK	DOGALSPK	DOGABSPK
DB2PMFACCT_DDF	DDF data	DGOACSDF	DOGALSDF	DOGABSDF

Statistics tables DDL and load statements

Figure D-3 shows the structure of each of the statistics tables in the performance database. PDB stored each data type in its own DB2 table.

Figure D-3 PDB structure statistics tables

In our environment, we generate loadable input records in the FILE data format. In that format, each table type that is shown in Figure D-3 stores the following information:

•General data: One row for each Statistics delta record, containing data from IFCID 1 and 2. A delta record describes the activity between two consecutive statistics record pairs.

•Group buffer pool data: One row per group buffer pool that is active at the start of the corresponding delta record.

•DDF data: One row per remote location that is participating in distributed activity by using the DB2 private protocol and one row for all remote locations that used DRDA.

•Buffer pool data: One row per buffer pool that is active at the start of the corresponding delta record.

•Buffer pool data set data: One row for each open data set that has an I/O event rate at least one event per second during the reporting interval. To obtain that statistics trace information, you must activate statistics trace class 9.

OMEGAMON provides sample create table DDL, load utility control statement templates, and table metadata descriptions in the RKO2SAMP library members that are shown in Table D-3. We used these templates to create and load these statistics tables.

Table D-3 Statistics table DDL and load statements

Table name	Type	RKO2SAMP create Table DDL	RKO2SAMP load utility statements	RKO2SAMP table metadata documentation
DB2PM_STAT_GENERAL	General data	DGOSCGEN	DGOSLGEN	DGOSBGEN
DB2PM_STAT_GBUFFER	Group buffer pool data	DGOSCGBP	DGOSLGBP	DGOSBGBP
DDB2PM_STAT_DDF	DDF data	DGOSCDDF	DGOSLDDF	DGOSBDDF
DB2PM_STAT_BUFFER	Buffer pool data	DGOSCBUF	DGOSLBUF	DGOSBBUF

D.2 Creating the performance database

We used the create table DDL RK02SAMP library members that are described in D.1.1, “Performance database structure” on page 529 to create the PDB accounting and statistics tables. To create the PDB, we performed the following activities:

•Create a DB2 for z/OS database to store the PDB tables.

•Customize PDB create table DDL.

•Create PDB tables.

D.2.1 Creating a DB2 z/OS database

We ran the SQL shown in Example D-1 to create the DB2 for z/OS database that we used to create the PDB tables. In our PDB environment, table spaces use buffer pool BP1, and index spaces use BP2.

Example D-1 PDB create DB2 z/OS database

CREATE DATABASE PMPDB

BUFFERPOOL BP1

INDEXBP BP2

CCSID EBCDIC

STOGROUP SYSDEFLT;

D.2.2 Customizing the PDB create table DDL

The OMEGAMON -provided PDB create table DDL statements require customization, as OMEGAMON does not provide an interface for providing PDB table qualifier, database, and table space names. In addition, the PDB provided database design does not provide create table space DDL and does not provide for indexes that are required to ensure uniqueness of data and to support query performance. To perform this customization, we performed the following tasks:

•Generate a create table DDL data set that contains all DDL statements.

•Modify a create table DDL to reflect the PDB database name and table qualifier.

Generating a create table DDL data set

We ran the JCL that is shown in Example D-2 on page 533 to merge the accounting and statistics create DDL statements that are shown in Table D-1 on page 530, Table D-2 on page 530, and Table D-3 on page 531 in to a data set. For application profiling, we run queries on aggregated accounting information we created in the OMPE accounting tables that are described in “SAVE accounting tables” on page 530.

Example D-2 PDB generate create table DDL data set

//S1GEN EXEC PGM=IEBGENER

//SYSUT1 DD *

SET CURRENT SCHEMA = 'PDB';

// DD DISP=SHR,DSN=<omhlq>.RKO2SAMP(DGOACFBU)