Improve the performance of DB2 queries in a business intelligence environment-a query tuning method specifically for data warehousing and BI (1)

Introduction: Running large queries efficiently is a top performance challenge in a business intelligence environment. Learn the skills to improve query performance in ibm®db2® data servers in this environment. Step through the various approaches and experiment on your own system. Apply each method to an SQL statement and evaluate performance using the Db2batch tool.

Brief introduction

This article focuses on some of the methods that can be used to efficiently execute large queries in decision Support systems (DSS). These queries are usually simple select queries that access more data. Here are some of the ways we'll discuss:

1, establish the appropriate referential integrity constraints

2. Use materialized query table (MQT) to copy tables to other database partitions to allow merge joins on non-partitioning key columns

3. Use multidimensional cluster (MDC)

4. Use table partitioning (db2®9 new features)

5, combined with table partitioning and multidimensional cluster

6, using MQTS to calculate the aggregate results in advance

The examples in this article are for DB2 9 running on the Windows platform. However, the concepts and information are useful for any platform. Since most business intelligence (BI) environments use the DB2 database partitioning Feature (DPF,DB2 partitioning feature), our example uses DPF to divide data into multiple physical and logical partitions.

Database layout and Settings

This section describes the physical and logical layout of the databases that are used to perform tests on our systems.

Star Pattern Layout

This article uses the star pattern shown below:

Listing 1. Star mode

                		
Product_dim             Date_dim
          /
           \          /
            sales_fact
                |
                |
           Store_dim