DB2 Materialized Query Tables (MQTs) are really great for DB2 data warehousing situations and also for all types of reporting applications. Combining multiple DB2 MQTs is one technique that is good for providing all types of reporting capabilities and answers. By combining multiple DB2 MQTs, the DB2 SQL optimizer can sometimes automatically rewrite the queries from the base tables to improve DB2 performance and quickly execute against the combined DB2 MQTs. Unfortunately, even the DB2 optimizer is not always smart enough to rewrite the SQL to go against the correct MQT after you’ve combined DB2 MQTs. However, once your users find out how quickly they can get their reports by writing optimizing SQL, they will redirect their SQL query to the proper DB2 MQT or views over several combined DB2 MQTs to get quick answers and reports.
To design these types of DB2 data warehousing and reporting savings, look at the long running reports or queries within your system. One of the most common SQL optimization efforts and that has been successful for several of my DB2 consulting clients is creating DB2 MQT and View combinations to provide Year-to-Date sales or other grand total types of information.
This design is very straight forward as noted in the figure below. There are two options to begin this SQL optimization: start with your main transaction operational table information and define a DB2 MQT, or run an extract process (or trigger) to put its data into another staging table. Care should be taken for this process because of the high volume and locking considerations. Then summarize the operational data into daily, weekly, quarterly, yearly, accounting periods or other specialized summaries. This process can be done through batch processes or DB2 MQT relationship set-up that refreshes the different levels of the DB2 MQT structures.
Also note that these structures can be supported by each other. The Quarterly data MQT is built from the Monthly data table, the Monthly data MQT is built from the Weekly data table that is built by the Daily sales data table thru the Transaction staging table. These summarizations again can be quickly created or updated through regular programs, triggers or defined through MQT relationships.
The Year-to-Date view is created with the proper calculation within its view definition or can be another DB2 MQT within the environment. Another solution is to have the view definition recreated every day to reflect the year-to-date figures. These structures can quickly provide year-to-date sales figures, or many other types of figures within your business environment.
DB2 MQTs are really great for improving DB2 performance and providing quick answers for all types of business situations, including DB2 data warehousing. Combining various view definitions and DB2 MQTs provides a great opportunity for SQL optimization to eliminate long running queries, improve DB2 performance and quickly provide business answers.
Leave a Reply