Unique keys on physical files

Unique keys on physical files

Post by Andrew D Hio » Tue, 28 Apr 1998 04:00:00



Could everyone give me their views on the pros and cons of placing primary
(ie unique) keys on physical files as opposed to leaving a physical as
'arrival' sequence only and placing the primary key on a logical?  Are there
good reasons why you shouldn't do it?

 
 
 

Unique keys on physical files

Post by John Dun » Tue, 28 Apr 1998 04:00:00


A long time ago, in a galaxy far, far away...
In the System/38 days, IBM told developers/software houses to leave physical
files keyless to enable possible future enhancements for things like global
field change across all files referencing a field reference file.  I believe
now-a-days it is a matter of personal preference.


> Could everyone give me their views on the pros and cons of placing primary
> (ie unique) keys on physical files as opposed to leaving a physical as
> 'arrival' sequence only and placing the primary key on a logical?  Are there
> good reasons why you shouldn't do it?


 
 
 

Unique keys on physical files

Post by OSIT » Tue, 28 Apr 1998 04:00:00


Quote:>Could everyone give me their views on the pros and cons of >placing primary

(ie unique) keys on physical files as opposed to >leaving a physical as
'arrival' sequence only and placing the >primary key on a logical?  Are there
good reasons why you shouldn't do it?

Whether or not a physical file should be keyed, is a separate question from
whether or not it should have a unique primary key.

Not keying the physical has some advantages:

1. This greatly improves restore time, if you didn't save access paths and if
you are restoring the physical to a different library.

2. Copy file, by default, copies by key. Sometimes it is benefical to copy by
arrival sequence, without having to remember to specify FROMRCD(1).

3. Occasionally, it is convenient for a program to be able to access a  file by
arrival sequence (especially for updates by RRN). And it makes more sense that
the physical not have a key (or be opened with arrival sequence), than to
create a logical without a key or t(or be opened by arrival sequence)

On the other hand:
1. It is more in line with how other SQL implementations work, if you always
key the physical file by a unique key.

2. Putting a key on the physical file, eliminates the need for creating at
least one logical.

 
 
 

Unique keys on physical files

Post by Bradley V. Sto » Wed, 29 Apr 1998 04:00:00



Quote:>Could everyone give me their views on the pros and cons of placing primary
>(ie unique) keys on physical files as opposed to leaving a physical as
>'arrival' sequence only and placing the primary key on a logical?  Are there
>good reasons why you shouldn't do it?

This is up to you.  I've heard arguments against it, IBM has alsways
said not to key PFs, but never gave a good reason.

One reason could be that future enhancements will be a whole lot
easier if you only have to change the logicals, and not have to do the
copy file thing when you change the PF (but now we have CHGPF!)

I would say don't do it.  It's up to you...

Bradley V. Stone
http://prairie.lakes.com/~bvstone
"Robble robble!" - The Hamburgler

 
 
 

Unique keys on physical files

Post by Paul Nicola » Wed, 29 Apr 1998 04:00:00


Hi Andrew,

I guess using LF's is a more structured way in doing this (and you don't
save a logical if you don't, it's just included in the physical and needs
processing time as well).  The only disadvantage I remember is that we once
got a corrupted keyed physical (one of our customers site, as I never use
it myself), and got no means in recovering the data.

Kind regards,
Paul
___________________


Quote:> Could everyone give me their views on the pros and cons of placing
primary
> (ie unique) keys on physical files as opposed to leaving a physical as
> 'arrival' sequence only and placing the primary key on a logical?  Are
there
> good reasons why you shouldn't do it?

       The contents of this message express only the sender's opinion.
       This message does not necessarily reflect the policy or views of
       my employer, Merck & Co., Inc.  All responsibility for the statements
       made in this Usenet posting resides solely and completely with the
       sender.
 
 
 

Unique keys on physical files

Post by Johan Verri » Wed, 29 Apr 1998 04:00:00


First of all : you never need an 'arrival' sequence, The information
you store is (almost) always in  a specified  order. The way you
access your information is described in the key. Secondly the building
of unique keys gives you the advantage to always being able to
point-down a record by logical definition (record 49494 could also
point a records, but is not 'semantic'.
Besides, when your application is trying to write duplicate records
there could be a logic error that could not be found when records were
stored in  'arrival' order.

Regards,

johan verrips

On Mon, 27 Apr 1998 17:06:10 +0100, "Andrew D Hiom"


>Could everyone give me their views on the pros and cons of placing primary
>(ie unique) keys on physical files as opposed to leaving a physical as
>'arrival' sequence only and placing the primary key on a logical?  Are there
>good reasons why you shouldn't do it?

 
 
 

Unique keys on physical files

Post by David Dunfiel » Wed, 29 Apr 1998 04:00:00


I have seen instances of damaged keys on a physical file making access to the
data impossible. We try to stay away from  keys on physical files because of
this.


> Could everyone give me their views on the pros and cons of placing primary
> (ie unique) keys on physical files as opposed to leaving a physical as
> 'arrival' sequence only and placing the primary key on a logical?  Are there
> good reasons why you shouldn't do it?

 
 
 

Unique keys on physical files

Post by GaryWWe » Thu, 30 Apr 1998 04:00:00



>The second issue, however, I've never seen anything that clearly indicates
>that it's been circumvented properly. Perhaps nowadays recovery can be
>complete through an attached, unkeyed LF. Personally, I'm not ready to force
>damage on a PF just to test it out. I'd like to know the answer though.

I had a damaged keyed PF awhile back where I had trouble copying the data. I
created an OPNQRYF over the PF with no KEYFIELD's and was then able to copy the
data using CPYFRMQRYF.

Gary W. West (consultant/contract developer - Software Solutions, Inc.)

 
 
 

Unique keys on physical files

Post by Dave Sha » Thu, 30 Apr 1998 04:00:00



>A long time ago, the disadvantages of a PF index were two-fold: First,
there
>was no way to create a LF without a key. And second, damage to an index on
a
>PF meant that you couldn't recover data from it.

>The first issue was resolved quite a while ago when IBM allowed LFs without
>keys. This meant that arrival sequence was possible without referencing the
>PF. (Contrary to the thought that arrival sequence is *never* necessary, I
can
>provide a number of data recovery scenarios where it *is* necessary.)

>The second issue, however, I've never seen anything that clearly indicates
>that it's been circumvented properly. Perhaps nowadays recovery can be
>complete through an attached, unkeyed LF. Personally, I'm not ready to
force
>damage on a PF just to test it out. I'd like to know the answer though.

>Me? I prefer PFs to be unkeyed.

This is the second time that I've seen a post which seemed to imply that a
keyed physical file can't be accessed in arrival sequence.  This is not
true.  Any physical file can be opened for arrival sequence or RRN access in
an HLL program just by coding the file spec for it, for example omitting the
K in the F-spec in RPG.  CPYF can do it as well, just specify FROMRCD(1) on
your CPYF command.  If the index is damaged on a physical file, this is how
you recover the data from it.  Note: I BELIEVE this has worked since CPF
release 1.0, I KNOW it's worked since CPF release 5.1.  (For those of you
unfamiliar with the /38, think of its OS releases as OS/400 Version Zero -
grin.)

--
Dave Shaw, General Nutrition, Greenville, SC (just down the road from BMW -
Bubba Makes Wheels :)
The opinions expressed may not be my employer's unless I'm sufficiently
persuasive...

 
 
 

Unique keys on physical files

Post by Samuel J Lenno » Thu, 30 Apr 1998 04:00:00


My preference is to *not* key physical files and always have the keys,
unique or otherwise, in the LF.  I tend to forget to specify FROMRCD(1) on
CPYF and then wonder why the file is taking so long to copy.

It also seems to me that if you CPYF records into a keyed physical it takes
a lot longer because it builds the index as it goes.  If you have a large
file you are loading this way, it is quicker to disable the logicals, copy
in the data, then rebuild the logicals.  (There are several ways.)
Yes, this does leave you with the possibility of duplicate keys, but
creating the logical will catch that, and mostly you are copying back data
from some kind of re-org.

Sam

 
 
 

Unique keys on physical files

Post by Daniel S. Cochra » Sun, 03 May 1998 04:00:00


Andrew and Group,

I always wondered the same thing, till . . .

In the case of restores, if the Key on the PF is messed up, then you can take
forever (if you even can) restore all the data...

In the case of non-keyed PFs, you can restore from a Bad System problem faster,
and then rebuild the LFs over the PFs. The time this takes is greatly reduced
from the time it takes to restore Keyed PFs.

The time it takes to RCLSTR and remove deleted records and objects seems to be
faster if the PFs are not keyed.

Yes this causes at least one LF for every PF on the system. But, in the case of
mission critical restores, I vote take this path.

IMHO,
Daniel S. Cochran


> Could everyone give me their views on the pros and cons of placing primary
> (ie unique) keys on physical files as opposed to leaving a physical as
> 'arrival' sequence only and placing the primary key on a logical?  Are there
> good reasons why you shouldn't do it?

 
 
 

Unique keys on physical files

Post by Bill Sco » Mon, 04 May 1998 04:00:00


On Mon, 27 Apr 1998 17:06:10 +0100, "Andrew D Hiom"

One good reason to put your most used keys in a PF is "system
resources." Too many logical files can virtually bring a system to
it's knees. I remember in 1990/1991 when I was using Synon/2E, Synon
didn't key any of the physicals and every time you specified a
different access path, Synon generated a logical access path. If you
weren't careful you'd end up with hundreds of logical files and every
time you added a record you could go out for coffee while a C-10
AS/400 updated all the logical access paths.

Even today, customers not wanting to spend the money to supply
adequate resources in memory and DASD will have problems with "too
many logicals." How many are "too many" depends on the individual
computer's resources. I sell my code commercially so I never know how
big the computer is going to be, therefore I have my own rules about
this, and here they are:

If I need a logical, I create one, BUT - if I can find a logical out
there that can be modified and still work with the programs that are
using the old logical, I modify the old logical to support the new
program and recompile all the old programs using the old logical
access path.

Of course if you have unlimited resources you can be just as sloppy
about it as you want I guess, but I feel that putting your most used
keys in your physicals and limiting/managing your logicals makes for
"good programing" practice.

Quote:>Could everyone give me their views on the pros and cons of placing primary
>(ie unique) keys on physical files as opposed to leaving a physical as
>'arrival' sequence only and placing the primary key on a logical?  Are there
>good reasons why you shouldn't do it?