Materialized query tables—examples shipped with Db2

In addition to the examples shown in this information, Db2 provides a number of samples to help you design materialized query tables for automatic query rewrite.

Begin program-specific programming interface information. The samples are based on a data warehouse with a star schema database. The star schema contains one fact table, SALESFACT, and these four hierarchical dimensions:

  • A TIME dimension that consists of one dimension table
  • A PRODUCT dimension that is a snowflake that consists of four fully normalized tables
  • A LOCATION dimension that is a snowflake that consists of five partially normalized tables
  • A CUSTOMER dimension that is a snowflake that consists of four fully normalized tables

See member DSNTEJ3M in data set DSN810.SDSNSAMP for all of the code, including the following items:

  • SQL statements to create and populate the star schema
  • SQL statements to create and populate the materialized query tables
  • Queries that Db2 rewrites to use the materialized query tableEnd program-specific programming interface information.