TABLEF FILE is an alternate for the TABLE FILE command when the answer set returned from the RDBMS is already in the sort order required for the report. TABLEF is faster and more efficient because FOCUS does not build (and sort) an internal matrix, but works directly on the data returned by the RDBMS.

To further improve efficiency, create an SQL clustered index on the sort field used in the TABLEF request. The RDBMS physically stores the data in the same order as the clustered index, so the request requires fewer I/Os to data pages.

Most reporting syntax is acceptable in TABLEF requests, but be aware of the following:

• If BY phrases are passed to the RDBMS as SQL ORDER BY phrases, the answer sets returned are sorted and FOCUS does not have to sort them again or verify the sort order.

  In some circumstances FOCUS does not pass an ORDER BY phrase to the RDBMS (see The Adapter Optimizer), so you must verify that the ORDER BY is passed before relying on the sorted order of the returned answer set. Use STMTRACE (see Tracing Adapter Processing) to examine the SQL statements generated by the adapter.

• ACROSS phrases are not supported.
• Requests with multiple display commands (multi-verb requests) are not supported.
• The RETYPE command is not available.
• You can include the ON TABLE HOLD command in the TABLEF request to produce an extract file.

Note: In a request that generates report output to the terminal, TABLEF holds locks on data pages until the adapter issues a COMMIT (usually when the report display is terminated).

Locks may prevent access to the data by other applications. If this presents a problem, reports generated to the terminal using TABLEF should be processed and terminated as quickly as is feasible. (For a detailed discussion of locks and isolation levels, see Maintaining Tables With FOCUS.) This consideration does not apply to regular TABLE requests, because the adapter normally issues the COMMIT prior to displaying the report. Nor is it a problem for requests that generate only offline reports or extract (HOLD) files, as the answer set is exhausted automatically.