Safeco improves PeopleSoft application performance and supports business continuity with archiving.

Database overload impacts business processing

Safeco has been in business for over 80 years, helping clients protect their valuable assets and secure their financial future. The company sells auto, home and business insurance, as well as surety bonds, through a network of trusted independent agents, brokers and financial advisors nationwide. Safeco’s mission is to provide the simplest experience for buying, selling and owning insurance.

Safeco implemented PeopleSoft® Enterprise Human Capital Management (HCM) and PeopleSoft Financial Management to streamline and standardize key business processes. Since that time, an estimated 5,000 tables in the supporting databases had accumulated large volumes of transactional data. In fact, the General Ledger (GL) tables had grown from 4 to 17 million rows in just one year.

Although only current GL data was needed to support daily business processes, Safeco had to maintain historical financial information for reporting and auditing purposes. In addition, the PeopleSoft Log tables, containing rarely accessed data, were growing at a rate of three million rows per month and consuming limited storage capacity on the production database. This log data was not required for data retention compliance.

“We had to address the increase in CPU usage needed to accommodate the large number of transactions being processed. The most CPU-intensive processes included PS Queries and PeopleSoft PS/nVision reporting,” said Anne Pan, Safeco DBA. “Accumulating and storing both current and historical data in the online production environment was beginning to affect application performance. Functional users, especially the controllers and accounting staff, were experiencing delays in transaction processing.”

“Our biggest challenges were to shorten the elapsed processing time and improve application performance,” said Pan. “We do have a month-end financial close window and missed a couple of closes. The IT department even scheduled a second shift. But when missed deadlines received attention from senior management, we decided to solicit the aid of an outside consultant to help analyze and suggest options for resolving both CPU and performance issues.”

Proven database archiving outweighs in-house solution
Based on consultant recommendations, Safeco began searching for a database archiving solution. “We didn’t want to spend a lot of money, so we considered several options,” said Pan. “We looked to PeopleSoft and also considered developing an in-house solution. However, another group within our organization had been successfully using IBM® Optim’s™ test data management capabilities. At the same time, the IBM Optim Data Growth Solution was being used to archive data from PeopleSoft Enterprise HCM and from an in-house statistics application.”

Already familiar with the Optim solution for test data management, Pan recommended Optim to Becky McGill, Senior IT Analyst and technical lead on this archiving project for PeopleSoft Enterprise Financials.

“Implementing a database archiving solution that was already validated internally at Safeco strongly outweighed having to write and maintain our own custom archiving program,” said Pan. “Our decision to implement Optim was evident – we already knew that Optim was easy to use and understand.”

Safeco implements database archiving
McGill was able to save a significant amount of time by modeling the archive process successfully implemented in the HCM group and adjusting the business policies to apply to GL and other financial data. Implementing Optim took less than four months, including time to define the archive business policies, test and deploy the software and complete the initial archive.

“Initial archive processing was completed in just three days, including archiving the historical GL data, inserting the archived data into archive history tables in our production database and verifying the archived data before removing it from the production database,” said McGill. “We ran the archive jobs at night during off-peak times and populated the archive tables during the day. Next, we compared and validated the rows before deleting them from the transactional tables. The summary and detail reports that Optim generates automatically after each job made the validation process much easier and allowed us to verify the accurate completion of each job.”

“Implementation went very well,” said Pan. “When we had a question about archiving data to tape, the Optim technical support staff was quick to provide guidance and information that helped us to use the tape capability more efficiently.”

Database archiving reduces CPU consumption
McGill initially archived four years of historical GL data and also archived data from three PeopleSoft Log tables, removing over 20 million rows of log data that was rarely or never accessed. As a result, Safeco reduced the size of the PeopleSoft production database by over 14 percent from these two areas alone.

“Database archiving has allowed us to reduce CPU usage by roughly 20 to 25 percent per month,” said McGill. “And the annualized savings from implementing database archiving for the GL tables is over $90,000. This CPU savings directly benefits the bottom line by reducing charge backs to the business units.”

“CPU upgrades are usually scheduled way ahead of time,” added Pan. “Keeping a cap on PeopleSoft CPU consumption allowed us to stay on course with our planned CPU upgrades. Since we archive historical journal and ledger data to online tables, there were no associated DASD savings. However, in the case of PeopleSoft Log tables, data was moved from online DB2 tables to tape, thus saving DASD costs.”

PeopleSoft queries access current and archived data
To maintain timely transaction processing and control costs, McGill archived historical data from the PeopleSoft Financials database regularly. “Our plan is to maintain the current plus the previous two years of historical data on the production database and archive the older data, based on the requirements of our functional users,” said McGill. “At the beginning of each year, we meet with our business users, who determine what and when to archive. Their primary concern is being able to view and report on archived historical transaction information whenever they need it. The way we manage our database archiving solution satisfies that requirement.”

Safeco decided to store the archived GL data in archive history tables in the current production database so that functional users have access for reporting purposes using PeopleSoft queries. Because the functional users would rarely need to access PeopleSoft Log data, this data is archived and stored to tape for three months and then discarded. As a result, application and processing performance improve and users maintain query access to current and archived Financials data.

Archiving works successfully ‘behind the scenes’
“We’ve definitely seen a ‘behind-the-scenes’ improvement in DB2 processing performance and shorter CPU processing times because the production databases are at a more manageable size,” said McGill. “It also takes less time for backup and recovery operations because we aren’t processing the high volume of data collected in the PeopleSoft Log tables.”

Next, Safeco plans to archive from the Accounts Payable, Purchasing and Asset Management modules to improve response time on database queries and maintain high levels of application performance.

“Optim has worked well for us. We’ve been able to reduce our CPU usage and associated charge backs for a substantial cost savings for each department,” said McGill. “Most of all, we can accommodate increased transaction processing and still allow business users to view and report on the archived historical data whenever needed.”

About IBM Optim
IBM® Optim™ enterprise data management solutions focus on critical business issues, such as data growth management, data privacy compliance, test data management, e-discovery, application upgrades, migrations and retirements. Optim aligns application data management with business objectives to help optimize performance, mitigate risk and control costs, while delivering capabilities that scale across enterprise applications, databases and platforms. Today, Optim helps companies across industries worldwide capitalize on the business value of their enterprise applications and databases, with the power to manage enterprise application data through every stage of its lifecycle.

For more information
To learn more about IBM Optim enterprise data management solutions, contact your IBM sales representative or visit: www.optimsolution.com.