Midrange News for the IBM i Community

Posted by: Viking
Information Systems
LF records don't show up in SQL
has no ratings.
Published: 26 Dec 2012
Revised: 23 Jan 2013 - 3283 days ago
Last viewed on: 18 Jan 2022 (3993 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

LF records don't show up in SQL Published by: Viking on 26 Dec 2012 view comments(2)

We have some DDS-defined logical files on our system that DBU can work with just fine, but if I try to use SQL over them, there appear to be no records.  I was just experimenting with a couple different LFs to try to understand the pattern.  I have a join LF with selects/omits and new fields from substringed PF fields, and those records are not seen by SQL.  Another simpler LF is just over one PF but also has selects/omits and new fields from substringed PF fields, and those records are also not seen by SQL.  Another LF has some selects/omits and those records ARE seen by SQL.

In embedded SQL, I always use PFs, not LFs, but I was just trying to do some quick ad-hoc SQL to check the contents of some LFs when I noticed this behavior.

Just curious, what are the conditions that cause a DDS-defined LF to not have its records queryable by SQL?


Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: LF records don't show up in SQL
Posted: 9 years 24 days 4 hours 42 minutes ago

SQL only works with tables, views and indices that it understands. If you start adding non-sql stuff to your DDS defined PF and LF, then SQL has issues. The primary areas of difficulty is in joined logical files and those select/omit rules - SQL sees these functions as aspects of the SELECT operation and not a part of the data definitions. Under early versions of SQL if a physical file had a LF with any or all these aspects then SQL would throw out the SQL request and hand off to the AS400 CQE (classic query engine) which was much less efficient than the SQE.

Around V5 SQL became configurable such that you could persuade the SQE to ignore these "invalid" access paths when considering SQL eligibility. But many shops did not change IBM's default configurations so once again the SQE would puke on the request and the CQE would take over. At V7 the IBM defaults were changed to automatically ignore the invalid access paths, and today very few SQL requests should get kicked over the to CQE.

Posted by: Ringer
Premium member *
Comment on: LF records don't show up in SQL
Posted: 9 years 23 days 22 hours 26 minutes ago

SQL can use LFs, even join LFs, as long as they don't have select/omits in them.

The select/omit LFs can make the SQE hand the query off to CQE (slower) unless you use IGNORE_DERIVED_INDEX = *YES. Prior to v6.1, that system setting was shipped as *NO.


Chris Ringer