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

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.

Thanks for your answer.
The sql sentece works fine

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.