DB2 UDB's High-Function Business Intelligence in e-business

Chapter 2. DB2 UDB’s materialized views 63

We will briefly review each of these steps, and then use an application to

illustrate generalizing of a few local predicates such as DISTINCT, compound,

ROLLUP and CASE.

2.8.1 Step 1: Collect queries & prioritize

The best source of information about SQL queries is the DB2 UDB package

cache using the DB2 Snapshot Monitor. It provides a list of all the SQL

statements, and the frequency of their execution.

The following command provides a verbose listing of all SQL statements, as well

as the total number of executions per statement:

db2 get snapshot for dynamic sql on <database>

Figure 2-11 lists a portion of the results of this command.

Figure 2-11 Get snapshot for dynamic SQL

Note: We will only be focusing on dynamic SQL and deferred refresh

materialized views.

Important: This approach only lists those SQL statements that are currently

in the package cache. It excludes those statements that have been flushed

since the last database restart or activation, as well as due to package cache

size limitations. Users should therefore collect dynamic SQL statement

executions over a period of time in order to arrive at the list of queries requiring

optimization.