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

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