Feeling lucky? DB2 V8 FixPak 13 hits the streets

IBM support and downloads: DB2 UDB Version 8.1 FixPak 13 (also known as Version 8.2 FixPak 6)

The platform list on this FixPak announcement serves as a reminder of the interesting fact that nearly half of all DB2 LUW versions are some form of Linux. Test first, and have fun.

Estimate MDC table size with free alphaWorks software tool

IBM alphaWorks: Multi-Dimensional Clustering Table Size Estimator for DB2

Thanks to freshmeat.net and LinuxQuestions.org for pointing out this promising little tool, which is currently available for Windows and AIX. Perhaps if it gets enough interest, the author will release a Linux version as well.

IDUG presentation abstracts due by September 1

IDUG conference PDF document: IDUG 2007 North America Call for Presentations

The conference planning committee doesn't ask for much in an abstract - just a few bullet points and a short paragraph - but you'd be surprised how daunting that can be when you've waited until the final hour to type them up. This little reminder can help you get organized sooner and with less stress, but only if you act on it now. Even if you spend an entire week just mulling over your presentation ideas, you'll still have one more week to work it down to a concise proposal that will blow everyone away.

2006Q3 issue of DB2 Magazine available

DB2 Magazine: Volume 11 Number 3 - Quarter 3, 2006

The latest issue of DB2 Magazine, devoted almost entirely to pureXML, is available as pixels and on paper. If you've been waiting to get your feet wet with DB2 9's native XML engine, this issue has plenty of helpful articles to get you started.

I am guessing that the decision to obscure half of Thor Thomassen's face on the cover was an artistic one and not done to hide some sort of disfiguring injury.

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.