To clarify - MDC and MQTs can be used in DB2 Express-C

You'll thank me for not coming up with some cutesy title for this post. It's been a bit frustrating to see such a powerful DBMS hit the market for free, only to get murky, contradictory coverage regarding important details. If you are among the many who are utterly confused about what can and cannot be done with DB2 Express-C, you are apparently in good company, and are now in for some straight answers.

The biggest questions concern multi-dimensional clustering and materialized query tables, both of which can add tremendous analytical power to databases that are otherwise designed for an OLTP workload. The answer is yes - absolutely - you can exploit MDC and MQT in DB2 Express-C, even though it may sound too good to be true. Just in case you don't believe me either, I am posting the proof here:


$ db2licm -l
Product name: "DB2 Express Edition"
License type: "Unwarranted"
Expiry date: "Permanent"
Product identifier: "db2exp"
Version information: "9.1"
Max number of CPUs: "2"
Annotation: "6;(_uw)"

So we're using Express-C. Believe me?

$ db2level
DB21085I Instance "db29exc1" uses "64" bits and DB2 code release "SQL09010"
with level identifier "02010107".
Informational tokens are "DB2 v9.1.0.0", "s060629", "LINUXAMD64", and Fix Pack
"0".
Product is installed at "/opt/ibm/db2expressc/V9.1".

...the GA version - not an open or closed beta.

$ db2 "CREATE TABLE mdctest (store_id INTEGER NOT NULL PRIMARY KEY, area_region INTEGER NOT NULL, area_salesrep INTEGER NOT NULL) ORGANIZE BY DIMENSIONS (area_region,area_salesrep)"
DB20000I The SQL command completed successfully.

$ db2 "DESCRIBE INDEXES FOR TABLE mdctest SHOW DETAIL"
Index Index Unique Number of
schema name rule columns Column names
------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------- -------------- ------------------------------------------------------------
SYSIBM SQL060814215526560 D 2 +AREA_REGION+AREA_SALESREP
SYSIBM SQL060814215526680 D 1 +AREA_SALESREP
SYSIBM SQL060814215526700 D 1 +AREA_REGION
SYSIBM SQL060814215526720 P 1 +STORE_ID

4 record(s) selected.

The Ds in the DESCRIBE INDEXES output show that there really are multi-dimensional block indexes on our silly example table, proving that MDC is really being used here.

Now, on to MQTs:

$ db2 "CREATE TABLE mqttest AS (SELECT area_region, area_salesrep, COUNT(*) AS store_count FROM mdctest GROUP BY ROLLUP(area_region, area_salesrep)) DATA INITIALLY DEFERRED REFRESH IMMEDIATE"
DB20000I The SQL command completed successfully.

$ db2 set integrity for mqttest immediate checked not incremental
DB20000I The SQL command completed successfully.

Hopefully this helps you out a bit. Have fun.