In-Depth

Join the Club: DB2 Management Drives Productivity at Automobile Clubs

• 08/01/2000

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.

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.

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.

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.

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.

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 almagro.dan@aaa-calif.com.