Hierarchical Query Processing and Access Method

In its simplest form, DB2 performs hierarchical query using a recursive CTE. The way to spot it is look for the UNION operator in the execution plan. The one shown here is a typical query plan.

Note the step labelled (7): This is where the main recursion happens. Once you know it is doing a NLJOIN from a TEMP ( step 5 resultset from elsewhere), you know that it has lost sight of the volume involved. But that is not the important bit.

Rows from this TEMP is used to probe the matching rows from the 'main' table on the right hand side of the operator. If there is an efficient access method ( eg, IXSCAN), this can be a very fast operation.

What if there is no such access method? What if there is no index?

You may ask what is the odd of that happening. Let's see ...what if the query is running not on a single table, but on a resultset from some tables joins? If that is the case, the resultset would have no precise access to its rows anymore. So, instead of having a NLJOIN into some rows using IXSCAN, you have an NLJOIN into rows using TBSCAN. Yes, for each row, coming from TEMP, it probes the entire table on the other side of the operator. We all know by now, that this is a performance killer.

For comparison: using a table containing 200,000 rows with proper index, the query completes in less than 1 second. using the same table *without* an index, it completes in 10 minutes.

Below is how it looks like when there is no index:

So, the next time you write a hierarchical query, keep this in mind.

db2caem And Optimization Guidelines Are Not Friends

Any attempt to run db2caem on a query with optimization guideline will be greeted with this message:

DBT7028E  "db2caem": The db2caem command failed because the DB2 database manager encountered an error while processing the specified input parameters and the specified SQL statement. Handle type: "3". Return code: "-1:2162".
           CLIInfo: SQLstate: "42601"
                    Return code: "-104"
                    Diagnostic message: "[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "'SELECT xxxxxxxx" was found following "WHERE STMT_TEXT LIKE".  Expected tokens may include:  "LIKE <pattern>".  SQLSTATE=42601
"

Too bad. 

db2top 'explain plan' - useful but unreliable

db2top is a handy tool to monitor the current database activity. One of my favourite features is the ability to 'explain' the statement present in the Dynamic query monitor screen. There is a big catcha though: it is merely showing the 'explain plan' at the time you choose to see the plan, it may or may *not* be the actual plan being used during the actual execution of the query.

Yup, it is as good as you manually running 'explain plan for <query>' without running the query.

Beware!

Blu Acceleration and Oracle Customer

Oct 2013 - Current fix pack = SP2. As IBM is trying to win over Oracle's customer with DB2, it is unfortunate that Blu Acceleration is not available *yet* in this condition:

===> Turning on DB2_COMPATIBILITY_VECTOR=ORA would *DISABLE* the ORGANIZE BY COLUMN feature.

Good luck to those trying to sell to Oracle customers with Blue Acceleration powerpoint slides :)

Organize by Column - Index Scan in v10.5 FixPack2

DB2 v10.5 Fixpack 2 has just been released. There is one bit of the changes as described in the fixpack summary that got my attention:

"The performance of a select, update, or delete operation that affects only one row in a column-organized table can be improved if the table has unique indexes, because the query optimizer can now use an index scan instead of a full table scan. "

As I wrote about the "Oraganize By Column" about 2 months ago, I thought it was unfortunate that DB2 was unable to use the primary key index to locate a single row. Naturally what this fixpack has promised to deliver is encouraging.

Unfortunately, after applying this fixpack and redoing the test, I am still not able to get the index scan to work for a ridiculously senseless and simple query like "SELECT C1 FROM T1 WHERE C1 = 10", where C1 is the primary key, with T1 holding 1 million rows. 

More bugs? Installation problem? Blah ...

ORGANIZE BY COLUMN - Part 2

In an earlier post, I demonstrated that DB2 would do a TABLE SCAN for a simple equality predicate on a primary key column. While that looks bad ( ya, looks REALLY BAD), something else is taking place under the hood. In most cases, the TABLE SCAN is not what we think it is.

While a TABLE SCAN is understood to scan the entire table, it is not really necessary if certain condition is met in an ORGANIZE BY COLUMN table. If the data type is datetime/boolean/numeric, the storage engine is able to perform DATA SKIPPING, which avoid scanning data ranges that *do not qualify* the predicate. Any pages picked up by the SCAN operation are guaranteed to contain data of interest. These pages might contain non-qualifying AND qualifying data, depending on the distribution. As a result, a TABLE SCAN pick up much less pages than necessary, and not the entire table is being processed.

Note that as of this writting ( 9-Sep-2013) , varchar/char column do not allow DATA SKIPPING, unless that column is an (enforced) primary key/unique.

Non-enforced uniqueness do not allow DATA SKIPPING as well.

There you go.

REPEATABLE READ: DB2 vs SQLServer

Just a short post to highlight the difference between the implementation of a similar sounding isolation level, namely REPEATABLE READ (RR), of DB2 and SQLServer.

In SQLServer, RR allows phantom reads, whereas DB2's RR does not allow phantom read.

Just because they have the same term, it does not *guarantee* they are the same ;)

As the saying goes:
*Assumption is the mother of all screw-ups*