Re: Can SQL DDL views have the same capability as a DDS Logical view?

Hi Tim

This is another "it depends" kind of thing. DDS logical files (not views, actually) are objects that contain records that have some kind of data for each record that is readable using it. DDL views are "nothing" more than a SELECT statement encased in a file object. How data is returned in an LF is a part of the LF, with a view, that is worked out by the SQL optimizer (might be faster using plan cache). Putting it another way, LFs have access paths, views have access plans. The latter can change, based on the run-time environment.

When you say "...used in the program...", do you mean record-level access or embedded SQL? SQL indexes can be used for record-level access, and those indexes now can have not just the index tree but also the values of columns you specify, similar to LFs with key fields. But if you mean embedded SQL, yes, indexes cannot be specified in a SELECT statement.

A view can have the same capability as an LF, whether keyed or not. Use and ORDER BY or not. And you much more flexibility than you do with LFs. You might need to create indexes (or use existing keyed LFs) to support performance of a view, to support JOINs and WHEREs and ORDER BYs. Those indexes are not specified explicitly in RPG or in the SELECT statement (that will actually be slower), but they are used by the SQL optimizer to get the best performance.

Uffda! Long answer to a short question! Hope it makes some sense!

Cheers
Vern

On 5/16/2023 7:26 AM, Timothy Feldmeier wrote:

I want to stop creating DDS logical views-but, I want the DDL view to have the same capability as the logical view. I don't want to create an Index, that can't directly be used in the program.







Thank You

Tim Feldmeier
Programmer Analyst, Applications Development
1431 Opus Place, Suite 530 | Downers Grove, IL 60515
Email: timothy.feldmeier@xxxxxxxx<mailto:timothy.feldmeier@xxxxxxxx>
Phone: (331) 233-7921

[Email Logo]

[Graphical user interface, website Description automatically generated]<https://www.shiproadrunnerfreight.com/newsweek-recognizes-roadrunner-as-one-of-americas-most-trustworthy-companies-2022/>

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.