SEO

Some time ago I was working with DB2/400 - and one of the most appreciated
features was the concept logical files - a bit more than views in that they
maintained an access path. That is it maintained an index of the records in
the physical table(s) depending on specified view selection criteria -
including order of records.

Some time ago I was about to create an Oracle based application and the
Oracle person in the project maintained that Oracle views are more or less
pointers; they are not maintained by the DBMS and everytime you access them
the view selection criteria would be executed and evaluated. There was
something called virtual tables as well, that were separatly maintained, but
they kept a complete copy of the tables concerned instead of an index
pointing to the real tables.

On DB2/400 views - or LFs - was a great way of improving performance, when
there was no need to issue the specific select criteria all the time in your
application, since maintenance of them was not the responsibility of the
application, but of the DBMS, that is the OS.

On DB2 UDB for Windows/Unix - how do views work? Are they reevaluated
everytime a select on a specific view is performed or are they maintained by
the DBMS and reevaluated during certain points in time, like everytime a
record is added, once a day or the first time the view is accessed?

Kind Regards

Johan Hellstrom

news.bahnhofbredband.se wrote:
> Some time ago I was working with DB2/400 - and one of the most appreciated
> features was the concept logical files - a bit more than views in that they
> maintained an access path. That is it maintained an index of the records in
> the physical table(s) depending on specified view selection criteria -
> including order of records.
>
> Some time ago I was about to create an Oracle based application and the
> Oracle person in the project maintained that Oracle views are more or less
> pointers; they are not maintained by the DBMS and everytime you access them
> the view selection criteria would be executed and evaluated. There was
> something called virtual tables as well, that were separatly maintained, but
> they kept a complete copy of the tables concerned instead of an index
> pointing to the real tables.
>
> On DB2/400 views - or LFs - was a great way of improving performance, when
> there was no need to issue the specific select criteria all the time in your
> application, since maintenance of them was not the responsibility of the
> application, but of the DBMS, that is the OS.
>
> On DB2 UDB for Windows/Unix - how do views work? Are they reevaluated
> everytime a select on a specific view is performed or are they maintained by
> the DBMS and reevaluated during certain points in time, like everytime a
> record is added, once a day or the first time the view is accessed?
It's important to distinguish between preservation of the access plan
(how to get to the data) and the precomputation of the view.
DB2 for LUW has the concept of optimizer "profiles" where you can store
in an XML document how certain snippets of SQL shall be computed.
In DB2 V8 is is done only on an exception bases and the XML is typically
provided by support when you open a PMR. The reason is that when this
view in combined with other SQL this new context typically prefers a new
access path.

To pre-compute and preserve the resultset of a view DB2 for LUW supports
"materialized query tables' (MQT). Take a look at the CREATE TABLE ..
AS.. statement for details.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge,

First, a heartfelt thanks for the plethora of information you
provide.

Can you provide more information about optimizer profiles? This is
the first I've ever heard of them.

Lew

Serge Rielau wrote:
> news.bahnhofbredband.se wrote:
> > Some time ago I was working with DB2/400 - and one of the most appreciated
> > features was the concept logical files - a bit more than views in that they
> > maintained an access path. That is it maintained an index of the records in
> > the physical table(s) depending on specified view selection criteria -
> > including order of records.
> >
> > Some time ago I was about to create an Oracle based application and the
> > Oracle person in the project maintained that Oracle views are more or less
> > pointers; they are not maintained by the DBMS and everytime you access them
> > the view selection criteria would be executed and evaluated. There was
> > something called virtual tables as well, that were separatly maintained, but
> > they kept a complete copy of the tables concerned instead of an index
> > pointing to the real tables.
> >
> > On DB2/400 views - or LFs - was a great way of improving performance, when
> > there was no need to issue the specific select criteria all the time in your
> > application, since maintenance of them was not the responsibility of the
> > application, but of the DBMS, that is the OS.
> >
> > On DB2 UDB for Windows/Unix - how do views work? Are they reevaluated
> > everytime a select on a specific view is performed or are they maintained by
> > the DBMS and reevaluated during certain points in time, like everytime a
> > record is added, once a day or the first time the view is accessed?
> It's important to distinguish between preservation of the access plan
> (how to get to the data) and the precomputation of the view.
> DB2 for LUW has the concept of optimizer "profiles" where you can store
> in an XML document how certain snippets of SQL shall be computed.
> In DB2 V8 is is done only on an exception bases and the XML is typically
> provided by support when you open a PMR. The reason is that when this
> view in combined with other SQL this new context typically prefers a new
> access path.
>
> To pre-compute and preserve the resultset of a view DB2 for LUW supports
> "materialized query tables' (MQT). Take a look at the CREATE TABLE ..
> AS.. statement for details.
>
> Cheers
> Serge
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

sethwai@yahoo.com wrote:
> Serge,
>
> First, a heartfelt thanks for the plethora of information you
> provide.
>
> Can you provide more information about optimizer profiles? This is
> the first I've ever heard of them.
If you want documentation I re-iterate:
In DB2 V8 is is done only on an exception bases and the XML is typically
provided by support when you open a PMR.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab