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

Thank you for that clarification.

Bobby

On 5/15/2025 09:19, Jim Oberholtzer wrote:

The number of active jobs in a memory pool is way different than the limits on the subsystem or the job queue entries. Be careful not to conflate them, bad things can happen.

The memory pool only controls how many jobs can have memory and processor assigned at once, the activity level. Not how many jobs can run in that pool.

The subsystem often time has a limit of how many jobs the subsystem will allow to be active, that’s different than the job queue limits.

The job queue limit is how many jobs from that job queue can be released to the subsystem at once, many times being 1 or several, or in the case of QINTER no limit.

Again keep all three of these related functions separate or you could wind up with a mess.


Jim Oberholtzer
Agile Technology Architects

On May 15, 2025, at 7:28 AM, Bobby Adams <bobby_adams@xxxxxxx> wrote:

Oops, sorry for the vagueness.

On WRKSYSSTS, the fourth column from the left. 'Max Active', the maximum number of threads that can use the processor concurrently.

When the job was running in batch, we could see (via WRKJOB option 20) 9 threads. Only 5 were active at a time, the other 4 were in a TIMW status. Interactively, all 9 threads were running. We changed the 'Max Active' value from 5 to 10. That helped that job and we didn't notice any ill effects for any of the other jobs.

Regards,

Bobby

On 5/14/2025 17:26, Don Brown via MIDRANGE-L wrote:
'Max Active' ?

Are you referring to the MAXACT on the job queue ? I do not see how that
could make any difference.

Sorry, was just interested in how you solved your performance problem.

Thanks
Don

Don Brown

Senior Consultant
[1]OneTeam IT Pty Ltd
P: 1300 088 400

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Bobby Adams
Sent: Wednesday, 14 May 2025 11:57 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: SQL stored procedure takes longer to run in batch than
interactively.

We discovered the 'Max Active' setting for QBATCH was too low. We tried a
few settings before settling on one. Now the batch job runs much
quicker. We understand that when it comes to performance no individual
setting change exists in a vacuum so we will continue to review the
different settings and evaluate as we go.

Thank you for all your responses.

Regards,

Bobby

On 5/13/2025 11:45, Bobby Adams wrote:
> Hi All,
>
> We have an SQL stored procedure that deletes records from a table and
> then inserts records into the table using a view. The view uses an
> SQL function that calls an RPG procedure in a service program. If I
> run the the stored procedure from a 5250 session interactively, it
> runs in about 35 minutes. It should insert just over 7 million
> records, but after 4 hours of running in batch it had only inserted
> 3,300 records. What would account for such a difference in behavior
> between batch and interactive?
>
> I am using "CALL PGM(DP/UPDCSTCACH)" to run it interactively or submit
> it to batch. We are baffled.
>
> Thanks for your help.
>
> Regards,
>
> Bobby Adams
>
>
--
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: [2]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
[3]https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content
filtering.
[4]https://www.mailguard.com.au

References

Visible links
1. https://www.oneteamit.com.au/
2. https://lists.midrange.com/mailman/listinfo/midrange-l
3. https://archive.midrange.com/midrange-l.
4. https://www.mailguard.com.au/

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