Princeton Softech Archive for DB2REVIEWER: Gary Girard, CIS application lead for American Electric Power.

BACKGROUND: With more than 20,000 employees and more than $1 billion in annual revenue, American Electric Power (AEP) is the nation's largest electricity generator, a leading wholesale marketer of energy commodities and a major U.S. electric utility.

PLATFORMS: Princeton Softech's Archive for DB2 is currently running on DB2 version 7 with an OS/390 operating system.

PROBLEM SOLVED: AEP's Marketing, Accounting and Customer Service System (MACSS) and Order Processing System (OPS) comprise a business-critical application suite that supports more than 5 million customers in the AEP network. This suite provides the functionality to initiate and close services, process meter readings, bill customers and post payments. In the late 1990s, AEP's IT staff realized they could not keep adding data to MACCS indefinitely. Database administrators and developers encountered many challenges when navigating through the large amounts of data in the MACSS database. With billions of rows stored in hundreds of DB2 tables, accumulating new data and storing historical data online was taking a toll on performance, while execution times for batch processing were creeping steadily upward.

PRODUCT FUNCTIONALITY: AEP implemented Archive for DB2 to safely archive and remove rarely accessed data from the MACSS database. In the first phase, AEP archived historical data for customers who had finalized service and whose accounts were no longer active. Working with the business users, the application team established rules to identify how much of this historical data needed to be maintained, and how much could be archived and deleted from the production database. Currently, AEP archives inactive customer account data every quarter. Next, AEP implemented the second phase and archived historical data (five or more years old) for active customers. The application team continued to archive the oldest data until only three or four years of historical data remained available for immediate access. The programs no longer had to sift through billions of rows of data for information requests. Execution times for batch processing improved. AEP continues to archive historical data on a regular basis to maintain the MACSS database at a manageable size and keep pace with the database growth. Using the IBM Enterprise Automated Tape Library (3494), AEP writes the archived data to 3490 Virtual tape drives (810MB) where it is then migrated to a 3490 Etape (810MB).

STRENGTHS: Archive for DB2 provided comprehensive capabilities, such as restore, that were necessary for AEP's archiving strategy.

WEAKNESSES: Because of the direct access processing that occurs when browsing a control file, Archive for DB2 requires that a company copy the archive file to disk in order to browse the control file.

SELECTION CRITERIA: The application team evaluated Archive for DB2 based on the following criteria: accessibility of archived data, capabilities to store archived data to a variety of storage media, capabilities to restore archived data for additional processing and/or compliance or auditing purposes and the ability to maintain the original business context of both current and archived data. After the initial demonstration, AEP installed Archive for DB2 in the test environment for a trial period of a few months and successfully tested the archiving capabilities in a product simulation environment.

DELIVERABLES: Archive processing automatically generates summary and detail reports with the archived data. Archived data remains available for easy access and selective restore for additional processing and compliance. The application team plans to reach a plateau where they can safely archive and remove rows from the production database at approximately the same rate that they are being added (approximately 125 million rows per week).

VENDOR SUPPORT: A Princeton Softech representative was on site to assist in the Archive for DB2 setup. The representative was responsive to all of AEP's questions and always available to provide the necessary expertise and ideas for the most efficient use of Archive for DB2.

DOCUMENTATION: The user and reference documentation was helpful for setting up the standard archive process. However, the section on overrides could be improved to be more in-depth for complicated archive processing where SQL is needed.

Archive for DB2
Princeton Softech
111 Campus Drive
Princeton, NJ 08540

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access