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

I don't know what a Logical View is, not a Table View, nor a Table Index - I don't think any of those exist in IBM's terminology - as Charles says, there are the 3 SQL object types and basically 2 DDS types.

SQL tables are implemented as PFs with a special set of attributes - the benefit of our object-based OS.
SQL views and indexes are implemented as LFs with their special set of attributes.

I figured pictures are more informative -

I ran the following SQL DDL statements -

CREATE OR REPLACE VIEW vhamberg.cuscdtnomn AS SELECT * FROM qiws.qcustcdt WHERE STATE <> 'MN';
CREATE INDEX vhamberg.cuscdtbyst on qiws.qcustcdt (state);

These are the resulting objects as seen in PDM -

      CUSCDTBYST  *FILE       LF
      CUSCDTNOMN  *FILE       LF

Here is the file description of the index - note the SQL file type - an LF created with CRTLF does not have that attribute - the access path names the column, STATE -

Data Base File Attributes
  Externally described file . . . . . . . . . : Yes
  SQL file type . . . . . . . . . . . . . . . : INDEX
  File level identifier . . . . . . . . . . . : 1210708194429
  Creation date . . . . . . . . . . . . . . . : 07/08/21

Here is the same for the view - again, note the SQL file type - the SELECT statement is in the file description and follows -

Data Base File Attributes
  Externally described file . . . . . . . . . : Yes
  SQL file type . . . . . . . . . . . . . . . : VIEW
  File level identifier . . . . . . . . . . . : 1210708194414
  Creation date . . . . . . . . . . . . . . . : 07/08/21

SQL view create statement . . . . . . . . . :
  --  category:  Data Definition Language (DDL)
                                               --  description: Crea
  te or Replace View
                    CREATE VIEW CUSCDTNOMN AS SELECT * FROM QIWS.QCU
  STCDT WHERE STATE <> 'MN'

Nothing of these is a one-time, static thing - every time the object is used, the data at the time is presented.

So even in DDS, what is a one-time build? PFs and LFs are always presented as what their content is at the time of use.

So what am I missing? I just don't know anything that is one-time - help me understand - seriously!

Regards
Vern

On 7/8/2021 4:40 PM, Charles Wilt wrote:

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.

-snip-

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