IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Handling multiple cursors in cobol - db2 program


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Thirumurgann

New User


Joined: 13 Feb 2008
Posts: 36
Location: India

PostPosted: Thu Sep 15, 2011 8:02 am
Reply with quote

Hi friends,

I have two queries. Q1 with 2 coloumns say C1, C2 and Q2 with 4 coloumns C3,C4,C5,C6.

The result of Query 1 will be given as input to Query 2 to fetch another 4 coloumns. Then finaly i need a 6 coloumns C1...C6 as a result set.

I thinked of using cursor for Query1 since it would fetch multiple ROWS.
Thats is fine.

Then when i pass C1 and C2 to Query2 it fetches multiple rows for each C1 and C2 combination. So again i need to go for cursor for Query2.

Kindly suggest me some ideas to handle this.

Below are my Queries:

Query1:

SELECT C1,
C2
FROM DFBP44I.TACE2_ENTITY A
JOIN DFBP44I.TACE2_ENTITY_ROLE B ON (B.UPN = A.UPN AND B.ROLE_CD IN ('IL','AS') AND B.STATUS IN ('03','09','04') AND B.BUSINESS_UNIT IN ('BW','FACT','FAR','FM'))
WHERE A.ENTITY_STATUS_CD IN ('03','09')
AND (A.EMPLOYEE_IND IS NULL
OR A.EMPLOYEE_IND = 'Y')

Query2:

WITH A1 AS (SELECT PARENT_UPN AS UPN FROM DFBP44I.TACE2_ENTITY_ROLE WHERE UPN = C1 AND MEMBER_OF_UPN = C2 AND STATUS IN ('03','09','04'))
SELECT C3,
C4,
CASE
WHEN '01' = (SELECT '01' FROM DFBP44I.TACE2_CNTRCT_ROLE X JOIN DFBP44I.TACE2_CONTRACT Y ON (X.ROLE_ID = A.ROLE_ID AND X.CONTRACT_ID = Y.CONTRACT_ID AND Y.CONTRACT_CD = '01')) THEN 'Y'
ELSE 'N'
END C5,
C6
FROM A1
JOIN DFBP44I.TACE2_ENTITY_ROLE A ON (A.UPN = A1.UPN AND A.STATUS IN ('03','09','04') AND A.BUSINESS_UNIT IN ('BW','FACT','FAR','FM'))
JOIN DFBP44I.TACE2_ENT_PD_ASSOC B ON (B.ROLE_ID = A.ROLE_ID AND B.OWNER_IND = 'Y')
JOIN DFBP44I.TACE2_PRODUCER C ON (C.PRODUCER_ID = B.PRODUCER_ID AND C.PRODUCER_CD_STAT IN ('03','09','04'))
JOIN DFBP44I.TACE2_CNTRCT_AUTH D ON (D.PRODUCER_ID = C.PRODUCER_ID AND D.STATUS = '03')
JOIN DFBP44I.TACE2_PRODUCT_TYPE F ON (F.PRODUCT_CD = D.PRODUCT_CD) WITH UR

Thanks,
Thiru
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Sep 15, 2011 10:18 am
Reply with quote

So what is the problem? What happened when you tried?
Back to top
View user's profile Send private message
Thirumurgann

New User


Joined: 13 Feb 2008
Posts: 36
Location: India

PostPosted: Wed Sep 21, 2011 10:25 am
Reply with quote

Hi Guys,

let me explain the scenario more clearly:

There are two cursors

First cursor is AUTHCUR2

Code:

    EXEC SQL                                               
       DECLARE AUTHCUR2 CURSOR FOR                         
       SELECT  DISTINCT A.UPN,                             
               B.MEMBER_OF_UPN                             
       FROM    ACE2_ENTITY A                               
       JOIN    ACE2_ENTITY_ROLE B                    ON     
               (B.UPN = A.UPN                        AND   
               B.ROLE_CD IN ('IL','AS')              AND   
               B.STATUS  IN ('03','09','04')         AND   
               B.BUSINESS_UNIT IN ('BW','FACT','FAR','FM'))
       WHERE   A.ENTITY_STATUS_CD IN ('03','09')     AND   
               (A.EMPLOYEE_IND IS NULL               OR     
               A.EMPLOYEE_IND = 'Y')                       
      ORDER BY A.UPN,                                       
               B.MEMBER_OF_UPN                             
      FETCH FIRST 5 ROWS ONLY                               
    END-EXEC.                                               


The second cursor is AUTHQ2:

Code:

   EXEC SQL                                                     
      DECLARE AUTHQ2 CURSOR FOR                                 
      WITH A1 AS (SELECT PARENT_UPN AS UPN                     
      FROM   ACE2_ENTITY_ROLE                                   
      WHERE  UPN = :ENT-UPN                        AND         
             MEMBER_OF_UPN = :ENR-MEMBER-OF-UPN    AND         
             STATUS IN ('03','09','04'))                       
      SELECT DISTINCT D.STATE_CD,                               
             C.BUSINESS_UNIT,                                   
             CASE                                               
                 WHEN '01' = (SELECT DISTINCT '01'             
      FROM   ACE2_CNTRCT_ROLE X                                 
      JOIN   ACE2_CONTRACT Y                       ON           
             (X.ROLE_ID = A.ROLE_ID                AND         
              X.CONTRACT_ID = Y.CONTRACT_ID        AND         
              Y.CONTRACT_CD = '01'))  THEN 'Y'                 
             ELSE 'N'                                           
             END BROKER_IND,                                   
             VARCHAR(D.PRODUCT_CD) || '~' || F.PRODUCT_DESC     
      FROM   A1                                         
      JOIN ACE2_ENTITY_ROLE A                      ON   
          (A.UPN = A1.UPN                          AND   
           A.STATUS IN ('03','09','04')            AND   
           A.BUSINESS_UNIT IN ('BW','FACT','FAR','FM')) 
      JOIN ACE2_ENT_PD_ASSOC B                     ON   
          (B.ROLE_ID = A.ROLE_ID                   AND   
           B.OWNER_IND = 'Y')                           
      JOIN ACE2_PRODUCER C                         ON   
          (C.PRODUCER_ID = B.PRODUCER_ID           AND   
           C.PRODUCER_CD_STAT IN ('03','09','04'))       
      JOIN ACE2_CNTRCT_AUTH D                      ON   
          (D.PRODUCER_ID = C.PRODUCER_ID           AND   
           D.STATUS = '03')                             
      JOIN ACE2_PRODUCT_TYPE F                     ON   
          (F.PRODUCT_CD = D.PRODUCT_CD)                 
   END-EXEC.                                             


Here ENT-UPN,ENR-MEMBER-OF-UPN are the host variables caught from Query1 and need to be fed in to AUTHQ2.

For Each values of ENT-UPN,ENR-MEMBER-OF-UPN The CURSOR2 - AUTHQ2 need to executed and process of OPEN, FETCH, CLOSE happens.

This impacts much on the program execution time. Could you please suggest some ideas on how to reduce the exec time. I dont have any clu on the execution time. wherther the query caues problem or multiple cursors. Sugegstions welcome

Thanks,
Thiru
Back to top
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Wed Sep 21, 2011 11:03 am
Reply with quote

One way, can be, load your first cursor variables (which you are using in second cursor) in an internal table and close the first cursor. Then use those values in your second cursor one-by-one.

This approach can reduce DB2 overhead.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Sep 21, 2011 7:17 pm
Reply with quote

Hello,

Quote:
This approach can reduce DB2 overhead.
Why do you believe this?

Quote:
This impacts much on the program execution time.
You need to determine how many rows have to be "touched" to return the needed values - not the only the number of rows returned.

How much time does it take?

Suggest you work with your dba to determine how much work this is causing db2 to actually do.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Sep 21, 2011 8:46 pm
Reply with quote

there is no way of helping you without some idea of the available indexes, primary keys and some basic statistics (#rows , cardinality of key columns,...)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Replace each space in cobol string wi... COBOL Programming 3
No new posts Using API Gateway from CICS program CICS 0
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts COBOL -Linkage Section-Case Sensitive COBOL Programming 1
No new posts COBOL ZOS Web Enablement Toolkit HTTP... COBOL Programming 0
Search our Forums:

Back to Top