Re: . RPG and SQL view/index (Timothy Feldmeier)

[Charles Wilt <charles.wilt@xxxxxxxxx>]

There's no such thing as a "table view"

In SQL DDL, there are TABLES, VIEWS, and INDEXES.
In DDS, there are Physical files and Logicals files (ok and DDM).  With
logical files subset into keyed/non-keyed and dynamic/non-dynamic
Select/Omit.

CREATE VIEW results in a OS level *FILE object that has neither a data
space nor an index space.  It's just a copy of the SQL select statement
used when the view was created; thus as the table changes there's nothing
to maintain.

If you do this
CREATE VIEW MYVIEW as
  select * from mytable where fld1='Y';

select * from MYVIEW;

What the Db actually runs is this
select * from (select * from mytable where fld1='Y');

If you specified MYVIEW on an RPG f-spec, the Db would materialize the data
when the file is opened by RPG.  However, there are no keys, so you'd need
to either read sequentially, or I suppose you could position by RRN.  I've
really never seen that done.

It does raise an interesting question about two seperate RPG jobs (or even
two programs in the same job) that open the view via f-specs.  Does the DB
materialize the data once and share it or does it materialize it multiple
times?

Charles

On Thu, Jul 8, 2021 at 11:37 AM Timothy Feldmeier <
Timothy.Feldmeier@xxxxxxxx> wrote:

> Vern, you are referring to a Logical View.  I am referring to a Table
> View.  They are a 1 time build.   An Table Index would always be up to date.
> This is the difference between DDS and DDL.   Create View vs   Create
> Index.
>
> The information transmitted is intended only for the person or entity to
> which it is addressed and may contain proprietary, business-confidential
> and/or privileged material. If you are not the intended recipient of this
> message you are hereby notified that any use, review, retransmission,
> dissemination, distribution, reproduction or any action taken in reliance
> upon this message is prohibited. If you received this in error, please
> contact the sender and delete the material from any computer.
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com