Tame Beastly Data with Summary Tables

Eddie was a production supervisor in the pre-computer era. He received comprehensive hourly production reports on paper. He also received urgent queries from managers, who needed up-to-date summaries spanning the entire year's production. He always had a ready answer.

Do you run summary queries against your database? Are they slowing down due to the sheer bulk of the raw data? Eddie may have your solution: keep summary tables. In this article, we will examine how DB2 can do it for you and have your answers waiting before you even ask.

Use summary tables to hold collated information and have them updated as required. Summary tables can be used explicitly or automatically to improve the speed of certain queries.

Summary tables can be:

• Do-it-yourself: Maintained by application logic or triggers
• Automatic: Maintained synchronously by DB2
• Semi-automatic: Maintained asynchronously by DB2 (updates upon request)
  OR
• Externally hosted: Take the data elsewhere for analysis and reporting. Where you have a lot of activity on your source data, it may be worth replicating to another machine. In fact, this is recommended practice for online analytical processing (OLAP), but outside the scope of this article.