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

Steve,

Not sure where you heard that...but Db2 for i and it's predecessors, unlike
some RDBMSs, don't store data by key even if the table has a primary key
defined.

Data is always appended to the end of a file. Unless reuse deleted records
is on; in which case the data could be stored in any available space.
Again the primary key doesn't matter.

note: "clustered index" (MS SQL Server) or "index organized table" (Oracle)
are the terms indicating that the data is physically stored in index order.

Closest you can get with Db2 for i, is to use RGZPFM; but the "clustering"
will only last as long as there are no more inserts to the table.


Charles


On Mon, Sep 27, 2021 at 2:26 PM Steve M via RPG400-L <
rpg400-l@xxxxxxxxxxxxxxxxxx> wrote:

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.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com