Re: Company thought DB2 will be better than Oracle.

Jim,

I can't answer your assertion to this level of detail ... I'll leave that to someone else from the lab who knows more about the DB2 concurrency model. I can tell you that

• I'm not sure that this is accurate ... "turns dynamic SQL into static SQL"?
• even if it is, you are talking about an experience on DB2/MVS from years ago
• there are drawbacks about Oracle's concurrency model also. I believe that the lock status is maintained on each data block potentially requiring disk access in certain cases.

Jim Kennedy wrote:

> DB2 doesn't do dynamic SQL; it turns dynamic SQL into static SQL and runs
> that. If you issue dynamic SQL (and do not commit) then anyone who is
> trying to bind after you is hung until you commit. Why? Because the
> dynamic sql is bound and a plan is generated, a row is added to the plan
> table (thus blocking others from adding to the plan table, until you
> commit). Since the concurrency model in db2 is not very concurrent people
> issueing dynamic sql lock out those trying to bind their plans in. It
> really pisses the developers on the system off. I was at ATT (American
> Transtech) years ago with DB2 running on a mainframe and this was a major
> problem. So we had to take the application and remove any transactions and
> just do everything in an autocommit type of mode.(issue select...;
> commit;...etc.)
>
> Binding is a nice and powerfule thing, but the way IBM has implimented it it
> really makes DB2 an autocommit only type of database. Ugly real ugly.
> Jim
>
> "Larry Edelstein" <lsedels_at_us.ibm.com> wrote in message
> news:3F632A76.D3609842_at_us.ibm.com...
> > Binding is done to support a feature called Static/Embedded SQL. Static
> > SQL is suitable for situations where the SQL is somewhat predictable.
> > You develop the SQL as part of the application code and "embed" it into
> > the application so to speak. Then, you precompile, compile, link edit,
> > and BIND the SQL into what is called a package (which essentially is a
> > Load module). The package is an executable ... and when invoked, no
> > compilation or bind is necessary ... therefore, the performance tends to
> > be significantly better than dynamic SQL.
> >
> > Larry Edelstein
> >
> > Neil Truby wrote:
> >
> > > > Larry Edelstein
> > > >
> > > > Neil Truby wrote:
> > >
> > > > > You forgot to mention that lovely old IBM tradition, BINDing.
> > > > "Larry Edelstein" <lsedels_at_us.ibm.com> wrote in message
> > > news:3F631D4B.899933B5_at_us.ibm.com...
> > >
> > > > Which many people see as an advantage by the way ...
> > >
> > > Please expand ..?
> >
Received on Sat Sep 13 2003 - 19:06:00 CEST