Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Handling multiple cursors in cobol - db2 program

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Handling multiple cursors in cobol - db2 program
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: 1722
Location: UK

PostPosted: Thu Sep 15, 2011 10:18 am    Post subject:
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    Post subject: Reply to: Handling multiple cursors in cobol - db2 program
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    Post subject: Reply to: Handling multiple cursors in cobol - db2 program
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

Site Director


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

PostPosted: Wed Sep 21, 2011 7:17 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Sep 21, 2011 8:46 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Executing OO COBOL program invoking J... Virendra Shambharkar COBOL Programming 2 Tue Jan 10, 2017 6:37 pm
No new posts OO COBOL compile error Virendra Shambharkar COBOL Programming 3 Tue Jan 10, 2017 6:05 pm
No new posts Need Suggestion on COBOL program vickey_dw COBOL Programming 5 Thu Jan 05, 2017 10:55 pm
No new posts I can not compile my program PL1 V3.R... Miguel Fernandez PL/I & Assembler 13 Tue Dec 06, 2016 8:30 pm
No new posts IMS BMP program causes 878 system abend Artemk IMS DB/DC 7 Tue Nov 22, 2016 8:26 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us