Re: SQL Views worth it?

Gents,

You are alle right (imho) when i comes to views - I deploy all our
applications with “nice “ names on top of old nonsense DDS files - and
hiding the legacy that way. However, nested views can in some cases confuse
the optimizer - making a wall between logics in subsequent queries that
have serious performance issues. And when we find these, just report them
back to IBM and Scott Forstie. When I run into these issues I “simply”
rewrite a new view for now - that performs well. But in the end of the day
I think IBM need to work on nested optimization. ... what do you guys think?
lør. 25. aug. 2018 kl. 18.35 skrev Nathan Andelin <nandelin@xxxxxxxxx>:

Jim,

Your assertion about us building a "nightmare" was not very thoughtful.
After some rumination on my part, it occurred to me that you're vested in
traditional save and restore functions and therefore defensive of them.

It is true that we have some SQL views that cross library boundaries. The
reason for that in our case is because we develop broadly scoped business
applications that include several packages that can be licensed separately.
One customer may license our student information system, while another may
license our student transportation system, for example.

As is often the case with broadly scoped business systems, "person" data is
required in multiple packages. A "person" may be a "teacher" in on package
and an "employee" in another, for example. So any database tables that are
shared between multiple packages - we place them in a library that is
shared by all. Perhaps now you can understand the business justification
for having a "common" library, shared across multiple packages?

But that is not the only reason that traditional save and restore
operations fail. They also fail when you define cascading SQL views in a
single library. The restore operation is not smart enough to figure out the
database relationships and dependencies, so it can't figure out how to
restore the views in their proper sequence.

So we came up with a workaround for our save and restore requirements. I
think that shops that (like us) adopt SQL views should be aware of the
need.

With regards,

Nathan.





On Fri, Aug 24, 2018 at 3:07 PM, Jim Oberholtzer <
midrangel@xxxxxxxxxxxxxxxxx> wrote:

If you have that much trouble restoring then you must be A) on an older
Version/Release, and B) not keeping the views, index objects, and logical
files together in one library. You must be "cross attaching" them and
therefore building a nightmare.

The problem is not with traditional backup/restore functions but rather

how

you've chosen to implement parts of the database.


--
Jim Oberholtzer
Agile Technology Architects

--
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