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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Not much info to go on...
Normally, the difference between "interactive" and "batch" SQL is related
to *FIRSTIO vs *ALLIO.
But since this is a stored proc, I don't think that applies.

What's the system CPU usage to start and compared to running the procedure
iterative vs batch?

My WAG...as shipped, QBATCH and QINTER have differ classes assigned giving
their jobs a different priority and timeslice.
In particular, interactive jobs have 2000ms timeslice assigned, which made
sense 30 years ago but is really long now-a-days.
I forget what QBATCH ships with (500 maybe?), but IIRC it's smaller than it
should be now-a-days.

If you really want to know what's going on, you'll want to look at the wait
accounting for each job.
https://www.ibm.com/docs/en/i/7.4.0?topic=problem-basics-i-wait-accounting

HTH,
Charles

Charles



On Tue, May 13, 2025 at 9:45 AM Bobby Adams <bobby_adams@xxxxxxx> 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: 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.