In addition, RRN is unreliable for relations when REUSEDLT(*YES) is set.
Vern
Birgitta Hauser wrote:
... but a relative record no is not useable if dependencies (such as primary
and foreign key Constraints) are based on it. Execute an RGZPFM and all
dependencies are destroyed.
... also when accessing a record with SQL using the relative record no,
either a table scan or a table probe will be executed, which slows down
performance. It is not possible to create an index over the relative record
no.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Raul A. Jager W.
Gesendet: Thursday, 26. November 2009 13:18
An: Midrange Systems Technical Discussion
Betreff: Re: SQL Sequence use cases
In some cases a "direct by Relative Record Number" is a good idea. It is
unque, no gaps, fast access.
Roger Harman wrote:
Agreed. I recently have had to deal with some SQL Server tables and
they are all designed that way. At first, I didn't care for it but I
really appreciate the value now.
We're used to things like part number, some code or other being the
primary key. Problem is, you can't change it without breaking the app.
Having the "id" field be the primary key and the part number, etc. being
a natural key gives you a lot of flexibility and, as you described,
simplifies some things greatly.
Again, read Dan Cruikshank's article. Thanks to Elvis et. al. at
Centerfield for publishing that.
pete@xxxxxxxxxx 11/25/2009 12:48:36 PM >>>
As long as it is unique I think it can be quite useful. Most of the
tables I work with outside of DB2 are keyed by sequence #. If you work
with a Rail application, most of the unique keys on tables are assumed
to be sequences and named 'id'. In fact, if you DO use sequences and
the column name is 'id', Rails pretty much works right out of the box.
I have table on the i from a "legacy" application that has 8 fields that
make up the unique key. If the table had a column that was a sequence,
my I/O would be SO much easier to accomplish. Issuing an update to
table x where id = 34423 is a lot easier than update tablex where
key1='foo1' and key2 = 'bar1', etc....
As for a "business" case, simplifying I/O could simplify development and
reduce costs. In the MSSQL application I work with, every table has a
sequence column named "id" (even though it isn't a Rails app, it is a
.Net app). Initially I found it confusing but as I work with the tables
more, my code can follow the pattern of using the sequence as the unique
key to the row making for greater consistency in SQL update statements
and my model code.
Perhaps it boils down to coding and DB design preferences. But, going
forward, all my new tables will have sequences that uniquely identify
the record.
Pete
David Gibbs wrote:
On 11/25/2009 2:09 PM, Luis Rodriguez wrote:
As per the business cases that David comments, I suppose that this
(the sequence object) could be seen as just another tool that SQL
provides. Myself, am a little uneasy about the storing of the data in
a *DTAARA (something that sometimes gets overlooked when restoring or
saving). Also, the fact that I can't get a guarantee of the sequence
can give some problems (think, for example, invoice numbers).
I'm not so concerned about the backup / restore issues ... as I always
recommend backing up the entire data library at once.
The non-sequential nature of a cached sequence makes me wonder what
kind of application it could be used for.
david
