How to limit the number of rows in an SQL SELECT statement.
Release : 19.0
Component : CA IDMS SQL
When coding SQL from a program the row count can be specified using the FETCH clause.
There is no row count on the SELECT statement but 19.0 PTF SO06940 introduced a new SQL Row Limit which can be set as a session attribute.
This sets a resource limit on the number of rows read. For example to limit the number of rows to 100 code:
SET SESSION SQL ROW LIMIT 100;
If the row limit is exceeded, error DB001108 SQL Resource Limit value <limit-value> was reached will be issued and the statement terminated so this cannot be used to select only the first x number of rows,
A method to retrieve the first x number of rows is described in article 20054:
See documentation on SET SESSION statement and Enhancements Since the Base Version 19.0 Software Release.