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

Or memory pool? Batch more restricted than Interactive?


Jon Paris
Jon.Paris@xxxxxxxxxxxxxx

On May 13, 2025, at 1:38 PM, Rob Berendt <robertowenberendt@xxxxxxxxx> wrote:

The batch vs interactive could also be the interactive using some sort of
already open data path.

Might make a difference between a newly signed on session vs an established
session.

On Tue, May 13, 2025 at 1:26 PM Bobby Adams <bobby_adams@xxxxxxx> wrote:

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.

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

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