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

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.%')

commit

connect reset

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-36.

Example 2-35 Query involving a simple predicate

SELECT cust_id, COUNT(*)

FROM trans

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.