RE: Which H-specs CTL-OPT do you use?

... and even worse when working with embedded SQL!
Each SQL Statement has to run at least one time through a FULL OPEN (full optimization) which is very time consuming. That's why SQL tries to keep the ODP (Open data path) open, so the next run only a PSEUDO OPEN (just refreshing the data in the ODP) is executed. A PSEUDO OPEN is 10-20 times faster than a FULL OPEN.
When running programs with the *NEW activation group, then at the program end everything that runs in the activation group is removed ... and our ODPs are gone.
Now you can imagine when working with a lot of SQL statements and for each SQL statement each time a FULL OPEN is executed you may imagine how slow your application get.
I had exactly this situation at a customer who had written a new application with embedded SQL, but each program run in a *NEW activation group, and the SQL was used in the same way as native I/O before.
The new application was much slower than the original one.
They were completely disappointed and shortly before throwing everything away.
Running Programs and Service-Programs (without INSERT, UPDATE, DELETE statements) in named activation groups and converting the "native I/O" thinking into a "Set based" SQL thinking, gave the application a huge performance boost and finally it was much faster than the original application.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"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)
"Learning is experience … everything else is only information!" (Albert Einstein)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jon Paris
Sent: Sunday, 10 August 2025 23:20
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Which H-specs CTL-OPT do you use?

If you want to discuss this, then this is probably not the right place. But just to explain my "horror" at *New as a default. It guarantees the AG is torn down. And AG creation is a relatively expensive process. Not quite as expensive as starting a job, but close, in part because it not only forces AG creation each time but also forces reactivation of any service programs, which again has a cost.

It is likely that if this were a "solution" for your problem, then you were committing one or more of ILE's 7 Deadly Sins. Probably the first one. You can find them on IBM's support sites now (I'm proud of that!) or read it here: https://authory.com/JonParisAndSusanGantner/The-Seven-Deadly-Sins-of-ILE-a0f4e66fe37a745c8bdf7cbc95f0b365f


Jon Paris
Jon.Paris@xxxxxxxxxxxxxx

On Aug 10, 2025, at 3:54 PM, Patrik Schindler <poc@xxxxxxxxxx> wrote:

Hello Jon,

Am 10.08.2025 um 18:37 schrieb Jon Paris <jon.paris@xxxxxxxxxxxxxx>:

*NEW as a default? That's pretty scary.

Why?

This is a lesson I've learned in my early days of OS/400 programming experience in probably early 2008. On e. g. Linux, if a process ends, nothing is retained. One can start the application a thousand times and it behaves the same each time.
On OS/400 with compiler defaults — dftactgrp(*yes) —, I ran into mysterious errors, happening because programs end but somehow retained state, while I assumed things being reset. Little did I know about LR back then. Reading a bit about AGs, and using a new AG which gets discarded at program end solved the issue for me thoroughly.

And it stuck.

Some context: The absolute majority of programs I develop and use are interactive 5250. They get started by hand from a menu and they end when I press F3. Most often, I open them just once per sign-on. No fancy calling of external programs in tight loops, etc.

Unusual? Maybe. Scary? No.

After learning some more about the multiple meanings of LR in ILE
context, I now use actgrp(*caller) on sub-programs being called from
such an interactive application, and set LR to on upon returning to
the caller to save some wait time. Keep in mind that my main machine
is a 150. Something most of the professionals in here won't even touch
with a pincer. Believe me, I take good care about efficient
application design. As a necessity. And as a personal challenge. :-)

There are still some open questions about AGs. What exactly is retained, the precise meaning of LR, the difference of setting it on at program entry, vs. setting it on at program end, etc. Most of them might be answered when I take my time to read the ILE Concepts manual for the n'th time. For n being about 5 or even 6.

Coming from Linux, AGs seem very alien. But I understand the motivation for their invention.

Does that help in understanding the scare? :-)

:wq! PoC


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

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