Implementing Search Limits

The RECORDLIMIT phrase sets a maximum for the number of rows the data 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.

How to: Implement Search Limits Example: Using Oracle READLIMIT Optimization

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

• The Oracle Data Adapter passes READLIMIT screening conditions to the Oracle RDBMS as SQL WHERE ROWNUM <=n clauses. ROWNUM is a special pseudocolumn that allows Oracle to limit the number of rows retrieved and returned.
• For DB2, READLIMIT appends an OPTIMIZE FOR n ROWS clause to the generated SQL. This can improve DB2 performance if you know the size of the desired answer set in advance. For more information, consult the IBM DB2 SQL Reference Manual.

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.

Search limit tests are helpful when:

• Testing a new Master File. Reporting requires only a few rows to indicate if the description is accurate.
• Using the trace facility in problem resolution.
• Testing the format of a new report.

In all three cases, a few rows are sufficient to verify results.

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 data 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.

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 Oracle Data Adapter 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.