SQL Doesn't Like Logical Files

Published: April 9, 2008

by Ted Holt

I had a great time at COMMON last week in Nashville, Tennessee. I saw some old friends and met a lot of nice people. I listened to some great presentations put on by some very intelligent people, and that means I have some new tips that I can pass along to you. Here's a tip from IBM'ers Jarek Miszczyk and Tom McKinley.

Today's tip: Don't put DDS-defined logical file names in your SQL statements.

To understand why, let me give you a little background. 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. Guess which one of the four SQE can't handle? I hope you guessed DDS-defined logical files, to which I will refer to simply as logical files in the following paragraphs. SQL views and indexes are also implemented as logical files, but they are not applicable to this discussion.

Not using any logical files in an SQL query is no guarantee that SQE will kick in and do the work. In V6R1, there are two other cases that force the CQE to handle a query. CQE continues to handle all non-SQL queries, such as the Open Query File (OPNQRYF) command and Query/400. CQE also handles distributed queries via DB2 Multisystem. In earlier releases, even more work is given to CQE.

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.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot