Re: SQL Views worth it?

<Thomas>
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 the OS.

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?
</Thomas>

I've read only some of the postings of this thread - some hints and arguments:
- 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.

D*B