Re: [EXT] Re: Should I be using cursors?

What's the problem with open ODPs?

I had a mass update program with lots of nested calls, and SQL's scattered
through them.
(only about 4 tables). As I watched the job perform, I saw via wrkactjob,
open files, the ODP count would climb up to a
high number, like 300 or so, then reclaim them back to 4, and start
climbing again...
I wanted to find a way, via compiling with common activation groups, or
something, but I couldn't get anything to work.

A PSEUDO OPEN may be 10-20 times faster than a FULL OPEN.

when I am looking at a job, in Display Open Files... How do I KNOW which
is a "FULL OPEN" vs. a "PSEUDO OPEN"?????





On Thu, Jul 27, 2017 at 10:40 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

What's the problem with open ODPs?

SQL works in this way! Because a FULL OPEN and expecially creating/opening
the ODP is very time consuming, tries to keep ODPs open as long as
possible.
With sub-sequent execution the ODP can be reused, i.e. instead of a FULL
OPEN only a PSEUDO OPEN is used, i.e. only the data in the temporary
objects
of the ODP must be refreshed.
A PSEUDO OPEN may be 10-20 times faster than a FULL OPEN.

BTW the ODP is closed after the first execution, it is not kept opened
before the end of the second execution.

Each statement gets its own ODP, and some statements for examples INSERTs
and/or UPDATEs based on SELECT statements even more than one!
Each SQL statement has to run at least once through the FULL OPEN.
Contrary to native I/O where the programmer defines the physical/logical
file to be used. This physical/logical files are used even though it was a
bad choice and thousands of row are iterated.
There is no optimization at all for native I/O.
But native I/O reads only two rows at time (the current and the next one),
while SQL can return exactly the requested data in a blocked form.
AFAIK for update or delete SQL also reads row by row.

The compile option CLOSQLCSR is set per default to *ENDACTGRP, i.e. the
ODPs
are kept opened until the end of the activation group.
With the compile option CLOSQLCSR set to *ENDMOD the ODP is deleted if the
module is left and the next executions a FULL OPEN must be performed.
I your program runs in the *NEW activation group , the ODPs are deleted as
soon as the program ends (because the activation group will be reclaimed).

... and the number of ODPs in a job is NOT infinite. The maximum can be set
with an QAQQINI option.
If the maximum is reached the oldest and the longest not executed ODPs are
deleted and replaced with the new ones.

Using a single insert, update or delete statement may be (much) faster than
reading row by row, but you need a good strategy (commitment control) if
something goes wrong and you have to reset.
What if you want to update thousands of records an suddenly your update
statement runs on an error?
Do you know which records are update and which ones not?
Also rolling back thousands of rows and retrying the statement again may
not
be a good decision in your daily job.
That's why I use quite often cursors to manipulate my data, because I know
exactly what is executed and will be able to set multiple commits.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Englander, Douglas
Sent: Donnerstag, 27. Juli 2017 16:40
To: midrange-l@xxxxxxxxxxxx
Subject: RE: [EXT] Re: Should I be using cursors?


Danny,

I have seen this issue also, where ODPs are left open when embedded in an
ILERPG program and that program ends. Here is what we found reduces the
ODPs
upon close, but did not entirely eliminate them:

1. Use the CLOSQLCSR = *ENDMOD clause in your first EXEC SQL line where you
have the SET OPTION list, also COMMIT = *NONE if you are not using
Commitment Control.

2. If you have the case of one program ["A"] calling another ["B"] and the
B
program has the embedded SQL, then make program B have ACTGRP(*CALLER) in
its CTL-OPT statement. Make program A have ACTGRP(*NEW) in its CTL-OPT
statement. Using this method, all of the ODPs created by A and B are
cleaned
up when A ends. You also would need to use ACTGRP(*CALLER) in all the
programs that A calls so their ODPs get cleaned up too, when A ends.

This reduces the open ODPs upon close. But the ODPs that seem to stay open
are ones where we are using SQL to calculate a sum of a field and store it
in a variable, i.e.:


Select sum(taxes) from payroll_detail where department = 5;


Hope this helps.

Doug

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
http://amzn.to/2dEadiD

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD