IDMS SQL SELECT Limit Number of Rows

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

How to limit the number of rows in an IDMS SQL SELECT statement.

Environment

Release : 19.0

 

 

Resolution

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.

Additional Information

See documentation on SET SESSION  statement.