Re: Indexed PF vs. LF performance

Why wouldn't it? If all your other tables have identity keys, why
would you leave one table without identity keys?

On Tue, Jul 31, 2012 at 10:24 AM, <rob@xxxxxxxxx> wrote:

Define "need".
It's not required in the CREATE TABLE.

But I agree that almost all tables should have a "primary key" constraint.
Why would one need one on a transaction history file though?
I agree that, in general, an identity column works well for a primary key.
I can bend on that. For example a table of states. Why should IN,
Indiana have an identity column as it's primary key?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Alan Campin <alan0307d@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 07/31/2012 11:59 AM
Subject: Re: Indexed PF vs. LF performance
Sent by: midrange-l-bounces@xxxxxxxxxxxx



All of that is pretty much a moot point today. Any SQL table needs a
primary key and should be an identity key.

On Tue, Jul 31, 2012 at 9:37 AM, Voris, John <john.voris@xxxxxxxxxxxxx>
wrote:

Another reason to not key the PF is because of DFU.

If you use the UPDDTA command of DFU, it generates a maintenance screen
on the fly. When you use UPDDTA on the PF, then your DFU screen shows
the file to you in RRN sequence.
The only other alternative for viewing a file in RRN sequence is to view
the file in HEX format from the DSPPFM command.

The advantages are many:
If you want to see recently added records from your testing, you
can scroll through records from the end of file.
Deleted records are immediately noticeable as the *RECNBR field
shows you RRN#.
ISO date and time fields in a key are usually a PITA to deal
with, so having other ways to access the file ( by RRN or other keys) is
truly handy.

The reason I remember was that doing a CPYF of an unkeyed PF was much,

much faster than doing a CPYF of a keyed PF.

I have no idea of that is still true or not but we still follow that

rule and never key a PF
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.