Re: SQL Views worth it?

Hi Nathan

I am pretty surprised by your statement(s), so I just want to clarify what
you are saying:

You encountered a situation where a restore of a single library that
contained multiple views failed because the system could not resolve the
dependencies between the views; all of the required dependencies required
for the objects being restored including the views were defined in the
library being restored.

I understand that objects with cross library dependencies might have
issues, though recent releases have been pretty good about this in my
experience.

What release did you encounter this issue in and was there a problem report
to IBM ? What was IBM's response if it was reported ?

I guess if this is really the case then I would like to understand the
issue more deeply in order to take preventative measures.


On Sun, Aug 26, 2018 at 4:35 AM Nathan Andelin <nandelin@xxxxxxxxx> wrote:

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