RE: Fastest way to get a unique identifier/tracking column changes

["Reeve" <news@xxxxxxxxxx>]

Hello Chapin,

Thanks for your comments.  

I'm using AS IDENTITY in existing applications; the problem is that AS
IDENTITY is unique to the table, not to the system, and my historical
tracking table handles multiple tables' data.  

To identify each track record, I'll need to key the tracking file on table
name /and/ identity number.  Identity 1 could be from any of the 40 or so
files I'll be tracking, so I need the table name as tiebreaker (is that a
technical term?).  I could use a different starting number for each table,
but I don't design applications using intelligent numbering schemes (because
it's not intelligent).

I'm considering the space and performance considerations of using
table/identity versus UUID to identity the changes.

-rf 

> -----Original Message-----
> From: midrange-l-bounces+news=ltl400.com@xxxxxxxxxxxx
> [mailto:midrange-l-bounces+news=ltl400.com@xxxxxxxxxxxx] On Behalf
> Of Kaynor@xxxxxxx
> Sent: Monday, July 12, 2004 11:29 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Re: Fastest way to get a unique identifier/tracking column
> changes
> 
> 
> Reeve,
> >From subsequent discussion, I'm  not sure you understood Rob's
> statement
> about the "As Identity"  keyword.   DB2 supports identity columns, so
> you can get
> a unique  identifier for free as in:
> 
>    create table Orders (
> Order  integer generated always as identity
> (start with 1, increment by 1),
> PurchaseOrder  varchar(20),
> Amount decimal(15,5))
> 
> The above is an excerpt from
> _http://www.midrangeserver.com/tfh/tfh081502-story04.html_
> (http://www.midrangeserver.com/tfh/tfh081502-story04.html) .  The
> article goes on to say:  "Unlike a sequence, the identity information is
> associated with the table object rather than a separate object.
> Associating the
> identity information with the table makes it easier to move data between
> systems  and to set up test environments."
> 
> We have used this technique effectively.
> 
> Regards,
> --Chapin Kaynor
> Vermont
> [apologies for lack of header info for thread tracking--I am
> cutting-and-pasting from digest mode in AOL e-mail]
> ---------------------------------------------------------------------
> ---------
> ----
> date:  Mon, 12 Jul 2004 10:34:03 -0500
> from: rob@xxxxxxxxx
> subject: Re: Fastest  way to get a unique identifier/tracking column
> changes
> 
> If you are using SQL's As Identity then it should generate one  for you.
> 
> Will your tracking table have the following columns:
> Table,  Entity, Column, NewValue, User, Timestamp
> or some such layout?  And your  concern is, if SQL's As Identity clause is
> unique then why would I need  Table in the tracking table?
> 
> Rob Berendt
> --
> Group Dekko Services,  LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN  46755
> http://www.dekko.com
> 
> 
> 
> "Reeve Fritchman"  <reeve.fritchman@xxxxxxxxxx>
> Sent by:  midrange-l-bounces@xxxxxxxxxxxx
> 07/12/2004 09:21 AM
> Please respond  to
> Midrange Systems Technical Discussion _midrange-l@xxxxxxxxxxxxx
> (mailto:midrange-l@xxxxxxxxxxxx)
> 
> To
> "'Midrange  Systems Technical Discussion'" _midrange-l@xxxxxxxxxxxxx
> (mailto:midrange-l@xxxxxxxxxxxx)
> 
> Subject
> Fastest  way to get a unique identifier/tracking column changes
> 
> 
> I'm designing  a new system with a requirement for detailed tracking of,
> and...
> 
> My  design is to assign every row an "entity number"; the entity number
> would be  like a record serial number, would be unique on a system-wide
> basis,
> and  would be the key to the historical tracking table.  When a user
> wants to
> 
> see the details of the changes to a specific row, the row's entity number
> would allow simple access to the tracking file.  Using SQL's AS  IDENTITY
> with
> the table name could work to provide a key to a specific  record.
> 
> 
> The challenge is to determine a way to get the entity number  quickly.
> Having a control file is okay but probably limiting  performance-wise;
> another possibility is a journaled data area.  Is  there a system API
> providing a guaranteed unique sequential number?  Or  is there a better
> approach for tracking column-level  changes?
> 
> 
> Thanks,
> 
> Reeve
> 
> 
> --
> 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.