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.

Referential Integrity and Locks

When it comes to enforcing parent-child constraint, DB2 is not as aggressive compared to Oracle. This can be demonstrated easily:

DB2:

Create a table P1 ( with a single column C1), and another table C1 which references P1 as foreign key owner. Populate Table P1 so that value '1' is in there.
(ok, I admit the choice of table name C1 is confusing at best, given that there is a column name called 'c1' in P1 ... )

Remember to commit.

Now, start up another CLP and issue this:

LOCK TABLE C1 IN EXCLUSIVE MODE

At this point, this CLP session would have exclusive lock on table C1.


Now, start up another CLP session, and issue this:

UPDATE P1 SET C1 = 2 WHERE C1 = 1

You will have no problem with that. The update will succeed. There is only one IX lock on table P1 which is owned by this session.


For Oracle, the situation is different. When you issue UPDATE P1 SET C1 = 2 WHERE C1 = 1, this session will attempt to obtain an SX lock (mode 3) on table C1, and it will block because of the exclusive lock by another session ( with the 'LOCK TABLE xxxx' command).

It does raise the question: which one is 'safer'? What if a single call is needed to get a consistent view of , say, join between P1 and C1? The answer will be revealed in future post ...

Constraint Violation and Locks

Came across this nasty surprise while doing some casual testing. It is a surprise because this is something that   seems unnecessary.

Here's a quick look at the problem. First, make sure auto-commit is off.

a) create a simple table T1 with a unique column:

CREATE TABLE T1 ( C1 NUMBER NOT NULL PRIMARY KEY)

b) insert a single row into T1

INSERT INTO T1 VALUES (1)

COMMIT


c) Now, let's try to do the same insert:

INSERT INTO T1 VALUES (1)


DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "DB2INST1.T1" from having duplicate values for the index key.
SQLSTATE=23505

**********************

So far so good. We have triggered a constraint violation.

d) Now, start up another CLP session and issue this:

UPDATE T1 SET C1 = 2 WHERE C1 = 1 

You will notice that this session 'stalls'. Why? Because it is blocked. The question is: blocked by what?

==> It is blocked by the previous INSERT session that caused the constraint violation.

Comment: In my opinion, it is very strange that DB2 keeps the locks for an INSERT that has failed. This behaviour is hindering concurrency. It should  just simply release those locks associated with the failed constraint and be done with it.