RE: Logical File or OPNQRYF or anyother way ?

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Wilt, Charles
> 
> 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.

WAIT WAIT WAIT!

If you do NOT already have an index over the file, the index needs to be
built at run time.  This is a disastrous performance hit, especially on
a large file.  In fact, if you debug ANY query, you will immediately
note that the SQL processor tells you to create the appropriate index.
And what is an index?  It's a logical file.

Here's the cutoff: if the sequence is used rarely or the file is very
small, you don't need a logical; use SQL and let it build its index on
the fly.  If the sequence is used often and especially over large
numbers of records, ALWAYS create a logical view.

ALWAYS.


> 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?

Actually, no it doesn't.  How do you think this magic occurs?  The nine
different sort sequences are still required, and there is a very
definite overhead when trying to combine two single-field keys to create
a composite key.  Like most indexing operations, this overhead can be
disastrous on large files.

I have HEARD anecdotally about engines that somehow reduce the overhead
of this job, but I suggest you actually test it in practice on a
multi-million record file to see how it performs.


> 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.

SQL ONLY outperforms native I/O on blocked operations, especially
queries and even then only if the indexes are already created.  And that
means a logical file.

In ALL cases, EVERY case, WITHOUT AN EXCEPTION, if you regularly read
records from a file using the same sequence, you should have a logical
file over that physical with those fields.  Even if you subsequently use
an SQL statement for selection criteria, the SQL engine will still use
the logical file for ordering the records.

Like the JVM, people seem to get the idea that the SQL engine is a magic
land where I/O doesn't have to occur and keys don't need to be compared.
But in the end, it's just more code.

Joe