× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2018

Re: SQL Views worth it?

Hi Nathan

thanks for the longer explanation; I might try and recreate this as most
people would expect a library to "just restore" when all the objects
required are in the same library.

Hopefully, you logged a call with IBM as I don't think that is working as
intended; perhaps "as designed" but maybe the design needs tweaking.

On Tue, Aug 28, 2018 at 7:43 AM Nathan Andelin <nandelin@xxxxxxxxx> wrote:

Evan,

I'm not aware of any IBM reference that documents this condition. Maybe
"failure" is too harsh of a term. Restore procedures will complete ... but
only after time consuming recovery from Job Log errors that identify the
SQL views that could not be restored. In our case that may be dozens of
views.

I am surprised that this issue is not more well known. I think that you
could test this yourself by creating nested views, then trying to save and
restore them. Maybe it's only manifested after nesting to a certain level
or more. It was just one of those things that we noticed as we were
designing and deploying new databases.

I define nesting (or cascading) as meaning the output of one view being
used as input for another. A chain of queries. For example, the result set
generated by view A may be queried by view B, which may be queried by view
C, which may be queried by view D, which may be queried by view E.

I believe this is one way for developers to have more control over what
occurs in the query processor. You can use your knowledge about the
database and its indexes to improve the optimization of the query
processor.

Nathan.



On Mon, Aug 27, 2018 at 11:53 AM, Evan Harris <auctionitis@xxxxxxxxx>
wrote:

HI Nathan

you say that Nested views as not able to be restored; do you have an IBM
reference to support that or explain why that might be ?

Do you have a simple example of how I might re-create the restore issue
so
I test the scenario ?

I'd really like to understand this restore issue so being able to
re-create
the problem would be a great start.


On Tue, Aug 28, 2018 at 5:00 AM Nathan Andelin <nandelin@xxxxxxxxx>
wrote:

On Sun, Aug 26, 2018 at 3:33 PM, Evan Harris <auctionitis@xxxxxxxxx>
wrote:

Hi Nathan
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.


Hi Evan,

We're on 7.3 and our PTFs are pretty-much up to date.

The issue is that Restore commands are not able to resolve SQL view
dependencies when they are nested, even if they are defined within the
same
library. Nested (or cascading) views are very elegant and really slick
from
a design and functional perspective. They're a lot more readable and
understandable than creating a single SQL view from long and protracted
query logic that you might code into a program or put into a single
view.

We finally settled on our maintaining a script to rebuild nested views
after physical files have been restored.
--
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



--

Regards
Evan Harris
--
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

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




As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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 [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.