Scrollable cursors

How scrollable cursors can be supported on different databases.

Scrollable cursors can be used to go forward and backward in an SQL query result set:
DEFINE cust_rec RECORD LIKE customer.*
   FOR SELECT * FROM customer
FETCH NEXT sc INTO cust_rec.*
FETCH LAST sc INTO cust_rec.*
FETCH FIRST sc INTO cust_rec.*

This is a useful feature to implement record set navigation in applications. Scrollable cursors are typically implemented in the database server. But not all database servers support scrollable cursors.

When scrollable cursors are not supported by the target database server, the database driver will emulate it with temporary files.

The temporary files are create in a temporary directory, that can be defined with the DBTEMP environment variable. If DBTEMP is not defined, the default temporary directory dependents from the platform used.

It is recommended that you avoid scrollable cursor usage if the target database does not support this feature:

With emulated scrollable cursors, when scrolling to the last row, all rows will be fetched into the temporary file. This can generate a lot of network traffic and can produce a large temporary file if the result-set contains a lot of rows. Additionally, programs are dependent on the file system resource allocated to the OS user (ulimit).

Some databases do not support to use large objects data types (TEXT/BYTE) with scrollable cursors: The OPEN statement will produce SQL error -611. To write portable SQL, use only simple data types in the result set of the scrollable cursor, and use the primary key column to fetch TEXT/BYTE data in a secondary SELECT statement.

The following table lists the native scrollable cursor availability for each supported database:

Table 1. Database server support for scrollable cursors
Database Server Type Scrollable cursors support TEXT/BYTE support (with scrollable cursors)
IBM® DB2® LUW Yes, see details No
IBM Informix® Yes, native SQL feature No
IBM Netezza Emulated, see details No (N/A)
Microsoft™ SQL Server Yes, see details Yes
Oracle® MySQL / MariadDB Emulated, see details No
Oracle Database Server Yes, see details Yes
PostgreSQL Yes, see details Yes
SAP HANA® Emulated, see details No
SQLite Emulated, see details No