What are the disadvantages of Clause "OPTIMIZE FOR N ROWS" in SQL query?

Thanks

The main benefit is when you open a cursor and only fetch some of the rows (before getting to end of cursor with SQLCODE = 100). Using OPTIMIZE FOR 1 ROWS will turn off sequential prefetch (where DB2 retrieves rows from the tablespace and puts them in the bufferpool even before they are requested, in anticipation that they will be needed soon). But if all the rows in the cursor are not fetched, then it would be a big waste to invoke sequential prefetch. Sequential prefetch usually only occurs when DB2 determines that a large number of rows will be included in the answer set (such as with with a table scan).

Thanks Marcus.

How far can we believe on EXPLAIN tool for Db2 sql queries?

I don't know what you are asking. Generally the explain is accurate if done on the same system as the query is run. But you could get different results if your literals are different in the query than in the explain, or if parameter markers are used in the explain and literals are used in the query.

Also, the DB2 optimizer only estimates how many rows will be returned, and could be wrong based on the quality of statistics captured with runstats. But the access path is likely to be the same (index, table scan, etc).