Improve the performance of DB2 queries in a business intelligence environment-specifically for data warehousing and B query tuning methods (2)

Method 1: Define the appropriate referential integrity constraints between the fact table and the three dimension tables

In DB2, you can define primary key and foreign key constraints to allow the database manager to enforce referential integrity constraints on the data. Referential constraints such as foreign keys also help improve performance. For example, if you modify the subexpression TMP1 in the query in Listing 2, remove the local predicate on the Product_dim table, if you are in Sales_fact. A FOREIGN KEY constraint is created on the product_id, the optimizer eliminates the connection between Sales_fact and Product_dim. If a foreign key constraint is created, such a connection is considered lossless (lossless) and can be removed from the query because the data that the query needs to read from the Product_dim is in the Sales_fact table, and in the Product_dim connection to Sales_fact , only the primary key to Product_dim is referenced, and no other columns referencing Product_dim are referenced.

In the star pattern described in the Star Pattern Layout section, each date_id, product_id, and store_id that exist in the dimension must also exist in the fact table. Each ID is unique in the dimension table, identified by the primary KEY constraint created for each dimension table. Therefore, the fact table preserves the historical data (quantification) of the product when it was sold. The following table describes the primary keys and foreign keys that should be created in this pattern. Each uniqueness ID in the dimension has a corresponding foreign key constraint in the fact table.

Table	Column	Pk/fk	Target table (column)
Date_dim	date_id	Pk	No
Product_dim	product_id	Pk	No
Store_dim	store_id	Pk	No
Sales_fact	date_id	Fk	Date_dim (date_id)
Sales_fact	product_id	Fk	Product_dim (product_id)
Sales_fact	store_id	Fk	Store_dim (store_id)

Step 1 A: Perform an ALTER operation on the fact table to create an appropriate FK relationship between it and the dimension table. View the relationship between the fact table and the dimension table through the table above. Create an index on the sales_fact column (date_id,store_id) to compare to the MDC method described in Method 3, Method 3 uses a block index on (date_id,store_id).

Listing 23. Create a FOREIGN KEY constraint and index in the Sales_fact tabledb2 -tvf alter_sales_fact.txt -z alter_sales_fact.log

The contents of the list 24.alter_sales_fact.txt fileCONNECT TO DSS_DB;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY
(DATE_ID) REFERENCES DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY
(STORE_ID) REFERENCES STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY
(PRODUCT_ID) REFERENCES PRODUCT_DIM;
CREATE INDEX SKAPOOR.IND_DATE_STORE ON SKAPOOR.SALES_FACT (DATE_ID, STORE_ID);
CONNECT RESET;

Step 1 B: Collect statistical information about all tables:

The optimizer calculates the cost of an alternative query execution plan (QEP), based on statistics, and selects the best plan. Before proceeding to the next step, we need to collect some statistics.

Listing 25. Collect statistical information about all tablesdb2 -tvf runstats.ddl -z runstats.log

Listing 26. The content of RUNSTATS.DDLCONNECT TO DSS_DB;
RUNSTATS ON TABLE SKAPOOR.DATE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.STORE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.PRODUCT_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.SALES_FACT WITH DISTRIBUTION AND INDEXES ALL;
CONNECT RESET;