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

I though the "IDENTITY" value was table-specific, not global.  Therefore, to
track changes from multiple tables in one "historical tracking" table, I'd
need to identify each tracking record by table and identity value. 

You table layout is right; I'm including program name in it as well.

I just realized there's a big problem in using a system-generated unique
number: what happens when I go to an HAS solution?  How will the HAS partner
systems keep track of the "last used" number?  Of course, if such a function
doesn't exist, it's moot and I'm back to a user-controlled number, a data
area-controlled number, or the AS IDENTITY.

Note on using journals: Table/member/RRN is an inspired thought (even though
I don't use multi-member files for mission-critical functions), but there
are still a lot of programmers and operations managers determined to
reorganize files whenever possible (i.e. you're not looking).  If V5R3
reorg-while-active physically changes the RRN (which I assume it does), that
puts a crimp in the journal receiver solution.

-rf

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> Sent: Monday, July 12, 2004 11:34 AM
> To: Midrange Systems Technical Discussion
> 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@xxxxxxxxxxxx>
> 
> 
> To
> "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
> cc
> 
> 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
> 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
> 
> 
> 
> 
> 
> --
> 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.
> 
> 
> --
> 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.