Re: SQL stored procedure takes longer to run in batch than interactively.

Referencing:

+++++ *MACHINE
1118 *BASE
10 2 QBATCH QSYS
10 *SHRPOOL10
10 2 QCMN QSYS
3 2 QPGMR QSYS
15 *SPOOL
2272 *INTERACT

It’s highly likely that with acitvity levels that low, there will be quite a bit of memory thrashing. I suggest you turn on the OS ability to manage the storage pool activity levels. It’s easy and can be done without any interruption of the system and it takes effect immediately.

See system values: QDYNPTYADJ and QDYNPTYSCD. Set them both to ‘1’.

WRKSYSSTS paging and faulting should reduce as well after that.

Caveat: The system tends to prioritize interactive and if that becomes an issue we can show you how to mitigate that.

The activity level for *SPOOL should be set to about the number of actual writers that are started. Those tend to process almost immediately and then shut down as the spool is dealt with.


--
Jim Oberholtzer
Agile Technology Architects

On May 15, 2025, at 12:33 PM, Bobby Adams <bobby_adams@xxxxxxx> wrote:

Yes. Ours is below. I do not know how or why the values were set, but if you have questions I will ask.

+++++ *MACHINE
1118 *BASE
10 2 QBATCH QSYS
10 *SHRPOOL10
10 2 QCMN QSYS
3 2 QPGMR QSYS
15 *SPOOL
2272 *INTERACT

On 5/15/2025 13:15, Glenn Gundermann wrote:

I'm confused. You have Max Active set to 10?

Our values look like this:

+++++ *MACHINE
2400 *BASE
5600 *INTERACT
15 *SPOOL

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Cell: (416) 317-3144

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