Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: FOR FETCH ONLY In a Subquery

    I have a very basic SQL statement that has in it a subselect. For some reason, this query is performing kinda slowly, and another developer thinks the sub-select needs a FOR FETCH ONLY clause.

    The problem is that I have put in the clause but it doesn't seem to work anywhere. Here is the original without the clause:

    CREATE PROCEDURE AIM.UPDATEDELIVERYSTATUS (IN decAIMRETRIEVALID DECIMAL(13,0),
    IN intENVMODULUS INTEGER,
    IN dateARCHIVERETRIEVALDATE DATE,
    IN decSTATUSID DECIMAL(13,0),
    IN dateDELIVERYDATE TIMESTAMP)
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    SPECIFIC AIM.UPDATEDELSTATUS
    LANGUAGE SQL
    RESULT SETS 0
    P1: BEGIN
    DECLARE strArchiveDate VARCHAR(31);
    --SET strArchiveDate = CAST(dateARCHIVERETRIEVALDATE AS VARCHAR(10)) || '-00.00.00.000000';
    UPDATE AIM.DELIVERY
    SET STATUSID = decSTATUSID,
    DELIVERYDATE = dateDELIVERYDATE
    WHERE ARCHIVERETRIEVALID IN
    (SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL
    WHERE
    DATE(ARCHIVERETRIEVALRECEIPT) BETWEEN dateArchiveRetrievalDate and DATE(ARCHIVERETRIEVALRECEIPT) + 1 DAY
    AND
    MOD(INTEGER(AIMRETRIEVALID), intENVMODULUS) = decAIMRETRIEVALID);
    END P1

    You would normally place the FOR FETCH after the complete SELECT, but I get an error if I put it inside or outside of the subselect.

    Does anyone have any suggestions?
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A FOR FETCH ONLY would usually just affect the type of locking and/or the blocking (defines whether it is an ambigious cursor if used for an answer set returned to a remote requester). If should not affect performance of a stored procedure running on the server that does not return rows to a client.

    Check the access path with an explain.
    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
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Apparently, we are getting a fairly high level of lock escalations in the database being caused by this stored procedure.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, which problem are you trying to solve: lock escalation or update speed?

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Little from column a and a little from column b. The dba here isn't too keen on adding an index to the table - it already has five indexes. He's talking about breaking up the query into separate UOW's, but i mentioned regardless of how you break it up, you are still doing a WHERE on ARCHIVERETRIEVALRECEIPT and if there's no index, it will still do a table scan...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ..........
    Last edited by n_i; 11-15-04 at 17:33.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think your DBA is right - adding indexes won't help a bit because you're not using column values in your WHERE clause but the results of functions.

    This change may help a little:
    Code:
    WHERE EXISTS 
    (SELECT A.ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL A
    WHERE A.ARCHIVERETRIEVALID = ARCHIVERETRIEVALID AND
    A.ARCHIVERETRIEVALRECEIPT > TIMESTAMP(dateArchiveRetrievalDate,'00.00.00') 
    AND
    MOD(INTEGER(A.AIMRETRIEVALID), intENVMODULUS) = decAIMRETRIEVALID)
    Last edited by n_i; 11-15-04 at 17:32.

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi ansonee,

    For fetch only in subselect work only with db2 v8.1 and higher. What version do you use? Do you use v7 or lower? If so, for fetch only is not supported in earlyer versions.

    Hope this helps,
    Grofaty

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by grofaty
    For fetch only in subselect work only with db2 v8.1 and higher.
    Actually, "for fetch only" is a clause in the SELECT statement, not in a subselect. At least that's what the documentation says:
    Code:
    Select-statement
    
    >>-+-----------------------------------+-- fullselect --*--------->
       |       .-,-----------------------. |
       |       V                         | |
       '-WITH---- common-table-expression -+-'
    
    >--+------------------+--*--+---------------------+--*---------->
       +- read-only-clause -+     '- optimize-for-clause -'
       '- update-clause ----'
    
    >--+------------------+--*-------------------------------------><
       '-   isolation-clause  -'

Posting Permissions

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