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

[Kaynor@xxxxxxx]

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