Using DB2 MQTs (DB2 speak for Materialized Views)

building semantic layer
1

I am working on Universe(s) set up for reporting against a UDB DB2 Data Warehouse.

The DW has all the necessary MQTs set up for our needs and Table Elimination is also set-up for speedy reporting. However, while testing, which has involved analysis of QEPs to likely queries, it has become apparent that MQT redirection and Table Elimination falls over when using sub selects against our Calendar Dimension table (which is joined to our large Fact tables).

If direct matching is applied to say Full_Week in our Calendar table, by placing a condition of Full_Week=200417 then all is OK. However, if a sub-query is applied, by placing a condition of Full_Week IN (SELECT Full_Week FROM WeekRef WHERE …) then both table Elimination and MQT redirection fails.

I know this is more of a DB issue, but it seriously impacts our Universe designs. We are forced in the short term to get re-write all predefined conditions that make use of a sub-query and temporarily hard code them (urrr). You can imagine this is pretty bad for filters such as “Last 3 Periods” and totally impractical for a Last Week filter. This is no solution to the problem as far as I am concerned.

Any DB2 bods out there got any ideas?

Daniel.

Daniel Williams :uk: (BOB member since 2002-08-19)

2

We faced a similar issue and decided to use @aggregate_awareness instead to force the use of the most beneficial table via universe design (instead of relying on DB2/UDB query rewrite)

Andreas :de: (BOB member since 2002-06-20)

3

I was afraid of that answer… Having said that, I’ve got absolutely no problem making the affected Universe(s) Aggregate Aware. Just got to sell that to a few sceptics!

The next question I have for you then is did you get Table Elimination to work OK? Aggregate Awareness can counter the MQTs being inflexible, but it can’t do anything for Table Elimination.

Daniel.

Daniel Williams :uk: (BOB member since 2002-08-19)

4

Please, can you explain what do you mean by “Table Elimination”?

Andreas :de: (BOB member since 2002-06-20)

5

Our Weekly Sales Fact Table, is actually a View on to a number of Period Sales Tables, i.e. 1 table for each period (4 weeks in a period).

Things are set up here so that given the FullWeek (or a range of FullWeeks) UDB DB2 will eliminate the tables it need not query.

The optimizer can redirect for conditions such as:
Full_Week= 200401
Full_Week in (200401,200402,200403)
Full_Week BETWEN 200401 AND 200402

… but just can’t handle:
Full_Week= some function returning FullWeek
Full_Week in (Select Full_Week FROM WeekRef WHERE …)

I can see why this may be the case, because to know which tables to eliminate it would have to actually execute the subquery…

It’s really quite annoying.

Daniel Williams :uk: (BOB member since 2002-08-19)

6

DB2/UDB “Table Eliminating” sounds like “Table partitioning” in Oracle…

Andreas :de: (BOB member since 2002-06-20)

7

Yes the very same thing. DB2 may even use the same terminology (I’m not sure) and Table Elimination may simply be the terminology of what is going on in the background. That’s asuming I got the DB2 terminology right in the first place!

Anyway that seems to be what’s going on and that seems to be my problem in a nutshell.

Daniel.

Daniel Williams :uk: (BOB member since 2002-08-19)