Announcement

Collapse
No announcement yet.

QZDASOINIT Stored Procedure Running Slow

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • QZDASOINIT Stored Procedure Running Slow

    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

Working...
X