XPost: comp.databases.ibm-db2   
   From: kennedy-downwithspammersfamily@attbi.net   
      
   "Serge Rielau"  wrote in message   
   news:2tprmrF23getiU1@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)