Re: convert LF's to SQL - LF's have Select/Omit

Thanks for your reply's Chuck,

> IMO the better approach
> over conversions is to start using DDL.

We have been using DDL for all NEW things...

I would say that our goal (maybe misguided?) is:
_Slowly_ migrate away from the RLA programs, but attempt to
achieve benefits in performance (with page sizes, and Encoded
Vector Indexing on some of our files)..

Start with our Item master file, then our Bill of Materials file.

lots of our other big systems depend on multi-member files, so they
will need more like complete re-writes...






On 4/16/2013 2:47 PM, CRPence wrote:

On 16 Apr 2013 11:34, Gqcy wrote:

I am reading about converting my DDS files to SQL...

Be sure if going down that path, that the decision to do so is made to
effect something specific; i.e. such a change should not be undertaken
simply "because SQL is..." and thus perhaps change just specific files.
There should be little to no reason to change more than some specific
set of files, and should be done only when there are reasons that can be
articulated; e.g. doing so, solves a problem. IMO the better approach
over conversions is to start using DDL.

Most of our Logical files are doing both, ordering and record
selection...

However I am reading that I should _only_ create indexes for my
ordering.

Effectively; the WHERE clause should generally not be included, esp. if
the applications are changing from RLA to SQL. Though FWiW, the INDEX is
not really used much for /ordering/ in SQL, as they are mostly used for
selection. Although implicitly, the concept of ordering is apparent from
a key, for the SQL the most selective key generally should be first,
which is atypical for feasibility for ordering. That is because unlike
RLA which often desires ordering by the least selective key first, the
SQL wants the most selective key for selection in order to
encounter\process as few rows as possible.

I see I and <ed: where I can> put in a where clause for my indexes,
but should I?

Atypically. When there is a specific matching and common query that has
that same selection, then there possible value in doing so. The WHERE
clause is IMO a much nicer way to specify the selection logic than using
the DDS COMP on S and O specs. A good replacement if RLA vs SQL is
continued against the data; the predicates are much easier to change
than the S\O of DDS LF.

The more important value I find in having the INDEX is when mapping to a
consistent data type for typical queries. For example if one file has an
INT type and the other a DECIMAL(8), then a casting expression to define
the key of one to match the other allows comparisons via the keyed
access path vs having to either create one dynamically by the query or
performing the conversion for comparisons at run-time.

Should I create SQL indexes just on ordering?

Effectively... unless those with selection are still needed for RLA vs
for SQL, or a commonly-run query can and does take advantage of the
INDEX with the WHERE clause either because the query has the identical
predicates [such a query could be encapsulated, in a VIEW, for example]
or the statistics from that INDEX are just helpful to the optimizer.

will the Logical files share the index, even though the logical
files have select/omit logic?

No. The WHERE clause is like an LF without DYNSLT. The selection logic
defines the rows selected for the keyed access path, and thus there is
no sharing; each file's access path is maintained separately. And I
think no sharing is the effective rule for SQL INDEX anyhow; i.e. while
a DDS LF can share an access path of an INDEX or a CONSTRAINT with a
compatible but more expansive key, IIRC an SQL INDEX can not share any
access path except an exact match to either another INDEX or to the
PRIMARY or UNIQUE KEY constraint [implicitly adopting the rules of the
constraint].?