If n is less than 1000, it controls the number of rows to be fetched per batch and the same number of rows is fetched for all cursors. If n is greater than or equal to 1000, it sets the size of the prefetch buffer for each cursor. All cursors will have the same buffer size but the number of rows prefetched will depend on the overall size of the row returned by the query for each cursor.
When PREFETCH=n is used with Microsoft SQL Server, AUTOFETCH is also used for read only cursors. Cursors which are not read only are forced to be keyset cursors and can be used for positioned updates. PREFETCH=n is only supported with DB2, Oracle and Microsoft SQL Server.
|Used at compile time:
|Behavior at run time:
See Scope - OpenESQL SQL Compiler Directive Options for more information.
Much of the functionality provided by PREFETCH is now incorporated into the functionality of the BEHAVIOR SQL compiler directive option. As a result, PREFETCH is likely to be deprecated in a future release.