ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

CA IDMS SQL SELECT Limit Number of Rows

book

Article ID: 133073

calendar_today

Updated On:

Products

IDMS IDMS - Database IDMS - ADS

Issue/Introduction

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

Environment

Release : 19.0

Component : CA IDMS SQL



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: 

https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=20054


Additional Information

See documentation on SET SESSION  statement and Enhancements Since the Base Version 19.0 Software Release.