Join the Club: DB2 Management Drives Productivity at Automobile Clubs

In today's OS/390 environment, hardware costs have declined substantially, while software costs are increasing dramatically to consume an ever-larger share of the IT budget. But, not all vendors price their products accordingly. A new tool for managing DB2 for OS/390 has entered the market, promising to save DB2 shops about $100,000 a year and allow for a more productive DB2 staff.

In today’s OS/390 environment, hardware costs have declined substantially, while software costs are increasing dramatically to consume an ever-larger share of the IT budget. Unfortunately, not all vendors price their products accordingly.

Part of the cost of using DB2 is managing the environment. In a DB2 environment, resources also have to be allocated for managing the DB2 objects. DB2 management tools can improve productivity and efficiency, but comes at a price. Due to the high cost of DB2 management tools, some DB2 shops have had to manage the environment, relying on the DBA’s knowledge, experience and time. New DB2 releases and new features are less likely to be exploited if the support staff doesn’t have the time.

A new tool for managing DB2 for OS/390 has entered the market, named DB2I2. JRH GoldenState Software Inc., and its founder, Jerry Doong, created DB2I2 to manage the DB2 environment and reduce the workload of the DB2 professional. JRH offers DB2I2 with a low-entry price and a site licensing option. This is a similar approach to IBM’s sysplex pricing, only better. Other DB2 management tools from other vendors typically would cost four times that of DB2I2 for a medium-size data center. This can save DB2 shops about $100,000 a year.

ACSC Discussion

One of the first sites to install DB2I2 is the Automobile Club of Southern California (ACSC). The core business of the ACSC is to provide member services, insurance services and travel services to more than five million members.

ACSC’s data processing environment consists of two S/390s, over 1.5 terabytes of DASD and more than 5,000 online users accessing the corporate DB2 databases through CICS. There are 5,000 Production DB2 tables, and 10,000 Production DB2 packages, not to mention the testing and development environments, all this is managed by only five DBAs. ACSC has recently acquired the Automobile Clubs of Texas, New Mexico and Hawaii. The acquisition of Automobile Clubs outside California has created a need for new applications that are scalable, easier to maintain and can be integrated with the existing applications. ACSC is committed to using DB2 for OS/390 as the base for its core business applications.

Product Acquisition Motivation

ACSC initially evaluated DB2I2 as a tool to assist the Y2K efforts that were ramping up in the second half of 1998. DB2 application environments were created in order to do regression testing and acceptance evaluation of the fixes. For the most part, these phases of DB2 application environments were a mirror of the production DB2 application. The primary variation from DB2 production was essentially the object names and the sizing. This is where DB2I2 was widely used.

The ease of replication of databases from one DB2 subsystem to another has reduced the DBA workload by at least 50 percent.

Product Description

DB2I2 is built on a reliable TSO/ISPF and REXX foundation. The DB2I2 interface is the familiar TSO/ISPF edit screen. User input to the commands and processes can come from the main edit screen or from an external user dataset. DB2I2 and TSO process the request, where the appropriate DB2 catalog tables are queried. The result of the process is returned into the edit screen, where it can be reused for the next command or process. This iterative use of the result set as input into the next process is unique to DB2I2. Other DB2 management tools typically require multiple ISPF panel accesses to accomplish similar functions. DB2I2 saves time and is many times faster than the other tools on the market.

DB2I2 is designed intuitively to function the way a DB2 Administrator would approach a problem and solve the problem in the most efficient manner. Increasing the DB2 staff’s productivity is the goal for using DB2I2. DB2I2 uses an ISPF edit panel and a PFK to access the tools functions.

Minimal training is required to be productive in using DB2I2 functions by using the same TSO/ISPF edit panel that the users are accustomed to. Input and output of the DB2I2 functions can be from within the ISPF edit panel or external dataset(s). Non-DBAs and DBAs alike can also benefit from the tool, by allowing them to run SPUFI in the same edit panel, and receive the output in the same edit screen or to an output dataset of their choosing. The non-DBA can also query the DB2 Catalog for information about their application’s DB2 resources through simple line commands, without having to take time away from the DBA.

Online help is available to help guide the novice and experienced alike. By using an ISPF environment and getting the results online for reuse as input into the next task helps improve productivity. A major time-saver is the ability for the user to integrate their own REXX exec(s) to their queries in order to automate repetitive tasks. The former task of creating DB2 Catalog queries in order to do the day to day work of the DBA has been transformed into simple line commands. DB2 utility batch jobs can be generated based on DB2 online query results and processed singly or in groups, saving time and effort. The need to refer back to the DB2 utilities manual to generate a DB2 batch utility is reduced, by the use of check-offs and fill-in-the-blank utility parameters in an ISPF panel. Increased productivity helps to create good, stable subsystems, increased availability, and reduced staff workload.

Installation is easy, generally taking less than one hour – and that includes reading the install documentation. The product is distributed on a 3.5-inch floppy disk. The diskette contains the program files that will be uploaded to the host, and installation documents. A terminal emulator is required for uploading the files to MVS.

ACSC Disaster Recovery

One of the effective uses of DB2I2 has been the preparation for the Disaster Recovery Testing. For the Disaster Recovery, the DASD full volume backups of the production system is shipped to the remote disaster recovery site. At the disaster recovery site, the production system is restored on the provided DASD farm. The operating system and subsystems are restored and online systems made available. It is up to the DBAs to restore the production DB2 environment and all of the associated tables. ACSC has more than 2,000 tables that need to be recovered to a consistency point. Building the batch job to recover a single table and apply the DB2 Log data is time consuming enough.

With DB2I2, some REXX code and a competent staff, all the goals were met. DB2I2 was used to extract the table information from the DB2 Catalog and generate the batch JCL for the recovery utility. REXX code was used to automate the process for all the required tables. DB2I2 even provided unique DB2 utility-IDs so that we were able to concurrently run as many batch jobs as the system permitted, reducing recovery time and staff workload.

ACSC RVA Failure

During the evaluation period, the DB2I2 tool was put to a real life test. ACSC’s production databases are housed in a RAMAC Virtual Array Turbo 2. A unique set of conditions occurred which caused an error between the host and RVA. OS/390, RVA microcode and staff responses to the conditions, resulted in a virtual DASD volume corruption. The corrupted virtual DASD volume contained some tables for the ACSC Insurance application and Membership application.

More than 60 tables had to be recovered during the morning peak activity. The Storage Management staff was able to restore the DASD volume to the last full volume backup. The DBAs were able to ascertain the tables that needed to be recovered by using DB2I2 and its IDCAMS interface. From the DB2I2 list of DB2 Linear VSAM datasets, the tablespaces and indices were identified. DB2I2 was used to generate the DB2 tablespace, and index recovery utility batch jobs from the last image copies. After the tables were restored, DB2I2 was again used to generate the necessary DB2 Log apply batch jobs to current. A major disaster was averted, resulting in a much smaller outage of about two hours, thanks to DB2I2.

About the Author: Dan Almagro is Senior Database Administrator for the Automobile Club of Southern California (Costa Mesa, Calif.). He can be reached at

The Key Features of DB2I2

• Interprets the DB2 Catalog information

• Displays the DDL for existing DB2 objects

• Migration and DDL Generation management with Partitioning Assist

• Issues DB2 commands against databases and table spaces (without requiring the user to remember DB2

command syntax)

• Executes IDCAMS commands within the same command shell

• Call Level Interface (CLI) allows for insertion of user programs or REXX execs, prior to and/or post

processing of the DB2I2 function(s)

• Displays the static SQL from application plans and packages

• Executes dynamic SQL statements (in many cases, without requiring that you remember the SQL syntax)

• EXPLAIN an SQL statement or DECLARE CURSOR statement from DB2 DBRM or package, dynamically

• Plan/Package and Version Management - BIND, REBIND, FREE and PACKIT

• Security management - Display authorization for objects and Copy authorization from one user to another

• Data move management - DSCOPY command to copy underlying VSAM linear dataset from one DB2 object

to another even though they are located in different subsystems

• Generate DB2 utility JCL to run most of DB2 utilities with work spaces calculated

• Space management - Space calculation and data extents removal

• Backup management - Generate image copy with space allocation and tape staging

• Recovery Management - Allows selection from a RBA list for the POC recovery and multiple recovery jobs

generation for concurrent DB2 object recovery

• Intelligent reorg - Determines reorg requirement and builds reorg jobstream


HUON's DB2 Package

One of the newer applications being developed is a flexible insurance software package from HUON Corporation. The software package is DB2-based and uses a CICS or other front-end user interface. HUON's base configuration requires at least 650 tables and each subsequent promotion stage from baseline to production, requires their own DB2 environment of more than 650 tables. The HUON customer can choose how many stages they wish to support between baseline and production.

To provide integrity, user test environments and future development, ACSC has defined 10 stages, including baseline and production. The vendor has provided the initial table definitions and sizing requirements. The ACSC DBA Staff (one-and-a-half full-time DBAs assigned to the HUON project) was tasked with the creation and support of the subsequent DB2 environments.

In addition, the creation of the utility batch job JCL, is the HUON customer's responsibility. One of the application selection criteria was to acquire an insurance software package that could be installed, customized and placed into production in a short development life cycle or nine months, whichever is sooner.

The project was started in September 1998, and to date, 10 DB2 HUON environments have been created. The image copy JCL, reorg JCL, security authorization, management of more than 6,000 DB2 plan/packages and other utilities for the tablespaces in each DB2 HUON application stage, was accomplished with the same number of DBAs, prior to the project.