Wednesday, August 7, 2013

IOQWT - A typical IBM acronym that works for DB2 BLU

IOQWT is a typical acronym, used to deal with lengthy product names like IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows. It is part of the InfoSphere Optim suite of tools for data lifecycle management. IOQWT usually helps to tune single queries or entire workloads. Some licenses for the tool are included in Advanced Editions and the Developer Edition of DB2. That is how I got my fingers on it. And the reason I had to try out using IOQWT is that the so-called Workload Table Organization Advisor (WTOA...?) can predict whether it makes sense to convert tables from row organization to a column-organized layout, i.e., whether DB2 with BLU Acceleration is a big benefit.

My journey started by starting up Data Studio with IOQWT integration. In the so-called Data Source Explorer I had to activate my database for tuning. After the license had been applied and additional internal-use tables been created, I could start tuning, i.e., invoke the tuning advisors. The first was to tell IOQWT what statements to analyze. It allows to directly paste a single query as text, however, it only recommends table organizations for a workload, a set of queries. Thus I chose to import a file with three queries. My test database uses a TPC-H schema and I imported 3 queries.

After saving the 3 queries to a workload, IOQWT was ready for action. As next step I could select what types of advisors it should run and what type of tuning suggestions I was interested in. After marking Table Organization as the kind of advice I proceeded to the next step, waiting for results. :)

As you can see below, IOQWT analyzed six tables from my small TPC-H database. If I would convert tables to column organization (BLU Acceleration), it predicted a performance improvement of 98.79%. The most gain would be for the first query which right now has the most costs and would have least costs associated after the conversion. So DB2 with BLU Acceleration seems to make sense for my workload and by analyzing it in IOQWT I got that information without converting my database.

That's it for today, see older posts on DB2 with BLU Acceleration.
Results from Optim Query Workload Tuner suggesting DB2 BLU