Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    17

    Question Unanswered: difference between optimize fetch n rows

    hello everybody.
    Could you telle me what is the difference between
    EXEC SQL DECLARE CURSCLI CURSOR
    FOR SELECT ACLMEMO_SEQUENCE ,
    ACLMEMO_TITRE
    FROM TACLMEMO
    WHERE
    ACLMEMO_UG = :ACLMEMO-UG AND
    ACLMEMO_NUMCLI = :ACLMEMO-NUMCLI AND
    ACLMEMO_SEQUENCE >= :ACLMEMO-SEQUENCE
    OPTIMIZE 19 ROWS
    END-EXEC
    AND
    EXEC SQL DECLARE CURSCLI CURSOR
    FOR SELECT ACLMEMO_SEQUENCE ,
    ACLMEMO_TITRE
    FROM TACLMEMO
    WHERE
    ACLMEMO_UG = :ACLMEMO-UG AND
    ACLMEMO_NUMCLI = :ACLMEMO-NUMCLI AND
    ACLMEMO_SEQUENCE >= :ACLMEMO-SEQUENCE
    FETCH FIRST 19 ROWS
    OPTIMIZE 19 ROWS

    END-EXEC

    Why do i have an this error compilation
    ILLEGAL SYMBOL "OPTIMIZE". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ONLY
    ILLEGAL SYMBOL "19". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <END-OF-STATEMENT>
    lwhen i write this sql order
    EXEC SQL DECLARE CURSSIN CURSOR
    FOR SELECT ASNMEMO_SEQUENCE ,
    ANSMEMO_TITRE
    FROM TASNMEMO
    WHERE
    ASNMEMO_UG = :ASNMEMO-UG AND
    ASNMEMO_CIE = :ASNMEMO-CIE AND
    ASNMEMO_NUMSIN = :ASNMEMO-NUMSIN AND
    ASNMEMO_SEQUENCE >= :ASNMEMO-SEQUENCE
    FETCH FIRST 19 ROWS
    OPTIMIZE 19 ROWS
    END-EXEC
    (language cobol on zos and db2 v7)

    Merci
    Last edited by fab01; 05-12-06 at 05:22.

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    the correct syntax is: FETCH FIRST 19 ROWS ONLY.
    and OPTIMIZE FOR 19 ROWS

    As you omitted the keyword ONLY, you get the above error.
    ( I'm not sure whether the keyword FOR may be omitted )

    If you code FETCH FIRST 19 ROWS ONLY , you'll receive sqlcode=100 if you try to FETCH the 20th row.
    If you code OPTIMZE FOR 19 ROWS, DB2 only optimizes the access path for that number of FETCHES but doesn't limit the resulttable. If you try to fetch the 20th row, you'll get the data and a sqlcode=0.

  3. #3
    Join Date
    Mar 2006
    Posts
    17
    Thanks for your answer.
    The sql sentece works fine

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Generally, the OPTIMIZE FOR clause is used to shut off sequential prefetch. Sequential prefetch is when DB2 starts reading data or index pages from disk into the bufferpool, even before the DB2 Datamanager has asked for them (in anticipation that they will be requested shortly).

    If you want to shut off sequential prefetch, then using OPTIMIZE FOR 1 ROWS is recommended (even when you have FETCH FIRST 19 ROWS ONLY).

    However, if the query must materialize the entire answer set before it figures out which of the 19 rows to retrieve, then you might get better performance by using sequential prefetch. If you have an ORDER BY or the query requires a tablespace scan or a merge-scan join (see the Explain for that query) then I would not use the OPTIMIZE FOR clause because sequential prefetch may help you.

    I know that this may be counter-intuitive to many people (not using OPTIMIZE FOR 19 ROWS when you have FETCH FIRST 19 ROWS ONLY), but DB2 optimization and performance is not always straight forward.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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