SQL Doesn’t Like Logical Files
April 9, 2008 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.
|
Hi Ted,
“Today’s tip: Don’t put DDS-defined logical file names in your SQL statements.”
This is no longer true, right? I work on a V7R2 machine, and I used ACS Run SQL Scripts to query a DDS LF. Visual Explain showed that the query engine used SQE.
Thank you,
Allan
Much has changed since I wrote this article. I don’t think anything goes thru CQE anymore. I query logical files all the time these days because of the way the database of our ERP is structured.
I think you’re right, Allan. I believe everything goes thru the SQE now. I put logical files in SQL queries all the time because of the way the database is designed in the ERP we use.