I’d rather play golf

For a software system, IBM’s IMS (Information Management System) hierarchical database system is getting pretty long in the tooth – it was developed beginning in 1964 as a joint effort between IBM and Rockwell International for the Apollo moon program. That makes IMS 44 years old, an eternity in the life of a software product.

When I was an undergraduate, though, IMS was king. At that time there were no industrial-strength commercial relational database systems – DB2 on MVS didn’t ship until 1984 – just research prototypes like IBM’s SYSTEM R. So business systems using IMS made the world go round. That’s still somewhat true today – every major Canadian bank and insurance company still uses IMS/TM for at least some of its operational business systems.

Performance and tuning of an IMS database installation was an art, though a number of things conspired to greatly simplify the problem. First, IMS online systems were, and most still are, exclusively OLTP systems. Reporting applications ran in a “batch window” overnight, with each having exclusive access to the database file (or for read-only reporting applications, executing over a separate copy of the database). Second, IMS databases are simple hierarchical structures, and access to any particular segment (loosely, a tuple, in relational terms) is always done via an index; sequential processing in the online IMS system would ordinarily lead to the crucifixion of the application programmer. Third, in most installations new IMS applications programs could not be installed – even in test environments – without the direct assistance of the DBA. Hence application development was tightly controlled, the DBA always knew (and could track) the development of additional applications, and the DBA was also able to greatly influence the application’s development with advice and “best practices”. Fourth, for system administrators the configuration of the IMS online system was primarily controlled by relatively few “knobs”, the major ones being:

• The size of each of the VSAM buffer pools, a separate pool for each page size.
• The number of IMS online regions (tasks) – the online system’s multiprogramming level.
• The MVS task priority of each of the online IMS regions. Different task priorities were often used to set service levels for particular transaction classes, as one could configure the online regions to service only specific transaction classes.
• The configuration of the disk subsystem attached to the mainframe, and the specific cylinder placement (!) of the IMS databases (VSAM datasets) on those disks.

Performance tuning in this environment is done manually. Manual tuning is possible because in typical IMS environments the system workload is (1) known and (2) relatively constant. It isn’t true that the workload is flatline constant; there are peaks and valleys of system usage, around 10am and 2pm, in my experience, on a typical work day. The major factor in the constancy of the online workload is the absence of reporting applications, which are relegated to the evening batch window.

Even with a simplified configuration, manual performance tuning is labour-intensive and without adequate tools is done by trial and error. Indeed, we have textbook quotations to follow such as the following:

Separate buffer pool objects should be used, based on the their type of usage by the applications(s), such as buffer pools for objects that are randomly accessed versus those that are sequentially accessed. Each of these buffer pools will have its own unique settings, and the type of processing may even differ between the batch cycle and the online day. These are very generic breakouts just for this example. Actual definitions would be much finer-tuned and less generic.

Readers might believe that I’ve pulled that quote from an old IMS tuning manual from the 1970’s, but they would be wrong. The above is from the IBM DB2 for zOS Version 8 DBA Certification Guide, pp. 594. That section in the IBM DBA Certification manual goes on to describe an example of buffer pool breakouts:

• BP0 – DB2 catalog tables
• BP1 – cursor tables, work files, sorts
• BP2 – heavily accessed code and reference tables
• BP3 – heavily updated but small tables
• BP4 – “Basic” tables
• BP5 – “Basic” indexes

and so on (there are six more).

The point I am trying to make is that it is extraordinarily difficult to perform this sort of detailed tuning for a mixed-workload relational database, even with the help of automated tools. What makes P&T far more difficult with a relational database than something like IMS is: a typically mixed variable workload, a high-level query language (SQL) whose queries are compiled into query execution plans with various physical access paths from which to choose, virtualization of hardware resources (CPUs, disks), ad-hoc queries and therefore an unknown (or at least unanticipated) workload, a relatively uncontrolled application development environment, and (usually) undedicated server machines.

Even my wife Leslie’s firm, where she is employed as a DBA, does not go into this level of detail, even with the existence of tools for configuring DB2 buffer pools. And this is a firm where in addition to the 11 “application” DBAs there are six “system management” DBAs responsible for DB2 server performance.

It is for these reasons that database system self-management holds such promise. This is so DBAs can concentrate on the issues most under their control, and that affect system performance most significantly: logical and physical schema design and application behaviour, including the specification of SQL queries.