E: Logical File or OPNQRYF or anyother way ?

["Dave Odom" <Dave.Odom@xxxxxxxxxxxx>]

Charles,

You wrote that which is quoted below and let me say BRAVO to someone
out of the very few on the iSeries that seems to understand the benefits
and ins-and-outs of SQL, logical files, etc.  

Let me also offer something in cursory form...  if the developer has a
choice, use the full benefits of DB2/400 and instead of creating the
legacy AS/400 flat file architecture, create DB2 tables with appropriate
indexes, views, etc., and use Static SQL in a compiled program, not a
query language nor command nor anything that creates Dynamic SQL.     If
you can, use stored procedures, especially if your application is
web-based and accessing the DB2 tables.

Take care,  
Dave

"While others have pointed out that embedded SQL is an option (and one
I and others prefer over OPNQRYF), let me point a reason not to use a
logical.

Each set of sort/select criteria needs its own LF.

In contrast, SQL (and OPNQRYF?) can make use of composite keys when
they run.

For example, lets say you have three fields: key1, key2, key3.  To
provide for all possible sorts, you'd need 9 separate logicals with the
overhead or performance penalties associated with maintaining the access
paths.  On the other hand, you could simply use 3 indexes, one over each
key.  As needed the DB2 query engine make composite keys from the
separate indexes.  

Does this make sense?

Additionally, one plus for embedded SQL is it's higher performance vs.
native RPG I/O.  _IF_ you use it properly.  This means, only select the
fields of interest to you and fetch more than one row at a time.


HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121"