Re: SQL Views worth it?

We have deployed hundreds of SQL views. I might go as far as suggesting
that views are a fundamental tenet of database and application
modernization in that they externalize query processing and separate that
type of logic from application logic. With views, your program logic will
look cleaner and more streamlined.

I prefer creating views of views (what I like to call cascading views)
rather than coding elaborate CTEs. The separation makes cascading views
easier to read and understand than CTEs.

SQL views can and likely will cause traditional database restore commands
to fail because they can't keep track of database dependencies. The
workaround we came up with was to maintain a lengthy SQL script that
contains all the SQL CREATE VIEW statements for all views pertaining to a
library.

In order to restore database libraries, we first restore its physical
files. Then build its logical files. Then finally run the script that
rebuilds all its SQL views. In addition to having a script that creates all
your views in a proper sequence, you will likely need one that deletes all
your views in a proper sequence. The database relationships (dependencies)
can become rather gnarly.



On Fri, Aug 24, 2018 at 12:10 PM, Thomas Garvey <tgarvey@xxxxxxxxxx> wrote:

Hi,

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?

--

Best Regards,

Thomas Garvey


--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD