RE: SQL Views worth it? -- RPG400-L

Have you by chance removed the cached access plan, so it must build a new
one? You can do that in Navigator but I'm not near a machine to look up the
exact steps but it's in there right near the index advisor.

--
Jim Oberholtzer
Agile Technology Architects

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Thomas
Garvey
Sent: Monday, August 27, 2018 2:51 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQL Views worth it?

Hi,

These nesting, or cascading views are exactly my circumstance, and the
apparent slow response time are due to using them (I believe), and prompted
my original post.
As advised, I have reviewed the Index Advisor and created indexes that
appear there but haven't seen any appreciable improvement.
As for the backup and restore issues that have arisen in this discussion, I
haven't gotten that far in system testing.
I'm anticipating issues though because the physical files underlying these
views are in different libraries, and there is nothing I can do about that.

I appreciate the advice and broad discussion that has resulted from my
original post.

Best Regards,

Thomas Garvey

On 8/27/2018 2:42 PM, Nathan Andelin 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