How to Improve the DB2 query performance in the business intelligence environment

Introduction: DB2 databaseOfQueryThe function is a good performance in the DB2 database. The query function of the DB2 database shows the business intelligence of the DB2 database. This article mainly discusses some methods that can enable efficient execution of large queries in the Decision Support System (DSS. These queries are simple select queries that access a large amount of data.The following are some methods we will discuss:

1. Establish appropriate integrity constraints;

2. Use a materialized query table (MQT) to copy the table to other database partitions to allow merged connections on non-partition key columns;

3. Use multi-dimensional clusters (MDC );

4. Use table partitioning (new feature of DB29 );

5. Use table partitions and multi-dimensional clusters in combination;

6. Use MQT to pre-calculate the aggregation result.

The example in this article is for running DB2 9 on Windows. However, concepts and information are useful to any platform. Because most business intelligence (BI) environments use DB2 Database Partitioning Feature (DPF, DB2 Database partition Feature ), our example also uses DPF to divide data into multiple physical and logical partitions.

Database layout and settings

This section describes the physical and logical la s of databases used for testing on our system.

Star mode Layout

This article uses the star mode as follows:

Listing 1. Star Mode

The table is defined as follows:

The fact table SALES_FACT contains the overall sales information for 2006. It includes the product sales date, product ID, store ID that sells the product, the number of products sold, and the product price. The TRANSACTION_DETAILS column is added to the fact table to generate more I/O when accessing data from the fact table.

1. The dimension table DATE_DIM contains the unique date and the corresponding month, quarter, and year during the store opening period.

2. The dimension table PRODUCT_DIM contains different products sold by the company. Each product has a unique product ID and a product description, model, and texture.

3. The dimension table STORE_DIM contains different store IDs, store locations, blocks, and regions.

Database partition information

Each table is in its own partition group. The three dimension tables are small, so they are located in one database partition. The fact table has four partitions.

Tablespace Information

Buffer Pool Information

The default buffer pool used for testing in this article is ibmdefabp bp, which consists of 1,000 4 K pages. In this test, all tablespaces share the buffer pool. In a common BI environment, different buffer pools are created.

Primary Query

The following query is used to test the methods discussed in this article. This query executes an external connection to compare the sales information of 10 stores in February and February.
 

This is all about the DB2 database query I want to introduce to you. Some people may think it is not enough, so I will continue to introduce some content to you in the future, hoping to help you.