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.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.