The Case of the Missing Logical File

Published: May 18, 2011

Hey, Mike:

I created a logical file that combines multiple tables into a single result set (similar to a SQL UNION.) However, when Microsoft Excel's Query Wizard presents a list of available tables and views to query, this logical file does not show up in the list. What is the problem?

Suzy

Hi, Suzy:

The problem is that DB2 for i is classifying your logical file as a non-relational file. It turns out that the Microsoft Excel Query Wizard uses ODBC (or OLE DB for Excel 2007 and later) to access DB2 for i. When tools like the Query Wizard and Crystal Reports ask DB2 for i for a list of available "tables" it can use, the results are retrieved from the QSYS2.SYSTABLES catalog. The special type of "union" logical files you mention are not included in this catalog. I'm guessing part of the reason for this exclusion is that these hierarchical logical files can potentially have multiple record types (which SQL doesn't handle).

For readers unfamiliar with the syntax, here is a simple source code example of how to create a logical file that spans multiple tables (this example assumes that the field names are the same in both files):

R PRODUCT                   PFILE(PRODUCT PRODUCT2)
K NAME

The best way to combine multiple tables into a single result set is to use SQL to create a VIEW with the UNION ALL operator. UNION ALL will do the same thing you are trying to accomplish with your logical file by combining two underlying tables in a single result.

Here is a sample view:

CREATE VIEW MYLIB/PRODUCTS AS
(SELECT * FROM MYLIB/PRODUCT
 UNION ALL
 SELECT * FROM MYLIB/PRODUCT2)

The one thing that the logical file can provide that the view can't is a keyed access path. If the underlying tables in the view already have indexes on the proper columns, SQL will be smart enough to use them when it queries the view. If the underlying tables do not have an index on the desired columns and if there is a sufficiently large number of rows, you'll want to build indexes on these columns (for each table in the view) using the SQL CREATE INDEX statement.

In the LF example above, the logical file created an access path on the NAME column across both tables. In SQL, the approximate equivalent would be to create indexes on the NAME column for each underlying table:

CREATE INDEX MYLIB/IDX_PRODUCT_NAME ON MYLIB/PRODUCT (NAME)
CREATE INDEX MYLIB/IDX_PRODUCT2_NAME ON MYLIB/PRODUCT2 (NAME)

Functionally the view and the LF will be about the same. However, SQL is the way of the future and the VIEW will be available in the list of tables and views that Microsoft Query and other tools present to users. Further, queries that use this view can also potentially be routed for processing to the newer SQL Query Engine (SQE).

--Mike

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

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