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

[Bobby Adams <bobby_adams@xxxxxxx>]

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/