Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2014
    Posts
    3

    Unanswered: Bad open cursor performance

    Hi all,

    This is my first post on this forum.
    I'm french so forgive my low english expression.
    I need your help about a db2 cobol cursor that's take too long time to open.

    Here is the cursor :

    EXEC SQL DECLARE CURS01 CURSOR WITH HOLD FOR SELECT
    ..
    ..

    FROM
    VPAEAFF1 AFF
    , VPAEHAF1 HAF
    , VPAPENG1 ENG
    , VPAPECR1 ECR
    WHERE
    HAF.ID_TECH_DPR = AFF.ID_TECH_DPR
    AND HAF.NO_ENG = AFF.NO_ENG
    AND HAF.NO_ECRG = AFF.NO_ECRG
    AND HAF.NO_AFEC = AFF.NO_AFEC
    AND AFF.CO_STUT_AFEC = HAF.CO_STUT_AFEC
    AND AFF.ID_TECH_DPR = ENG.ID_TECH_DPR
    AND AFF.NO_ENG = ENG.NO_ENG
    AND ENG.ID_TECH_DPR = ECR.ID_TECH_DPR
    AND ENG.NO_ENG = ECR.NO_ENG
    AND AFF.NO_ECRG = ECR.NO_ECRG
    AND ENG.ID_TECH_DPR = : DCLTPGEAFF.ID-TECH-DPR
    AND AFF.MT_NET_FISC_AFEC IS NOT NULL
    AND NOT (AFF.MT_NET_FISC_AFEC = 0
    AND AFF.CO_TYPE_AFEC ='RBT')
    AND ECR.CO_ROLE_BNEF_ECRG = 'AL'
    AND AFF.DT_PAI_AFEC >= : WS-DT-DEB-PRDE-LF
    AND AFF.DT_PAI_AFEC <= : WS-DT-FIN-PRDE-LF
    AND AFF.CO_TYPE_AFEC IN ('ORD', 'RBT')
    AND AFF.CO_STUT_AFEC NOT IN ('RJT', 'ANN')
    UNION
    SELECT
    ...
    ...
    FROM
    VPAEAFF1 AFF
    , VPAEHAF1 HAF
    , VPAPENG1 ENG
    , VPAPECR1 ECR
    WHERE
    HAF.ID_TECH_DPR = AFF.ID_TECH_DPR
    AND HAF.NO_ENG = AFF.NO_ENG
    AND HAF.NO_ECRG = AFF.NO_ECRG
    AND HAF.NO_AFEC = AFF.NO_AFEC
    AND AFF.CO_STUT_AFEC = HAF.CO_STUT_AFEC
    AND AFF.ID_TECH_DPR = ENG.ID_TECH_DPR
    AND AFF.NO_ENG = ENG.NO_ENG
    AND ENG.ID_TECH_DPR = ECR.ID_TECH_DPR
    AND ENG.NO_ENG = ECR.NO_ENG
    AND AFF.NO_ECRG = ECR.NO_ECRG
    AND ENG.ID_TECH_DPR = : DCLTPGEAFF.ID-TECH-DPR
    AND AFF.MT_NET_FISC_AFEC IS NOT NULL
    AND NOT (AFF.MT_NET_FISC_AFEC = 0
    AND AFF.CO_TYPE_AFEC ='RBT')
    AND ECR.CO_ROLE_BNEF_ECRG = 'TC'
    AND AFF.CO_TYPE_AFEC = 'RBT'
    AND AFF.DT_PAI_AFEC >= : WS-DT-DEB-PRDE-LF
    AND AFF.DT_PAI_AFEC <= : WS-DT-FIN-PRDE-LF
    AND AFF.CO_STUT_AFEC NOT IN ('RJT', 'ANN')
    ORDER BY 1, 2, 3, 4
    FOR FETCH ONLY
    END-EXEC

    _____________________________________________
    PK of VPAEAFF1 :
    XPAEAFFK 1 ID_TECH_DPR
    XPAEAFFK 2 NO_ENG
    XPAEAFFK 3 NO_ECRG
    XPAEAFFK 4 NO_AFEC

    Index of VPAEAFF1 :
    XPAEAFF1 1 ID_FLXF
    _____________________________________________

    PK of VPAEHAF1 :

    XPAEHAFK 1 ID_TECH_DPR
    XPAEHAFK 2 NO_ENG
    XPAEHAFK 3 NO_ECRG
    XPAEHAFK 4 NO_AFEC
    XPAEHAFK 5 NO_STUT_AFEC

    Index of VPAEHAF1 :
    XPAEHAF1 1 ID_ORIG_OPER
    _____________________________________________

    PK of VPAPENG1 :

    XPAPENGK 1 ID_TECH_DPR
    XPAPENGK 2 NO_ENG

    No index

    _____________________________________________

    PK of VPAPECR1 :


    XPAPECRK 1 ID_TECH_DPR
    XPAPECRK 2 NO_ENG
    XPAPECRK 3 NO_ECRG

    Indexes of VPAPECR1 :

    XPAPECR1 1 CO_TYPE_ANNL
    XPAPECR1 2 ID_TECH_DPR_ANNL
    XPAPECR1 3 NO_ENG_ANNL
    XPAPECR1 4 NO_ECRG_ANNL

    XPAPECR2 1 ID_TECH_DPR
    XPAPECR2 2 NO_PDUR_RCUV

    XPAPECR3 1 ID_TECH_PPT

    XPAPECR4 1 ID_BNEF_ECRG
    XPAPECR4 2 CO_ROLE_BNEF_ECRG
    XPAPECR4 3 CO_TYPE_BNEF_ECRG


    PROGRAM STMT TYPE NO. NO. CALLS ELAPSED % CPU % PAGES
    -------- ---------- ---- ------ ----- --------------- --------------- ------
    + PPGEAJ36 S OPEN 1 2752 135K 30:49.02946 63 01:21.53492 59 26454K
    + PPGEAJ36 S FETCH 1 2947 648K 00:32.97258 1 00:04.80930 3 0
    + PPGEAJ36 S CLOSE 1 3596 135K 00:10.58027 0 00:01.32384 1 0

    As you can see the Cobol batch call this curseur many times (around 135 000 for this test).

    I can give you more details if you think about one solution.
    Do you think that an index on AFF.DT_PAI_AFEC will be useful ?

    Any suggestion is welcomed
    Thank you for reading

    A. LOTFI
    Last edited by alf06; 11-05-14 at 09:15.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Get and study the execution plan ( = access plan ) for your cursor statement...

    You did not give your db2-server operating-system, version+fixpack etc.

    If your db2-server runs on linux/unix/windows, you can copy the query into a file (replacing any host variables by parameter markers) and use db2exfmt or dynexpln or equivalent tools to view and study the access-plan.

    The db2 on-line knowledge-centre (also available in your language) gives enough information about these tools, and how to optimize SQL statements.

    You can also use db2advis to check if it suggests any alternate solutions, such as other indexes, or runstats etc.

  3. #3
    Join Date
    Nov 2014
    Posts
    3
    OK,

    My DB2 is on Z/OS V10.1
    So the tools you have talked are not reachable for me.
    By the way in the explain i saw the Union is by far the most expensive.
    I m currently working on splitting my unique cursor and make two. (i hope u understood what i mean)
    For that i need to make some change in my cobol batch program.
    Tests are in progress
    Last edited by alf06; 11-03-14 at 11:25.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Why have a union? I think this should give you the same results. Keep in mind this is untested and written in about 2 minutes, but should give you the idea.

    Code:
    FROM
    VPAEAFF1 AFF
    , VPAEHAF1 HAF
    , VPAPENG1 ENG
    , VPAPECR1 ECR
    WHERE
    HAF.ID_TECH_DPR = AFF.ID_TECH_DPR
    AND HAF.NO_ENG = AFF.NO_ENG
    AND HAF.NO_ECRG = AFF.NO_ECRG
    AND HAF.NO_AFEC = AFF.NO_AFEC
    AND AFF.CO_STUT_AFEC = HAF.CO_STUT_AFEC
    AND AFF.ID_TECH_DPR = ENG.ID_TECH_DPR
    AND AFF.NO_ENG = ENG.NO_ENG
    AND ENG.ID_TECH_DPR = ECR.ID_TECH_DPR
    AND ENG.NO_ENG = ECR.NO_ENG
    AND AFF.NO_ECRG = ECR.NO_ECRG
    AND ENG.ID_TECH_DPR = CLTPGEAFF.ID-TECH-DPR
    AND AFF.MT_NET_FISC_AFEC IS NOT NULL
    AND NOT (AFF.MT_NET_FISC_AFEC = 0
    AND AFF.CO_TYPE_AFEC ='RBT')
    AND ECR.CO_ROLE_BNEF_ECRG IN ('AL','TC')
    AND AFF.CO_TYPE_AFEC IN ('ORD', 'RBT')
    AND NOT (ECR.CO_ROLE_BNEF_ECRG = 'TC'
    AND AFF.CO_TYPE_AFEC = 'ORD')
    AND AFF.DT_PAI_AFEC >= : WS-DT-DEB-PRDE-LF
    AND AFF.DT_PAI_AFEC <= : WS-DT-FIN-PRDE-LF
    AND AFF.CO_STUT_AFEC NOT IN ('RJT', 'ANN')
    ORDER BY 1, 2, 3, 4
    FOR FETCH ONLY
    END-EXEC
    Also, I wouldn't expect this to be super fast to begin with. An index on the AFF.DT_PAI_AFEC can help. I don't know what kind of date range you are looking for here 1 month, 2 years... One item we have done in the past was to use a UDF that filled in the date range between 2 dates(we normally used 1 week and 1 month ranges), then our SQL was able to match on more than just the date column in an index.
    Dave

  5. #5
    Join Date
    Nov 2014
    Posts
    3
    Thanks Dave for the query, it make sense, i m gone test this.
    After all our tests we think that the solution is to avoid to open so many times the cursor, and add a new inner join on ID_TECH_DPR that have to been processed.
    This means we need a change on our program code.

    Many thanks to all

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •