Re: Embedded SENSITIVE DYNAMIC SQL cursor question

I did not intend for the View to be used with RLA op-codes.

SQL multi-row fetch would still be the preferred method of loading the
subfile. It's just instead of having the complex SQL statement embedded in
the RPG program, you've encapsulated it in the view.

Charles

On Thu, Apr 21, 2022 at 4:45 PM Alan Cassidy <cfuture@xxxxxxxxxxx> wrote:

I did a quick test and created a simple SQL *View* real quick and then
used it in a DCL-F statement in RPG and it compiles as a file.

So RPG reads it as a file. Questions:

(1) I assume it can only be read sequentially?

(2) Can it be used "as if" it were an SQL cursor? (sort of) Open and
close on the file using RPG i/o to load a subfile?

--Alan



On 4/21/2022 2:31 PM, Alan Cassidy wrote:

Thanks a lot Charles! Yes, the idea is to load the the data from the
cursor into a load-all subfile. Or rather, to do it as a something
callable from more than a dozen different programs, to keep some of
the supporting infrastructure in place when moving from one program to
another. Each will be pulling its own subset from the data.

I should practice doing UDTFs and stored procedures again for my own
benefit, but I haven't done any UDTFs that I recollect, and the team
here might throw fits because it would be just me and one other guy
(of about 20) that could work with them. Not a heavy knowledge pool of
SQL around here. But the performance is killing productivity in that
way that economists call "opportunity costs", or "hidden costs"
because the snail's pace loading costs not only the time, but I think
it is what is causing record locks.

I've only used SQL cursors to read data straight to a subfile, but
closing and opening with ASENSITIVE would catch changes while
remembering the access paths and joins built the previous open. I
assume that is correct? Anyway, I'll proceed and incorporate your
suggestions. I think using a VIEW for the cursor may be the best
idea, in another step. I'm working on just putting together what
accounts a current user should see depending on the setup of assigned
accounts and backups to cover absences et. al. I'll see what returns
on investments this yields. (A boatload of joins are involved with
complex logic).

In fact I'll finish this particular change, and then consider how I'd
implement using a VIEW. That's the best approach anyway I think for
this situation. (The data to see changes all day).

Thanks again for your time and clarifications,

Alan



On 4/21/2022 9:54 AM, Charles Wilt wrote:

Generally speaking, both SENSITIVE and INSENSITIVE are detrimental to
performance, as you are forcing the DB down a single path.

ASENSITIVE lets the DB decide which to use.

Having said that, SENSITIVE should perform just fine as long as you have
the right indexes.

However, do you really need SENSITIVE SCROLL?

If you're opening the cursor and reading the entire set into a load-all
subfile, then ASENSITIVE NO SCROLL is sufficient. Simply close and
reopen
the cursor when the user refreshes.

If using a page-at-time subfile, then SENSITIVE SCOLL means when the
user
pages backwards, they might not get the same set of records they saw the
first time. If that's really what you want, fine.

In any UI other than twinax attached 5250 over a dial-up line, page-at-a
time or even expanding subfile makes little sense IMHO :)

Personally, I'd encapsulate the SQL statement in a stored procedure,
view
and/or UDTF rather than directly embedding it in an RPG program.
Many of
IBM's new services are view's generated by UDTFs ... there's a good
reason
for that.

Charles

On Thu, Apr 21, 2022 at 6:01 AM Alan Cassidy <cfuture@xxxxxxxxxxx>
wrote:

I've searched around but haven't found clarity to get my understanding
confirmed on this.

I'm working on getting to a solution to a horrendous performance
problem
in a set of programs that show different views to data that changes all
day that a team is working on with customers. It is important that what
they see is the most currently available status of the data.

I converted the primary file read that is input to populate the subfile
to an SQL cursor as the first step but it's not enough. So now I'm
doing
a cursor with somewhat complex join logic including a couple of common
table expressions, WITH clauses, and so on.

The same data set is used in various programs that they navigate.

I have changed the first program called from the menu from ACTGRP( *NEW
) to ACTGRP( XYZ ), and I've changed most of the first programs called
from there to ACTGRP( *CALLER ). I am grateful for the response I
got on
this forum (and/or the midrange forum) for helping me to decide on a
named activation group. And THANK YOU DAVID for creating these lists
and
managing them!

This is the idea I'm asking for confirmation for:

Now I'm proceeding with the idea of using a service program
procedure to
create a SENSITIVE DYNAMIC SCROLL CURSOR. That way it gets real-time
changes all day. So first time in, the SQL cursor gets created, and the
code reads it start to finish to populate the subfile. When the user
refreshes, the FETCH FIRST returns the "pointer" to the first row and
then FETCH NEXT reads thru the cursor and I can populate the subfile
from that.

That should reduce performance time, should it not?

Sorry if this is more words than necessary,

---Alan

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

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