BLEVEL: How High Can You Go?

While investigating index splits, I encountered a fairly 'mind-numbing' number. But first, here's the basic set up on a database with default page size 4K:

CREATE TABLE LONGT (V1 CHAR(254)) ;
CREATE INDEX IDX_LT ON LONGT ( V1);

And populate this table with 50,000 rows of ascending 'numbers', starting with 1.

Guess what BLEVEL you get?

Drumroll please ... it is ... 5!

I'm beginning to wonder if this BLEVEL has the same meaning as the BLEVEL I have been exposed to ... ;)

Optimizer: Fake it like you mean it

There is another useful utility that lets you fool the optimizer into thinking it has more resource than there is in the hardware. The utility is db2fopt.

This is useful if you are testing the optimizer in a test environment that is considerably less powerful than the production environment. eg, the sortheap in the production system might have 80000 pages, but your tiny test machine might max out at 2000. What do you do then? You can use db2fopt to fake it:

db2fopt <database> update opt_sortheap 80000

Easy.

It is a bit unfortunate that only these 4 are available to be 'faked' as of this writing ( for DB2 LUW v10)

• opt_buffpage
• opt_sortheap
• opt_locklist
• opt_maxlocks

Replicating a database's 'statistics'

db2look has a useful option '-m' that allows you to 'replicate' a database's statistics, which is not unlike Oracle's DBMS_STATS.EXPORT_XXX_STATS. One advantage it has over the export is that it gives you an insight into the stats components required, in the form of individual UPDATE statements. The other advantage is that you don't have to prepare a table to be 'exported' or 'imported' into.

eg:

db2look -d sample -t t1 -m -o test.sql

This will 'look' into the database 'sample', pull stats only for table 't1', and redirects the output to file 'test.sql'.

One word:  Convenient!

ColumnStore Index in SQLServer (2012)

This is obviously neither Oracle nor DB2 feature, but I like it so much that I have put it in my wishlist. For traditional row-based RDBMS, OLAP kind of activities can really hurt performance. On the flip side, newer column-based databases excel in OLAP, but suffer in OLTP type of activities. Forget about throwing in SSD and terrabytes of RAM to 'solve' the issues ( eg: Oracle's Exadata, IBM's pureSparta ), this new kind of index offers partial relief to traditional RBDMS in OLAP system. 'Best' of both worlds at a fairly low cost.

Ellison, are you listening?

CLUSTERING_FACTOR = CLUSTERFACTOR ?

Running, in Oracle compatible mode, you get a set of system catalog tables that 'resemble' what you see in Oracle. Column names similarity aside, there are plenty of gotchas that you need to be aware of.

eg: You get 'USER_INDEXES' in DB2 if you create  the DB in Oracle compatible mode. However, the column values could mean something totally different.

If you see the column CLUSTERING_FACTOR in USER_INDEXES, it is definitely related to index 'clustering' , but the value is no way what you expect from a 'real' Oracle DB. This CLUSTERING_FACTOR is actually the CLUSTERFACTOR in DB2's original 'syscat.indexes'. If you have a table with , say, 100000 pages, you might have a CLUSTERING_FACTOR of just 1, which can be very puzzling to those coming straight from the Oracle world.

To confuse matter, there is another value called 'CLUSTERRATIO' in syscat.indexes, which is also an indicator of the data clustering ...

I will just leave a 'WARNING' sign here ....

Query Results Depends on Processing SPEED?

Let's say you can go back in time and run a query over and over again. Let's also assume that there are many other concurrent DML on the tables *while* the query is executing. Do you want your query result to be
1) the same , everytime
2) anything, depends on what is happening to the rows.

??

I prefer option#1.

It is important to note at this point that none of the isolation level in DB2 , (RR, RS, CS, UR) can give you option#1. RR, RS, and CS would only prevent modifications *after* those matching rows have been fetched. So, it is still possible to modify some rows that are supposed to match the criteria of the query *before* they are fetched, and that may mean the query result can differ from run to run.

Sounds fun? ;)

RUNSTATS ahoy!

I have to say, this has got to be the biggest nasty surprise of all! Again, this is the standard blocking issue we all frown upon.

Test case:

Disable auto-commit. Perform runstats on a table, say, T1:

RUNSTATS ON TABLE T1


Start up another CLP session, issue this:

SELECT * FROM T1

What do you get? Nothing! Well, it is more like 'waiting for something to happen', but it is as good as nothing.

This SELECT session is waiting on a lock type CatCacheLock, presumably the RUNSTATS command has an exclusive lock on this, and this SELECT session needs it in EXCLUSIVE mode too ....why exclusive mode?  Hmmm ...another Alfred Hitchcock moment ... :)

What is the damage then, you may ask. Imagine this scenario: you have a huge table with billions of rows, and you are running RUNSTATS on it, and at the same time, there are some users wanting to read this table ... you will get an ultimate test of patience ;)

Update: This behaviour seems to be timing-sensitive. So it seems that the other read session only requests for an exclusive 'loading lock' after runstats ends and the read begins.