Re: [DDS] [V4] Select/omit woes

Just to respond to a couple things in this thread.

First, although an SQL view IS listed in WRKOBJ as an LF, hence, perhaps, some confusion, it is basically a container for a SELECT statement - that statement is run each time the view is used. Now with the plan cache and optimization, that performance should be good. Yes, a view does not have an index, but more on that now.

A view, of course, can have JOINs and GROUP BYs, and those will benefit from indexes that support those "connections" between the "dials", or the tables in the table list.

That could address Patrik's concerns - at least, IBM wants us to work toward that and use set-based processing. And given a SELECT over an appropriate view and include an ORDER BY as needed, performance should be good using FETCH of a CURSOR. Now on his 150 it might not be possible to get the complete update to the most recent improvements that IBM are adding all the time.

An interesting turn is that indexes now CAN be much like DDS-based LFs - they CAN have a column list, and they CAN have a WHERE clause, which serves the select-omit needs. Combined with the ordering, which can include not just column names but can include expressions, these give a lot of capability. AND they can be specified in an F-spec - I know, I'm now turning us around in several circles, right? SQL indexes do have some performance advantages over classic LFs, especially page size, IIRC.

So enough said, I think. HTH
Vern

On 6/14/2021 10:22 AM, Paul Therrien wrote:

Glad it worked out.


Paul

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Patrik
Schindler
Sent: Monday, June 14, 2021 11:06 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [DDS] [V4] Select/omit woes

Hello Paul,

Am 14.06.2021 um 16:13 schrieb Therrien, Paul via MIDRANGE-L
<midrange-l@xxxxxxxxxxxxxxxxxx>:

I figured you would do the create view in the same way you would do a

CRTLF.

No, complete misunderstanding. Sorry for being ambiguous, obviously.

But, I guess you cannot get the index you need then.?

I don't need an index. What I have is completely sufficient.

But, couldn't you then use embedded SQL over the view and sort the view as

you need?

No, I want to get rid of embedded SQL because it's too slow for interactive
5250 things on my 150.

(I am now bumping up against the limits of my SQL knpwledge)

In the files I was linking to is a working SQL definition, and the same
functionality cast into DDS. Here are the corrected versions, without *NULL,
and thus also working now.

https://leela.pocnet.net/~poc/OSMRPTMLF-corrected.pdf

https://leela.pocnet.net/~poc/OSMRPTOLF-corrected.pdf

And indeed, launching the program and seeing the result is very fast!
Compare that to multiple seconds wait time for the SQL version. Again, on a
150. ;-) Very efficient!

Anyway, you pointed me exactly to the point hidden by a blind spot of mine.
So, thanks!

:wq! PoC

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-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