Re: Comparison of DB2 and Oracle?

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Thu, 21 Oct 2004 14:33:56 GMT
Message-ID: <oNPdd.290528$D%.276997_at_attbi_s51>


"Serge Rielau" <srielau_at_ca.ibm.com> wrote in message news:2tprmrF23getiU1_at_uni-berlin.de...
> OK, here is the deal:
> When you execute a dynamic statement which depends on table T.
> DB2 will hold a usage lock on T until the end of the transaction.
> So noone will be able to alter T (in a non-trivial way) until this
> transaction is over.
> An alternate design would be to release the lock after usage.
> However in the interest of keeping the cache fast the capturing of locks
> needs to be minimized. Schema evolution is considered a much rarer (more
> rare??) event than cache-hits
>
> Does that answer the comment?
>
> Cheers
> Serge
Here was the behavior that was observed. (on db2 on a mainframe)

1. Issue commit;
2. Issue a select statement (like select ... from mytable where ...)
3. People try to bind their programs and no dice.
4. Minutes pass and programmers start calling because they can't get their
work done.
5. Issue a commit (or rollback).
6. People can now bind their programs.

The explanation according to the manual was that DB2 doesn't do dynamic SQL, it takes dynamic SQL and turns it into static SQL and then binds that static SQL and runs it. Since a commit happens minutes later access to the plan table is serialized. OUCH!

We observed this behavior and so had to turn the system into an autocommit system just so we didn't turn a multi million dollar machine into the equivalent of an based 8080 PC.

This went on for at least a couple of years and then I went onto other companies.
Jim Received on Thu Oct 21 2004 - 16:33:56 CEST

Original text of this message