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

Thank you, Charles.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles
Wilt
Sent: Monday, September 27, 2021 16:01
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: 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

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