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

Hi Charles,

I didn't include a lot of details because I was hoping the issue was something about running stored procedures in batch that was obvious to everybody except me.

Batch taking longer than interactive is contrary to what I have seen over the years. Generally, data intensive jobs run faster in batch and that has been true on our current machine. Everything else that runs in batch runs incredibly fast and that includes various other stored procedures.

In batch, the job starts off using between 20-30% of the CPU, but after a few minutes it goes down to practically 0%. When run interactively it stays around 25% until it finishes.

I will check out the link you provided.

Thank you,

Bobby

On 5/13/2025 13:07, Charles Wilt wrote:

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.