|
View previous topic :: View next topic
|
| Author |
Message |
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
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 |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| So what is the problem? What happened when you tried? |
|
| Back to top |
|
|
Thirumurgann
New User
Joined: 13 Feb 2008
Posts: 36
Location: India
|
|
|
|
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 |
|
|
Dsingh29
Active User
Joined: 16 Dec 2008
Posts: 132
Location: IBM
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
