Skip to content

Personal tools
You are here: Home » DB2 » DB2 Mainframe Articles Archive » DB2 Subsystem Tuning in an ERP Environment - Part 1
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3554

DB2 Subsystem Tuning in an ERP Environment - Part 1

by Lockwood Lyon

Part 1  |  Part 2  |  Part 3

This series of articles provides some answers for DB2 for OS/390 system configuration questions when implementing and maintaining Enterprise Resource Planning (ERP) packages. The emphasis is on OS/390 and DB2 subsystem issues primarily of interest to the systems programmer and DBA.

In Part 1, we review the most important issues facing the system programmer and DBA when planning for ERP package implementation. In Parts 2 and 3, we will address DB2 subsystem configuration, performance monitoring, and tuning.

Clarify Roles and Responsibilities

Where does the systems programmer fit in? The systems programmer can be a valuable coordinator and communicator of alternatives and costs. For example, as part of the ERP package selection process, the SysProg can provide information about the cost of accelerating a CPU upgrade; requirements for tool upgrades due to a planned operating systems upgrade; alternative DASD architectures, including ESS/Shark, RAMAC; and so on.

Even in a mature organization with many well-tuned operational DB2 applications, some shift may occur as the ERP package is installed. For example, who is “on call” for ERP performance and availability issues? Here is my recommended division of responsibilities:

      • The Database Administrator should handle data-oriented tasks: Object creation and maintenance (e.g., Create, data population, Reorg / Recover / Copy, Bufferpool sizing, RCT, RLST).
      • The Systems Programmer should be responsible for subsystem configuration (e.g., ZParm settings, address space memory allocation, WorkFile sizing and placement, automated operations)
      • The Application Administrator should retain responsibility for process-oriented tasks (e.g., job scheduling, online transaction management)

Some areas for possible conflicts are:

      • ZParms — Changes affect the entire DB2 subsystem; however some are data- or process-related (e.g., RELCURHL, CDSSRDEF, CACHEDYN).
      • Workfiles — Used system-wide, changes may require Catalog/Directory lock.
      • Bufferpools — Changes affect how data is read into memory; also affects DBM1 address space virtual storage.
      • Utility Scheduling — Who decides when (and if?) to run a SHRLVL REFERENCE Image Copy? How often should be take backups?

The Four Most Important Issues

Here are the BIG FOUR systems-related configuration decisions:

      • Should the ERP package be implemented in a separate DB2 subsystem?
      • What is the best way to manage virtual memory allocation?
      • How many databases / tablespaces should be defined?
      • How to define recovery considerations?

A Separate DB2 Subsystem

Should you install an ERP package in its own DB2 subsystem? There are advantages and disadvantages. The major reasons are:

      • YES: Provide Risk Avoidance
          • Prevent monopolization of shared resources
          • Facilitate maintenance
          • A possible step to data sharing
          • Best for point-in-time recovery
      • NO: Maximize Overall System Throughput
          • Use of data spaces mitigates some virtual storage problems
          • The need for SQL tuning, stored procedures for pool sharing
          • Use of online reorg, hardware recovery solutions
          • The practical OS/390 address space limits

As an online transaction processing system with a batch component, ERP work mixes well with other applications. The only time it may be necessary to isolate an ERP in its own DB2 subsystem is when you have a requirement for fast point-in-time recovery. In this case, Recover/Rebuild of tablespaces and indexes may not be fast enough and you must fall back on a hardware solution.

There are two ways to give an ERP its own subsystem: either with a standalone DB2 subsystem or as a member of a data sharing group. The issues?

      • Standalone
          • Subsystem-wide point-in-time recovery
          • Perhaps a “clean” environment
      • Data Sharing Member:
          • Easier migration to full data sharing
          • Careful virtual pool assignment and segregation
          • PeopleSoft member can assist other members

Note that a proliferation of DB2 subsystems may affect the way you migrate to sysplex and DB2 data sharing. If the ERP package is in a “shared” DB2, then you should consider separate bufferpools for its objects to better define the eventual Coupling Facility requirements (i.e., How many group bufferpools will there be? Must you increase CF memory installed to compensate?).

Virtual Storage Management

Most of the virtual storage issues are related to control block allocation within the DBM1 address space. Several DB2 system-defined and user-defined areas compete within it. Some of these are:

      • DB2 executable code
      • Local Dynamic Statement Cache
      • Global Dynamic Statement Cache
      • Tablespace compression dictionaries
      • DSCB control blocks for open datasets
      • Control blocks for virtual and hiper pools

Many other pools and control blocks are stored in the DBM1 address space. There’s a limit! Note that there are unique virtual storage constraint considerations in a data sharing environment.

Other things that are stored in the DBM1 address space include:

      • The "Storage Cushion" (an area reserved for must-complete operations)
      • Working storage
      • Stack storage
      • Log Buffers for Fast Log Apply
      • Sorted Log Records for use during restart / recovery

How does a virtual storage problem like this manifest itself? Your DB2 Subsystem abends! What do you do? There are three approaches:

      • Reactive — Set up automation / monitoring of memory use
      • Active — Analyze your needs; understand what will affect usage
      • Pro-Active — Prepare for using Hiperspaces, Data Spaces

Number and Configuration of Databases and Tablespaces

There can be thousands of tables and indexes (sometimes tens of thousands) per instance of an ERP package. With many enterprises installing multiple instances (default, development, quality assurance, production, and so forth), the total number of objects can climb quite high. These objects are collected in tablespaces and databases, and the way they are distributed has performance ramifications. Some of the issues are:

DBD Size

      • More objects means bigger DBDs; DBD size affects EDM Pool performance
      • Object administration
      • Start and Stop commands are at the Database, Tablespace, and Indexspace level
      • Backup and Restore is at the Tablespace and Index level
      • The Reorg utility functions at the Tablespace and Index levels
      • Tablespace-Specific Parameters
      • Freespace
      • DASD allocation quantity
      • Locking parameters such as row-level locking
      • Partitioning
      • Partitioning is at the Tablespace Level
      • This affects potential query execution parallelism as well as query performance

Opinions vary on how many tables per tablespace you “should” have.

Recommendation: Assuming that you can avoid lock escalation, place 20 — 100 tables per table space for administrative convenience. Assign each tablespace to its own database.

Place certain tables in their own tablespace(s) for recovery, locking, or performance reasons. Remember that you specify LOCKMAX on a tablespace basis.

Row-Level Locking Recommendation: Use row-level locking for smaller tables that are frequently updated. For example, common PeopleSoft tables that may benefit from this feature are:


Before diving into row-level locking consider setting a value for MAXROWS (via Alter Tablespace) and Reorg-ing the tablespace in order to 'spread out' the distribution of rows.

There are some considerations for keeping the total number of Database and Tablespaces to a manageable level. Some of these are:

      • Use of COMPRESS YES for tablespaces
      • This means more compression dictionaries stored in the DBM1 address space — a potential virtual storage issue
      • The maximum number of open datasets (ZParm DSMAX)
      • More datasets (tablespaces, indexspaces) open while DB2 is up means longer shutdown times, as DB2 has to ensure that all dataset are physically closed before terminating
      • Data volatility and Reorg frequency
      • Usually you plan to Reorg important tablespaces and indexes
      • More tablespaces and indexes translates to more jobs to run (or at least more objects to specify)
      • The proliferation of DASD VTOC entries
      • More datasets translates to more VTOC entries — ensure that VTOCs are sized appropriately

Recovery Issues

Recovery of the data associated with an ERP package revolves around the following:

      • The choice of appropriate Quiesce Points
      • Whether or not you plan on doing Point-in-Time Recovery
      • Planned Outages

What are the recovery requirements? Must you be responsible for point-in-time recovery? Are there times during the online day when Quiesce points are possible? Are planned outages available? Although Copy and Reorg can be done with SHRLVL CHANGE, you still may want regular outages for applying maintenance or as preparation for faster recovery.

Some recovery tactics include the following.

Recovery to a System-Wide Quiesce Point
Execute DASD-to-Tape or use a proprietary hardware solution

Recovery to an Application-Wide Quiesce Point
Run the Quiesce utility
If Quiesce fails, this may still be acceptable
Execute application-wide Image Copies

Recovery to a Set-of-Tablespaces Quiesce Point
Run the Quiesce utility
Execute ERP-specific Image Copies

Planned outages should be pre-planned as part of an ERP package implementation. These will be used for one or more of the following:

      • Data copy or refresh
          • Semi-regular copy of production data to test environment
          • Refresh of data in an integrated testing environment
          • Potential copy of production data to a disaster recovery (or "backup") environment
      • Product maintenance or upgrade
          • Upgrade of the ERP package will most likely involve a phased approach, rolling in changes and maintenance across multiple instances
          • Changes and fixes to objects will happen on a regular basis; DDL changes will require "quiet" times of low activity
      • Object administration
          • Automated regular Reorgs of appropriate objects
          • Statistics gathering
          • Non-DB2 backup and recovery solutions

Other Issues

Apart from The Big Four issues, other considerations include the following:

      • Applying DB2 maintenance
      • Disaster Recovery considerations
      • OS/390 Systems Issues

Maintenance (‘PTFs’, ‘EBFs’, ‘FixPaks’, etc.) will happen, and you must plan for it. In particular, be prepared to run Before/After benchmarks to establish the benefits gained (if any).

Disaster recovery usually centers around media (or data) recovery. However, after your data has been restored (perhaps at a ‘hot site’), performance may become an issue. This means that you should test performance of the disaster recovery configuration. Performance issues that arise during measurements of your normal configuration must be considered at the disaster site as well.

Note that recovery may not be within the purview of the systems programmer; however, it may be your responsibility to provide alternatives and costs.

You may wish to visit the JOBNUM parameter on the JOBDEF for your spool subsystem (or multi-access spool (MAS) if you have it).


In Part 1, we reviewed the most important issues facing the system programmer and DBA when planning for ERP package implementation:

      • Should the ERP Package be Implemented in a Separate DB2 Subsystem?
          • YES - Risk Avoidance
          • No - Overall System Throughput
      • The Best Way to Manage Virtual Memory Allocation
          • Analyze your needs
          • Be Pro-active; consider using dataspaces and hiperspaces
      • How Many Databases / Tablespaces Should Be Defined?
          • Remember that utilities function at the pageset level (tablespace, indexspace)
          • Enabling data compression or proliferating objects increases virtual storage use
          • Implement row-level locking sparingly
          • Group objects by activity / volatility
      • Defining Recovery Considerations
          • Define requirements first, backup strategy second
          • Be prepared to consider hardware solutions

In Part 2 we will address DB2 subsystem configuration issues. These include sizing the catalog and directory, virtual storage management, log management, and recommended ZParm settings. Part 3 will review monitoring and tuning options.


Lockwood Lyon is a Systems and Database Performance specialist in the Midwest. He has over twenty years of experience in Information Technology as an IMS and DB2 database analyst, systems analyst, manager, and consultant. Most recently he's spent quite a lot of time on DB2 subsystem installation and performance tuning. Lockwood is the author of, MIS Manager's Appraisal Guide (McGraw-Hill 1993), Migrating to DB2 (John Wiley & Sons 1991), and The IMS/VS Expert's Guide (Van Nostrand 1990).

Contributors : Lockwood Lyon
Last modified 2006-01-04 03:20 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone