RE: A data management head-scratcher: CHAIN taking 30+ seconds

I just saw this issue and it reminded me. Maybe it's applicable, in case. Sorry if this idea was already shared.
Years back, users were having issues with a certain program that tracked shipments for callers to call the customer contacts. Running the menu options that opened this program were taking up to a minute to load, so much so that the previous programmer had set up a routine that ran when they exited the program (F3) because they were typing so fast they'd often hit the exit key accidentally, and there you go, another minute to get back in. Sometimes had to manually "unlock" the locks on records when two users were blocking each other loading.
So all I did was change the file opens to input instead of input/update, and added another file path to the file for when they actually did the update.

On 04/16/2026 2:35 PM EDT Birgitta Hauser <hauser@xxxxxxxxxxxxxxx> wrote:


Just one question:
1. On what do you do the CHAIN on a logical file or an SQL index?
2. If it is a logical file does it perhaps include the DYNSLT Keyword?

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: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Reeve
Sent: Thursday, 16 April 2026 11:35
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: A data management head-scratcher: CHAIN taking 30+ seconds

Greetings all--

A customer has documented long response times--over 30 seconds--when doing a read-only CHAIN to an indexed (one key, 36 characters) file, "XXX", with *IMMED maintenance and *AFTER journaling. The program waits and the transaction is abandoned by the requestor. *ALL commitment control is not active. No other apps use this file. The problem is chronic and intermittent. The system is well-resourced with CPU, memory, and disk.

The program in question--ZED--is a web service acting as a wrapper for the business app (providing price quotes) and about 50 instances are running concurrently. Tens of thousands of transactions per day flow through this app. File XXX has about 5 million records and about 200,000 deleted records. The job log shows SQL7917, "Access plan not updated", when the program initiates.

Here's the scenario:


1. At IPL, 50 instances of program ZED are initiated.
2. Each instance opens file XXX as a shared input file. Important fact:
at this point, XXX is not available for “exclusive use”.
3. An instance of ZED leaves its wait state and begins execution.
4. The program executes to the CHAIN to XXX.
5. The database engine—the query optimizer, to be specific—recognizes a
need to rebuild the access path for XXX. We are sure the file is XXX but
SQL7917 doesn't have any details.
6. The query optimizer updates the access path in memory but—and here’s
where there’s trouble in River City—it can’t update the access path
object. According to the second-level text on the error message, this
update requires exclusive access to XXX.
7. Why no exclusive access? Because XXX is open the other 49 copies of
ZED. It's cousin to the database "deadly embrace" design flaw.


My hypothesis is this: the query optimizer can't get exclusive use of the file and can't update the file object with the updated (meaning the 200K deleted entries are dropped) access path. The resulting wait for the CHAIN to complete is caused by the database reading through those 200K+ deleted entries in XXX's index. Because the first instance of ZED ties up the file, the other 49 instances have the same issue and the access path remains dirty.

Having deleted entries in the access path doesn't make sense; my understanding is that the system rebalances the tree dynamically. But the purge program deleting unneeded records runs while ZED is active and, once again, no exclusive use of XXX is possible. So, all 50 instances of ZED are stuck with a dirty index.

My suggestions to the customer:

1. Use SQL instead of a CHAIN (not sure this will work)
2. Create an LF over XXX and use it in ZED.
3. Change the schedule for XXX's purging to be part of the monthly IPL,
when the system is down and we can RGZPFM the file; I'm not sure if a
reorg-while-active will work.
4. Open and close the file within the RPG program; this may provide the
window for the first executing instance to let the access path update
complete.
5. Spawn one instance, wait a minute (or longer, thereby allowing this
first instance to do let the access path update complete), then spawn the
other 49.

What have I missed?

Thank you for any insight/feedback/instruction!

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


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