Row limiting clause

Informix®

Informix SQL supports the SKIP and FIRST/LIMIT keywords to limit the number of rows of a result set:

SELECT SKIP 10 FIRST 20 customer.* FROM customer ... ORDER BY cust_name

This Informix SQL syntax is not portable.

Recent database engines support the row limiting clause syntax defined by the SQL standard:
SELECT ... OFFSET n ROWS FETCH FIRST m ROWS ONLY

This should be the prefered syntax to be used, if all target database types support this SELECT clause.

The ODI database drivers can convert the Informix SQL SKIP/FIRST row limiting clause to a native SQL equivalent, if the row limiting clause parameters are simple integer literals (the clause is not translated when using SQL parameters / program variables).
Important: In addition to the SKIP/FIRST clause of the projection clause, Informix SQL supports also a LIMIT clause after the ORDER BY clause:
SELECT customer.* FROM customer ... ORDER BY cust_name LIMIT 10
This Informix SQL syntax construction is not converted by the ODI drivers. To benefit from the conversion, review the code to use the Informix SQL SKIP/FIRST clause instead.

Microsoft™ SQL Server

Microsoft SQL Server supports the following row limiting clause:
SELECT ... ORDER BY ... OFFSET n ROWS [ FETCH FIRST m ROWS ONLY ]

Solution

The Informix SQL row limiting clause can be converted by the Microsoft SQL Server driver to the native SQL equivalent clause, when the parameters are simple integer literals.

Note: The row limiting clause must not use SQL parameters. Only row limiting clauses using integer constants will be converted.
The translation of the Informix SQL row limiting clause can be controlled with the following FGLPROFILE entry:
dbi.database.dsname.ifxemul.rowlimiting = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.