srini_igsi
Beginner
Joined: 12 May 2006
Posts: 41
Topics: 16
Location: Mumbai
|
 Posted: Mon Mar 16, 2009 1:37 pm Post subject: DB2 query optimization |
 |
|
The following query extracts all the CORPS for all the releases released till date at CORP level. The db2 host variable DB2-HOST-V-RLSE-DATE is populated with highest/latest release date from some other db2 table so that this wil be used to extract all the corps less than that release date. Just before the MAP is thrown to the user, we alwasys save the release dt, release id and the corp for the 1st and 12th rown on the MAP so that these values will be used for the forward (PF8) operation.
My requirement is that I have to extract all the releases for a specific CORP enterd by the user at the terminal on DESC order by RELEASE DT.
The updated query should also support forward (PF8) operation.
Could somebody please help to update the following query to retrieve the records matching the criteria said above.
| Code: |
SELECT CORP
,C.RELEASE_ID
,C.RELEASE_DATE
,C.RT_IND
,C.PROD_CODE
,VALUE (SUM (SHARES_TOT), 0) AS SHARES_TOT
,VALUE (SUM (SHARES_WTHHLD), 0) AS SHARES_WTHHLD
,SUM (MULTIPLY_ALT (VALUE (VEST_DT_FMV , 0),
VALUE (SHARES_TOT , 0)))
AS GROSS_TOT
,VALUE (SUM_TOT_SHRS , 0) AS COVER_SHARES
FROM SOSRSTK_REL_CNTL C
INNER JOIN SOSRSTK_REL_ELECT E
ON (( C.CORP > :DB2-HOST-V-CORP-12
AND C.RELEASE_DATE = :DB2-HOST-V-RLSE-DATE
AND C.RELEASE_ID = E.RELEASE_ID )
OR ( C.CORP = :DB2-HOST-V-CORP-12
AND C.RELEASE_ID > :DB2-HOST-V-RLSE-ID-12
AND C.RELEASE_DATE = :DB2-HOST-V-RLSE-DATE
AND C.RELEASE_ID = E.RELEASE_ID ))
INNER JOIN SOSRSTK_REL_STATUS S
ON S.RELEASE_ID = C.RELEASE_ID
AND S.REL_TYPE IN ('1', '2', '3', '4')
AND S.REL_STATUS IN ('C', 'O', 'P')
GROUP BY CORP
,C.RELEASE_DATE
,C.RELEASE_ID
,C.RT_IND
,C.PROD_CODE
,SUM_TOT_SHRS
ORDER BY C.CORP ASC
,C.RELEASE_DATE ASC
,C.RELEASE_ID ASC
OPTIMIZE FOR 12 ROWS
|
Thanks in advance.
_________________
Thanks,
-Srini |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
