64 High-Function Business Intelligence in e-business
The advantage of extracting this information from the package cache is that it is
less disruptive than running Event Monitor.
Once all the dynamic SQL statements of interest have been collected, they need
to be prioritized by importance.
The SQL shown in Example 2-34 can be adapted to store the results of dynamic
SQL capture into a suitable table for subsequent analysis.
Example 2-34 Capturing snapshot data into a table
connect to SAMPLE
delete from ADVISE_WORKLOAD where WORKLOAD_NAME='GET SNAPSHOT'
get snapshot for dynamic sql on SAMPLE write to file
insert into ADVISE_WORKLOAD (WORKLOAD_NAME, STATEMENT_TEXT, FREQUENCY, WEIGHT)
(select 'GET SNAPSHOT',stmt_text,NUM_EXECUTIONS, 1.0 from table
(SYSFUN.SQLCACHE_SNAPSHOT()) sqlsnap where stmt_text not like '%ADVISE_%' and
stmt_text not like '%EXPLAIN_%' and stmt_text not like '%SYSIBM.%' and
stmt_text not like '%SysCat.%' and stmt_text not like '%SYSCAT.%')
2.8.2 Step 2: Generalize local predicates to GROUP BY
Consider each query in turn, and generalize the local predicates to a GROUP BY.
A very simple example of this exercise is shown in Example 2-35 and
Example 2-35 Query involving a simple predicate
SELECT cust_id, COUNT(*)
WHERE cust_id > 1000 AND cust_age < 50
GROUP BY cust_id
In Example 2-35, the simple predicate is “...WHERE custid > 1000 AND
cust_age < 50“. Generalizing the local predicate involves converting this
predicate to a GROUP BY in a materialized view as shown in Example 2-36. The
assumption made in this materialized view is that user queries are equally likely
to choose from all possible values of
cust_id and cust_age, since we have
chosen not to add a filtering predicate in the materialized view.
In Example 2-9 on page 45, the materialized view has a filtering predicate of
“...WHERE cust_id > 500” which implies that the predominant queries will choose
cust_id values above 500, which might be associated with premium customers.