Optimizing retrieval for a small set of rows

Question: How can I tell IMS that I want only a few of the thousands of rows that satisfy a query?

Answer: Use or FETCH FIRST n ROWS ONLY.

If you want to retrieve only the first few rows. For example, to retrieve the first 50 rows, code:

SELECT * FROM PCB01.HOSPITAL
FETCH FIRST 50 ROWS ONLY

Use FETCH FIRST n ROWS ONLY to limit the number of rows in the result segment to n rows. FETCH FIRST n ROWS ONLY has the following benefits:

• When you use FETCH statements to retrieve data from a result segment, FETCH FIRST n ROWS ONLY causes IMS to retrieve only the number of rows that you need. This can have performance benefits, especially in distributed applications. If you try to execute a FETCH statement to retrieve the n+1st row, IMS returns a +100 SQLCODE.