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

Chapin,

Do you use this in a HA environment?

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Kaynor@xxxxxxx 
Sent by: midrange-l-bounces+rob=dekko.com@xxxxxxxxxxxx
07/12/2004 10:28 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

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.