Re: AW: SQL Sequence use cases

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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