Defines the number of cursor rows to buffer.
Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.
Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set only for individual connections or the PUBLIC role. You must shut down and restart the database server for the change to take effect.
When an application opens a cursor, SAP Sybase IQ creates a FIFO (first-in, first-out) buffer to hold the data rows generated by the query. CURSOR_WINDOW_ROWS defines how many rows can be put in the buffer. If the cursor is opened in any mode other than NO SCROLL, SAP Sybase IQ allows for backward scrolling for up to the total number of rows allowed in the buffer before it must restart the query. This is not true for NO SCROLL cursors, as they do not allow backward scrolling.
For example, with the default value for this option, the buffer initially holds rows 1 through 200 of the query result set. If you fetch the first 300 rows, the buffer holds rows 101 through 300. You can scroll backward or forward within that buffer with very little overhead cost. If you scroll before row 101, SAP Sybase IQ restarts that query until the required row is back in the buffer. This can be an expensive operation to perform, so your application should avoid it where possible. An alternative is to increase the value for CURSOR_WINDOW_ROWS to accommodate a larger possible scrolling area; however, the default setting of 200 is sufficient for most applications.