Data Purging and Performance Tuning
Business Question/Need
Client required a data purging effort to eliminate three years of historic insurance data. In addition performance of the system was slow and the client requested analysis to determine the cause of the performance degradation. The client suspected that one client-server application was the cause of the problem.
Technical Solution
Pinnacle consultants performed the data purging project. In their further analysis determined several opportunities for system performance improvement. As a result of the analysis changes were made in the database system definition in the following ways:
-
Batch jobs were programmatically optimized for better throughput;
-
30 tables were reorganized and redefined to make better use of indexes, serially advancing keys and freespace definitions;
-
Table scans were eliminated from the predefined queries in the client-server application
These changes resulted in an immediate 22% performance improvement in the system performance. Later changes and enhancement brought about a 50% improvement exceeding the customer’s expectations.
Technology: DB2 DBMS
Project duration: 6 months
PTR Resources: 2
Benefits to Client
-
Data successfully purged from system.
-
More responsive application interfaces as a result of improved performance.
-
Cost savings of several million dollars per year as a result of lower maintenance and improved productivity.
-
Savings of multiple giga bytes of storage space as a result of table redefinition and schema restructuring.