Implementing Search Limits

The RECORDLIMIT phrase sets a maximum for the number of rows the adapter fetches from the answer set returned by the RDBMS. It does not restrict the RDBMS in its construction of the answer set. However, if the RDBMS does not have to sort the answer set, using this feature may result in a significant reduction in response time.

The READLIMIT phrase is synonymous with RECORDLIMIT except in the following cases:

The READLIMIT and RECORDLIMIT phrases are effective in reducing RDBMS-to-FOCUS communication traffic, the volume of formatted report data, and terminal and disk I/Os.

Syntax: How to Implement Search Limits

{WHERE|IF} {RECORDLIMIT|READLIMIT} {EQ|IS} n

where:

n: Is the number of records to be included in the TABLE request.

The FOCUS database administrator may place the READLIMIT or RECORDLIMIT test in a Master File to limit the fetching of rows for all users. If the Master File and the report request both contain such a test, the adapter uses the lower of the two values.

Note:

For IDMS/SQL and Teradata, READLIMIT and RECORDLIMIT are synonymous and do not pass any special SQL syntax to the RDBMS.
In some instances, the RDBMS performs a substantial volume of work before returning any data to FOCUS. To limit the amount of work performed by the RDBMS, add another IF or WHERE test to one or more of the tables in the request.

Top of page

Example: Reducing DB2 Answer Set Size

The following request turns on the STMTRACE trace component and issues a retrieval request with a READLIMIT phrase. The SQL request contains the FETCH FIRST 2 ROWS clause:

SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=STMTRACE//CLIENT
TABLE FILE EMPINFO
PRINT LAST_NAME FIRST_NAME CURRENT_SALARY
BY EMP_ID
IF READLIMIT EQ 2
END

The generated SQL request contains the FETCH FIRST 2 ROWS clause:

 SELECT T1."EID",T1."LN",T1."FN",T1."CSAL" FROM 
USER1."EMPINFO" T1 ORDER BY T1."EID" FETCH FIRST 2 ROWS ONLY FOR FETCH ONLY;

If the test specifies READLIMIT EQ 1, the SQL request contains the clause FETCH FIRST 1 ROW ONLY:

 SELECT T1."EID",T1."LN",T1."FN",T1."CSAL" FROM
 USER1.EMPINFO" T1 ORDER BY T1."EID" FETCH FIRST 1 ROW ONLY FOR FETCH ONLY;

Top of page

Example: Using Oracle READLIMIT Optimization

Assume the EMPLOYEE Master File describes the Oracle table USER1.PAYROLL and consider the following request:

TABLE FILE EMPLOYEE
PRINT EMP_ID
WHERE READLIMIT EQ 5
END

The Adapter for Oracle generates the following SQL for this request:

SELECT T1.EMP_ID FROM "USER1"."PAYROLL" T1 WHERE ROWNUM <=5;

Because the screening condition is passed to the RDBMS, only the first five rows retrieved by Oracle are returned. This results in less communication between the RDBMS and FOCUS and less processing by FOCUS prior to displaying the report output.