By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,612 Members | 1,632 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,612 IT Pros & Developers. It's quick & easy.

DBMS maintained views

P: n/a

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
Feb 12 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Feb 12 '06 #2

P: n/a
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


Feb 13 '06 #3

P: n/a
se*****@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
Feb 13 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.