DECLARE TRAN1-CSR CURSOR FOR
SELECT
A.ID
FROM MCS_REQUEST A
JOIN MCS_RQST_STATUS B
ON A.RQST_STUS_CD = B.RQST_STUS_CD
JOIN (
SELECT E.MCO_CTRT_NUM
FROM MCS_MCO_CONTRACT E
WHERE E.RGN_NUM IN (
SELECT F.RGN_NUM
FROM MCS_DATA_ACS_RULE F,
MCS_USER_DATA_ACS G
WHERE F.DATA_ACS_RULE_ID =G.DATA_ACS_RULE_ID
AND G.MMCS_USER_ID = :WS-DATA-FLTR-ID
AND F.MCO_CTRT_NUM IS NULL)
UNION
SELECT E.MCO_CTRT_NUM
FROM MCS_MCO_CONTRACT E
WHERE E.MCO_CTRT_NUM IN (
SELECT F.MCO_CTRT_NUM
FROM MCS_DATA_ACS_RULE F,
MCS_USER_DATA_ACS G
WHERE F.DATA_ACS_RULE_ID = G.DATA_ACS_RULE_ID
AND G.MMCS_USER_ID = :WS-DATA-FLTR-ID
AND F.RGN_NUM IS NULL )
) AS Z
ON A.MCO_CTRT_NUM = Z.MCO_CTRT_NUM
WHERE A.RQST_STUS_CD = :WS-TRANS-STUS
AND A.RQST_TYPE_CD = :WS-TRANS-TYPE
AND A.GHP_TRAN_CD = :WS-GHP-TRAN-CD
AND A.RQST_TYPE_CD NOT IN (6, 4, 15, 16, 17,
18, 23, 25)
AND A.RQST_DEL_CD = 'N'
AND A.RQST_CRT_DT BETWEEN
:WS-FROM-DATE AND :WS-THRU-DATE
AND (A.RQST_XPRN_DT >= CURRENT DATE
OR A.RQST_XPRN_DT IS NULL )
FETCH FIRST 1001 ROWS ONLY
WITH UR



This is executed in a COBOL program via a DB2 Stored procedure.

The OPEN CURSOR statemebt works fine but it is clocking in the Fetch statement and that too at the first fetch. The Fetch statement is as said below.

FETCH TRAN1-CSR
INTO :WS-ID



My uderstanding is that an OPEN cursor statement will materialise the CURSOR sql statements(This is when the indexes are used), does it not mean that a Fetch should only read these records from the buffer.

In this case the OPEN is successfull but it clocks in Fetch. Which means that the records have already been retrieved from the respective tables but the reading of the records from the materialised cursor is clocking.

Please help me out

On DB2 for z/OS (or OS/390) the result set is not materialized in a temporary tablespace (if it needs to be materialized) until the first fetch. I am not sure how it works in DB2 for LUW.

Thanks Marcus

That was a good piece of information which clears of my misconception that it could be related to somthing other than the SQL itself.

Hi Marcus

However I have the following problem too. I used a display statement after the OPEN CURSOR statement and the following occured.

When I changed the SQL it started to clock at the OPEN statement itself. Whereas otherwise it was clocking at the first FETCH statement. Why does DB2 behave differently. The SQL was changed to use a different index all the remaining predciates were kept the same.

Sometimes DB2 will do the I/O when you open the cursor, and sometimes it will defer the I/O until you do the first fetch. To be honest, I don't recall when the I/O happens (my answer above may be backwards) but I do know that sometimes it may be deferred until the first fetch. My recollection is that it depends on whether the answer set has to be materialized in a temporary table and then fetched, or whether the rows can be fetched in place from the base tables.

IIRC, if the statement has to be materialized fully before the first fetch - eg. an order by clause in the DECLARE CURSOR Stmt, the OPEN CURSOR materializes the query.

Otherwise, it is at FETCH time ..

If there is not grouping operation (like Order by, group by etc), generally the rows will be returened to the calling application as and when they are fetched ... Therefore, by starting the query execution at FETCH time - the response time is smaller .. Also, if you close the cursor soon enough, the cost of executing the entire query is saved ...

HTH

Sathyaram