Managing performance of DB2 Ad Hoc Reports
The DB2 financial reporting systems at Westinghouse Savannah River Company consists of 212 standardized reports that over 1034 users have accessed in 1993 to generate their reports. Each report has a range of selection criteria that the users can specify. Depending on the selection criteria, a report can access from a few rows to millions of rows of data. When this new DB2 system went into production in 1992, the CPU was at 100% utilization. From the beginning, ad hoc reports had a backlog of 4--5 days. Since DB2 was a new DBMS, most people blamed the poor report turn around times on DB2 as an inefficient DBMS and on a shortage of CPU cycles. Since we are unable to purchase a more powerful CPU, the only option left to us was to improve report turn around was system and application performance tuning. This document presents our efforts in these areas. Education of users in report submission was a starting point. And as to index tuning techniques that were applied, we created more friendly indexes, used clustering indexes, and used a reorganizing mechanism. A more efficient SQL was written which saved a lot of money.
- Research Organization:
- Westinghouse Savannah River Co., Aiken, SC (United States)
- Sponsoring Organization:
- USDOE, Washington, DC (United States)
- DOE Contract Number:
- AC09-89SR18035
- OSTI ID:
- 10123759
- Report Number(s):
- WSRC-MS-94-029; CONF-940595-1; ON: DE94006669
- Resource Relation:
- Conference: 4. annual conference of the international DB2 users group,San Diego, CA (United States),8-12 May 1994; Other Information: PBD: [1994]
- Country of Publication:
- United States
- Language:
- English
Similar Records
New generation solutions for international petroleum data
Ad Hoc Query Support For Very Large Simulation Mesh Data: The Metadata Approach