This may be a left over from the old days but we never put a key on any table (Physical file) directly but just about every table (Physical file) had a view/index (logical file) that has a unique key defined over that table (PF). In some cases we have multiple views/indexes (LFs) with a unique key defined over a table (PF). If I want to access a table and read it beginning to end and do not need it in any sequence the fastest access is to use the un-keyed table (PF). If I need to access the table in a known sequence or pull a subset of data with a selected partial key I use the appropriate keyed access file.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, March 10, 2008 4:47 PM
To: Midrange Systems Technical Discussion
Subject: Re: Which of the SYSIBM tables/views show the row count for
Joe,
Good catch. I rechecked. Don't know what happened (forget to recompile
after changing source???). But I deleted the table and recreated it using
the DDS as unique key. Now it is closer to the SQL constraint. Good news
/ bad news. Good news - SQL is not the culprit. Bad news - some people
may use this information to continue not having keys on a physical file
(even though 95+% of their updates will involve some use of a key anyway).
12.289
12.280
12.136
However, if I add this "standard" logical
R IIMJOER PFILE(IIMJOE)
IPROD 25A
K IPROD
Against that same physical file then you will see the same times.
12.199
13.313
12.277
So, why not just have the UNIQUE key on the physical?
Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
03/10/2008 04:26 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc
Subject
Re: Which of the SYSIBM tables/views show the row count for
rob@xxxxxxxxx wrote:
So the UNIQUE was a good one in this case. I think it would be fair to
say that for files of this genre, in which there should be no duplicate
keys, then it should be a good one in all of that genre. Unless you can
come up with a good business reason why not, and can give a sample
thereof.
I thought I should do the runs myself, and I'm getting significantly
different results, Rob.
With no key at all, I get the 330ms number. With a key but without
UNIQUE, my average jumps to 1450ms. Add UNIQUE to the file, and it
leaps to 9300+. You might want to check your results. I did it by
actually performing a CHGPF on the file each time and clearing it before
each run.
And this is for only a single key, but at the same time no data to speak
of. I suspect the numbers change based on the size of the record and
the size of the key. However, if my numbers are correct, then a 7-to-1
jump in overhead is nothing to sneeze at, and it requires a darned good
business reason to add the keyword.
Joe
--
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.
midrange.com
