Tweet
Hello,
I have a weird behaviour on a stored procedure running in QUSRWRK subsystem.
When I run the above stored procedure from ACS(Run SQL Script) it takes 21 minutes to execute.
[ 31/12/2020, 18:49:16 ] Run Selected...
CALL spgetQtyOnHand ('020', '2020-01-01' , '2020-12-31' , '0') ;
Statement ran successfully, with warnings (1,262,608 ms = 21.04 min)
Submitting to QBATCH exactly the same stored within a 5250 session in an RPGLE programs it take about 1 minute to execute.
SBMJOB CMD(CALL PGM(TEST_PRF))
PGM TEST_PRF
h dftactgrp(*no)
*inlr = *on;
EXEC SQL call spGetQtyOnHand('020','2020-01-01','2020-12-31','0');
Runnig the same stored procedure from 5250 STRSQL command it usually takes 3 to 4 minutes , but some times it randomly takes 20 minutes.
STRSQL
CALL bms71l_dat.spgetGiacenzeValorizzate ('020', '2020-01-01' , '2020-12-31' , '0')
Running the stored procedure from ACS via QCMDEXC through the RPGLE program takes a lot of time (22 minutes).
CALL QCMDEXC('CALL PGM(TEST_PRF)', 18);
Running from a QSH session (it's a BCI job type in wrkactjob) on my PC it takes 1 minute:
qsh -c "db2 "call bms71l_dat.spGetQtyonHand('020','2020-01-01','2020-12-31','0') ""
I am running my tests on a dedicated system Ibm I on V7.3 , no other users and7or processes running .
The stored Procedure is as simple as
select q.* , x.*
FROM QtyOnHand q
LEFT JOIN TABLE (MG001_GETCOST ( CompanyCode , Skucode, 'CM' , '2020-01-01', '2020-12-31') ) X ON 1=1;
The SQL function MG001_GETCOST is a SQLRPGLE service program which returns a table result by passing relevant parameters.
The join to the SQL function is performed about 10.000 times, but the strange behaviour is that it takes 1 minutes to run in QBATCH and 21 minutes from ACS Run Sql Script.
I attach an extract of the Service Program Main Logic.
* CONSTANTS
d CALL_OPEN C CONST(-1)
d CALL_FETCH C CONST(0)
d CALL_CLOSE C CONST(1)
select ;
when calltype = CALL_OPEN ;
dealloc(n) pdsCostoMg;
when calltype = CALL_FETCH;
pDsCostoMg = MG001_getCostoDtl (p_cazi:
p_cart:
p_tpcos:
p_fmdate:
p_todate);
when calltype = CALL_CLOSE;
return ;
endsl ;
I have lots of enquiry 5250 programs that use the service Program and they are really fast.
The Service Program was optimised as much as I could.
I did try to create a new subsystem and run QZDASOINIT jobs from there, but the execution time is 21 minutes, no benefit.
At this stage I think it's something related to QZDASOINIT PJ Job that uses Interact CPW and not Batch CPW because it's slow even when I run from ACS via QCMDEXC, but I don't know how to move it to Batch CPW in order to perform one more test.
I need to run the stored procedure from a Report running in Microsoft Reporting Services so I am forced to use QZDASOINIT.
The above report takes 21 minutes to execute like SQL Script in ACS.
Any help is really appreciated.
Happy New Year to all of you.
Giovanni
I have a weird behaviour on a stored procedure running in QUSRWRK subsystem.
When I run the above stored procedure from ACS(Run SQL Script) it takes 21 minutes to execute.
[ 31/12/2020, 18:49:16 ] Run Selected...
CALL spgetQtyOnHand ('020', '2020-01-01' , '2020-12-31' , '0') ;
Statement ran successfully, with warnings (1,262,608 ms = 21.04 min)
Submitting to QBATCH exactly the same stored within a 5250 session in an RPGLE programs it take about 1 minute to execute.
SBMJOB CMD(CALL PGM(TEST_PRF))
PGM TEST_PRF
h dftactgrp(*no)
*inlr = *on;
EXEC SQL call spGetQtyOnHand('020','2020-01-01','2020-12-31','0');
Runnig the same stored procedure from 5250 STRSQL command it usually takes 3 to 4 minutes , but some times it randomly takes 20 minutes.
STRSQL
CALL bms71l_dat.spgetGiacenzeValorizzate ('020', '2020-01-01' , '2020-12-31' , '0')
Running the stored procedure from ACS via QCMDEXC through the RPGLE program takes a lot of time (22 minutes).
CALL QCMDEXC('CALL PGM(TEST_PRF)', 18);
Running from a QSH session (it's a BCI job type in wrkactjob) on my PC it takes 1 minute:
qsh -c "db2 "call bms71l_dat.spGetQtyonHand('020','2020-01-01','2020-12-31','0') ""
I am running my tests on a dedicated system Ibm I on V7.3 , no other users and7or processes running .
The stored Procedure is as simple as
select q.* , x.*
FROM QtyOnHand q
LEFT JOIN TABLE (MG001_GETCOST ( CompanyCode , Skucode, 'CM' , '2020-01-01', '2020-12-31') ) X ON 1=1;
The SQL function MG001_GETCOST is a SQLRPGLE service program which returns a table result by passing relevant parameters.
The join to the SQL function is performed about 10.000 times, but the strange behaviour is that it takes 1 minutes to run in QBATCH and 21 minutes from ACS Run Sql Script.
I attach an extract of the Service Program Main Logic.
* CONSTANTS
d CALL_OPEN C CONST(-1)
d CALL_FETCH C CONST(0)
d CALL_CLOSE C CONST(1)
select ;
when calltype = CALL_OPEN ;
dealloc(n) pdsCostoMg;
when calltype = CALL_FETCH;
pDsCostoMg = MG001_getCostoDtl (p_cazi:
p_cart:
p_tpcos:
p_fmdate:
p_todate);
when calltype = CALL_CLOSE;
return ;
endsl ;
I have lots of enquiry 5250 programs that use the service Program and they are really fast.
The Service Program was optimised as much as I could.
I did try to create a new subsystem and run QZDASOINIT jobs from there, but the execution time is 21 minutes, no benefit.
At this stage I think it's something related to QZDASOINIT PJ Job that uses Interact CPW and not Batch CPW because it's slow even when I run from ACS via QCMDEXC, but I don't know how to move it to Batch CPW in order to perform one more test.
I need to run the stored procedure from a Report running in Microsoft Reporting Services so I am forced to use QZDASOINIT.
The above report takes 21 minutes to execute like SQL Script in ACS.
Any help is really appreciated.
Happy New Year to all of you.
Giovanni