How SQL Works:

When you execute an SQL command, the system determines the best way to carry out your request. That is, you concentrate on the task that needs to be done, and the system figures out how to do your task. Various software components are involved in this process, and for this discussion, you need to know about three of them.

First is the Query Dispatcher, whose job it is to decide which of the two query optimization engines it will call on to optimize and process a query. The second and third software components are the two query engines--the Classic Query Engine (CQE) and the SQL Query Engine (SQE). SQE is newer and better than CQE, but there are certain tasks that it can't carry out.

You can reference four types of files in SQL statements: DDS-defined physical files, DDS-defined logical files, SQL tables, and SQL views. SQE can't handle DDS-defined logical files. SQL views and indexes are also implemented as logical files, but they are not applicable to this discussion.

CQE handles all non-SQL queries, such as the Open Query File (OPNQRYF) command and Query/400. CQE also handles distributed queries via DB2 Multisystem.

If you wish to query a logical file from an SQL statement, consider querying the underlying physical file(s) instead. If the logical file has select/omit criteria, put the criteria in the WHERE clause. Another approach would be to create a view over the physical file and reference that view in your SQL query.