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
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
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.
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,...)
