Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: Clocking when doing a Fetch

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    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.

  4. #4
    Join Date
    Apr 2004
    Posts
    6
    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.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Last edited by sathyaram_s; 03-13-06 at 22:36.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •