DBA > Articles

Monitoring an ERP Application in DB2 for z/OS

By: Lockwood Lyon
To read more DBA articles, visit http://dba.fyicenter.com/article/

Monitoring an ERP application suite involves using several basic measurement techniques. Since it probably co-exists with other applications within a DB2 subsystem or data sharing group, a means must be found to separate ERP package performance measures from those of other applications. Additionally, when measuring subsystem performance you must isolate the effects of the package from those of other applications.

Monitoring an ERP Application in DB2 for z/OS

Creating Measurement Processes
There are three things to remember when creating measurement processes:
1. Implement Automation. One-time measures are certainly useful - performance 'snapshots' are sometimes essential for debugging performance problems; however, an ERP package contains many thousands of objects and hundreds of processes (or more). You need a process that develops meaningful measures that allow you to get overall status information as well as the ability to drill down to the details. To do this you must find the means to automate both the data gathering and measurement processes. Many performance measurement products have features for automating either data gathering or performance reporting.

2. Save Historical Data. It takes time to develop a complete picture of ERP package behavior. It is unrealistic to believe that you will be able to do a good job of monitoring and tuning within a few weeks - or months. You must gather data on a regular basis and store it for later processing. (Relational databases are great for this!) Then, over the course of time, you can do trending analysis and capacity planning. Historical data is essential for regression testing; for example, if you are considering making major performance changes you will want a set of measures both before and after the changes to see if there was any improvement.

3. Develop graphical reports. A spreadsheet filled with numbers will not be enough. You must decide on the best way to present your data in order to interpret it correctly. Bar charts, scatter diagrams, and pie charts can all be used to good effect.

What to Monitor; What to Measure
There are two general categories of measurements: system-related and application-related. These categories overlap somewhat, but generally speaking system-related measurements are at a high, or global level. Applications demand work and resources from the DB2 subsystem (such as data storage and retrieval) and the subsystem then performs work on the applications' behalf.

Some common system-related measurements are:
Virtual pool threshold attainment
Sortpool, EDM Pool, Rid Pool filling or failure
Logging rate and Log Write performance
Global buffer pool usage
Subsystem address space CPU usage

Application-related measurements are more detailed and specific. They deal with suites of SQL statements (either separately or taken in sets). Here, the most common measure deal with I/O:
Buffer Pool hit rate (or page residency times)
Waits for synchronous I/O
Prefetch rates
Hardware Cache usage
Index efficiency
Data access paths

Measurement Classes
There are also two classes of measurements that you will need: measurements of resources (which may be overused, underutilized, or constrained) and measurements of behaviors (which may be symptoms of underlying problems).
Resources

Memory
DBM1 Address Space (Virtual Storage constraints)
EDM Pool, Sort Pool, RID Pool
Virtual Pools (Page Residency)
CPU
I/O Subsystem
Network

Behaviors

Locking
Compression
Work File Extents
Deadlocks, Timeouts

The difficulty in dealing with behaviors is that they are symptoms only; they are not the problems themselves. It is easy to fall into the trap of thinking that you have isolated a problem to a few SQL statements. True, making changes (adding OPTIMIZE FOR n ROWS, re-designing indexes, running multi-column RunStats) may increase statement execution speed; however, the net result may be that you have obscured or totally missed the underlying problem(s).

This is why you need to gather measurements from all classes: resources and behaviors, system and application.

In order to better understand what you are measuring, here's an example from one installation in the early stages of an ERP package tuning effort. Table 1 shows a cross-reference of their measurement categories and classes with specific measurements. Table 2 shows some typical tactics and tuning efforts for each quadrant.

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/