I'm developing a new application and trying to have the DB do as much of the
work as possible.
So, I'm incorporating SQL Views but finding the throughput underwhelming.
Granted that some of the views are based on other views (in keeping with the
attempt to have the DB do some work for me)
but it appears that every time a view is queried the views are rebuilt by
I read somewhere that Views can be considered somewhat like logical files,
but at least logicals
can be set to have immediate updates as underlying physicals change. Views
have no such attribute settings.
So, what's considered best practices for SQL Views?
Should I just rewrite these things as logicals?
I've read only some of the postings of this thread - some hints and
- most important is decoupling the application from the physical layer of
your database! As a conclusion of this, never use a table directly in the
application. Following this rule your application would be untouched by
changes in the physical layer (redesign!) as long as the appropriate views
could be provided (instead triggers where a great enhancement for this!!!).
@performance: most views will need appropriate indexes to perform well. in
real life: first create your needed views, without thinking about
performance; have a look to the index advises after the integration tests of
your application (I prefer database monitor over visual explain, debug
messages and index advisory file in db2 repository as a good compromise
between effort and result).
@DDS lf versus SQL views/indexes: DDS LF could be faster for read and slower
for update operations compared to appropriate view/index. Main benefit of
the SQL way is: you could create views based on views and DDS is only a
small subset of SQL views (group by, instead triggers ...)
@save restore (keep all objects, belonging to the database in one library
and it will work without thinking about.
@UDTF: be carefull with UDTFs, the query optimizer won't look inside the
UDTF and this could result in bad performance.
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact