RE: using sql views

Dave,
True, but the heavily used (index suggestion) may also
map to the more used business data.
In addition, the performance of a view depends mainly
on proper index design

Because a view does not have a "key" or "index", I think
use of CHAIN, SETLL or any RPG io dependent on a key
will be off the table. Maybe all you need is a read loop,
so I *expect* that works on a view.

Usually, a CHAIN could be replaced by a SELECT statement
with the key list translated into the WHERE clause.

%found and %eof are replaced by sqlcod and/or sqlstate,
and there is an sql equivalent for SETLL, but like
all new technology, you want to find a "small" example
program to begin with, because there is a lot of conceptual
re-mapping to do when developing a good sql solution.

However, as I have seen mentioned on this list, you may find
yourself, after more sql experience, "thinking in sets",
and really enjoying the benefits.

For example, I regularly read all required records into a
multiple occurrence data struct with one sql statement.
This works well where I need a few hundred or so records.

Hope this helps.





-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Dave
Sent: Friday, May 25, 2012 12:40 PM
To: RPG programming on the IBM i / System i
Subject: Re: using sql views

Thanks Gary, what kind of help would iNav give? Surely it is not capable suggesting which views might be needed as the index advisor does with indexes?! Also, I suppose a view would ideally need a test library as well?
As for using it, for those programs using a primary file there would be little or no change, but how is a CHAIN normally replaced? A Select with fetch first row only? What replaces the %found indicator. There is no equivalent for SETLL, I think.



2012/5/25 Gary Thompson <gthompson@xxxxxxxxxxx>

Dave,

We use views where there is a common/frequently-repeated need for the
same or nearly the same data. Especially if the data is the result of
a set of joins or requires knowledge of business rules to get the
correct data.

One example might be a join to a fiscal period table to get the period
attribute(s) needed to classify the result.

Bottom line, yes, this can eliminate programming and testing; the art
is in identifying candidates. You can get help from System i
Navigator (do a web search on visual explain) and there are IBM
iSeries manuals you will find on the IBM i Information Center for your
particular OS level that will help.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Dave
Sent: Friday, May 25, 2012 4:56 AM
To: RPG programming on the IBM i / System i
Subject: using sql views

Hi,

I was just contemplating the possible uses of views. We don't have any
on our system and not many programs that read files with embedded sql.
I have my own that I create then delete in my sql scripts in order to
query the data base. But there are so many batch applications that
have the same format : a program that does an extraction followed by a
program that treats the extracted lines. Would using views eliminate a
lot of the programming and testing in this kind of application?

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



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

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