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

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Just wondering whether journaling would be better performance-wise. You can 
have before and after images. You already have system-wide uniqueness with 
library name, object name, member name and relative record number.

Maybe?
Vern

At 09:21 AM 7/12/2004, you wrote:
> I'm designing a new system with a requirement for detailed tracking of, and
> inquiry into, column-level changes.  I've decided to build a single file
> with before and after values, etc. for all the tables by using triggers.
> Some of the tables have complex keys (order number/SKU/shipper
> location/consignee location/release number), and I don't want to burden my
> historical tracking file with a nasty key structure to support inquiry into
> the details of the changes.  I'm not going to track added records or
> date-of-last-change timestamps in the tables; the majority of the changes
> will be on a limited number of columns (of the status and date nature).
>
> 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