RE: RPGLE native I/O and SQL Tables/Views/Indexes

At risk to my own life and limb I will proffer this.....

I was always taught that the best practice was to have an un-keyed physical
file with the third-normal form (primary) key on the first logical file.
By keeping the physical file itself in an un-sorted sequence, FIFO, that
allowed the SQL engine to most effectively handle SQL inquiries against the
physical file. If the physical file was in a sorted (keyed) order, that
slowed down the hash table process over the physical file before actual
processing of the request could begin.

I used those terms because that rule was pre-dating the more widely accepted
standards of tables, indexes, and views within our platform.

Recovery from a crashed system was always best facilitated by not having
"cross-compiled/referenced" physical files and logical files (in two
different libraries), otherwise you had to be sensitive to the load
sequencing when restoring the libraries. The always fun one was where
"physical A in library X has logical B in library Z" as well as "physical C
in library Z has logical D in library X" so no matter which order you
restored libraries X and Z something would fail. Ah, the good ole days....

Steve M.


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles
Wilt
Sent: Monday, September 27, 2021 15:13
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: RPGLE native I/O and SQL Tables/Views/Indexes

Brian,

That hasn't been true in the 20 years I've been working on the platform...

The tale as I heard it, there were benefits to having a PF un-keyed back in
OS/400 v1 (or maybe it was 36/38?) during the recovery of a crashed system.

But that stopped being true long ago...

Charles

On Mon, Sep 27, 2021 at 1:37 PM Brian Parkins <goodprophet.bp@xxxxxxxxx>
wrote:

Indeed, but for DDS-defined Physical Files (pre-constraints) it was
deemed best practice to define keys in Logical Files - not the
Physical File itself. (Impact of change.)

Thx for calling that out, Charles.

Brian.

On 27/09/2021 17:44, Charles Wilt wrote:

I really hope your standards for SQL Tables have a primary key...

Just like your standards for PF should include a unique key.