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

Thanks for the explanations Vern.


Paul

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern
Hamberg
Sent: Tuesday, June 15, 2021 8:48 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: 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

--
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