run STRQMQRY in batch job for a long time

[hahahawind]

I'm facing an issue that running QMQRY in interacitve online call is very fast, but run QMQRY in batch job is very slow.

I worte a qmqry to export a report. This qmqry has mulitple with statement, something like this:

with t1 as (
select * from xxx where xxx
),
with t2 as (
select * from xxx where xxx
)
..
select * from t1, t2, ....

there are arounds 10 "with" statements, finally join these temporary tables and export a table only.

I have a CL Program that is submitted to batch. This CL program runs this qmqry by using STRQMQRY QMQRY(XXXX) OUTPUT(*OUTFILE) OUTFILE(QTEMP/XXXX).

But system keeps running this qmqry for a long time (over 30 minutes) and I don't see there is any progress in this job.

However, if I online call to run this CL (that includes the STRQMQRY command), it completed in 10 seconds. I don't think the sql logic is complicated as I can run it in 10 seconds if I login workstation and online call this progrm/qmqry.

Of course, if the qmqry output only contains one record, it can be done very fast in batch. But if I run 10,000 records, it use over 30 minutes and it does not finish yet. BUT BUT I online call to run the same 10,000 recods. It is ended in 10 seconds. Why qmqry in batch takes so much time. If there is any setting/parameter in system not correct.

Thanks a lot if anyone can help.

[hahahawind]

I found it's not related to qmqry or online call or batch job. Actually, even I online call, it's very slow in as400.

In my SQL, there are 9 "with" statement. if there are many "with" statement to create temporary table, AS400 execute this SQL very slow.

If I changed all "with" statement to create table. Then I use RUNSQLSTM, then the SQL can be completed in very fast.

create table qtemp.t1 as (
....
) with data;

create table qtemp.t2 as (
....
) with data;
...

create table qtemp/report as (
select * from t1, t2
) with data;


So my question is multiple "with" statement will lead to low performance?