Bell Atlantic On Call: Automating DB2 Operations
Bell Atlantic Mobile, a leading national provider of wireless communications services, relies on DB2 to support its strong focus on billing technologies, collections, customer services, and the development and deployment of computing services. From its New York data center, the company maintains dozens of DB2 databases, some tables with as many as 180 million rows in a table.
My responsibilities include mainframe software configuration management, automating DB2 maintenance, and ensuring that the enterprise’s disaster recovery plan accommodates the requirements of its production DB2 applications. The company operates an IBM 3090 CMOS processor operating under MVS/ESA. It is currently running DB2 4.1 with plans to upgrade to Version 5. In my work, I frequently rely on ISPF dialogues and REXX execs to automate many routine tasks.
Bell Atlantic Mobile is committed to establishing an enterprise contingency planning system. Automating backup and recovery processes is critical to implementing a reliable disaster recovery process because manual interventions are costly and inefficient.
Bell Atlantic Mobile has exploited a utility that makes it quick and easy to develop ISPF dialogues that access DB2 data in support of mission-critical contingency planning applications. This article describes three of those applications.
Based on the recommendation of a consultant knowledgeable in these matters, the company conducted a technical evaluation of software utilities designed to provide DB2 developers at Bell Atlantic Mobile with instant feedback and the expressive power of REXX is scripting enterprise contingency planning applications. Based on this technical evaluation, the company eventually installed RLX for DB2, a productivity tool from Relational Architects International, Hoboken, N.J. RLX for DB2 makes it almost painless to develop ISPF dialogues that access DB2 data. RLX provides the ability to combine the use of REXX and SQL within ISPF dialogues, allowing us to automate many of the routine data base administration tasks.
The suite of REXX Language eXtensions for DB2 provides the glue to integrate REXX, DB2, ISPF, SQL, NetView, and a growing number of third-party products that support REXX scripting.
One of my primary responsibilities is to support the mainframe-based, application development staff. The current configuration management tool for mainframe-based application development is SCLM, a facility bundled with ISPF. RLX for DB2 provides superior application development and integration in our environment for automation and accuracy. ISPF dialogues serve as my primary means of automation. RLX with DB2 allows me to write ISPF dialogues to automate DBA and application support tasks quickly. Since I have been writing ISPF dialogues in REXX for many years now, RLX for DB2 fits my needs well as I can now code SQL DML and DDL statements dynamically.
Automating REORG Processing
Managing REORGS is a major headache for DBAs. Automation and accuracy is critical for application performance and availability. Bell Atlantic Mobile uses ISPF dialogues and RLX for DB2 to elegantly solve a business challenge that formerly created weekly headaches, caused multiple abends, and added minutes or hours to a shrinking reorg batch window. Misspelling the name of a DB2 object in an "ALTER" statement is no longer an issue. Objects are now verified against the DB2 catalog prior to being written to a file for later use. The same is true for DB2 "STOGROUPS." If you "ALTER" a DB2 object to a "STOGROUP" that doesn’t exist, the attempt to redefine the underlying VSAM data set fails the next time the object is LOADed or REORGed. We no longer have these problems, as everything is verified against the DB2 catalog interactively. If you enter a DB2 "STOGROUP" name that doesn’t exist, an ISPF panel prompts you to either define a new "STOGROUP," if that is your intention or correct the name if you made a typographical error.
Automatic Extent Failure Avoidance
Bell Atlantic Mobile uses Auto-Ops, a data center automation tool, to read the MVS system log as it is written and perform general operational functions triggered by specific events or messages. For example DB2 issues a DSNP001I message to warn of an impending space shortage when any non-segmented table space has less than 50 percent of its secondary allocation currently available. DB2 can issue the DSNP001I message repeatedly if available space continues to shrink (due to table INSERT or UPDATE activity) while DB2 attempts to secure another VSAM dataset extent.
When DB2 issues the DSNP001I message, Auto-Ops initiates a short program that Beck developed. This application identifies the "STOGROUP" associated with the DB2 object experiencing the storage shortage and dynamically adds an available volume to the "STOGROUP" before the object fills up. This is accomplished by parsing the VSAM name in the DSNP001I message to identify the database name, tablespace or indexspace name, and partition number. Once these values are known, the DB2 catalog can be accessed to determine the associated DB2 "STOGROUP" and dynamically add a volume to it via the SQL "ALTER STOGROUP" statement. This occurs in about two seconds from the time the first DSNP001I message is issued by DB2. RLX for DB2 allowed this particular application to be developed in less than two days saving numerous batch abends and online interruptions.
Software Queue Front End for SCLM
In addition, Bell Atlantic Mobile also developed a front end for SCLM to enable company Team Leaders to control the editing of software. The front end prevents unauthorized editing of software by individuals without prior Team Leader approval. This information is stored in a DB2 table and controlled by Team Leaders. The application allows Team Leaders to coordinate future software availability, and historically monitor software updates and defects. This particular application took about two weeks to completely develop, integrate with SCLM and deploy into production.
ABOUT THE AUTHOR:
Tom Beck is a Senior Member of the Technical Staff at Bell Atlantic Mobile.