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

Walden, thanks for your suggestions.  

GUID is along the lines of what I've been looking for, and I've never met a
table that I wouldn't like to expand.  This is a honkin' big new application
to be coded by associates 9,000 miles away, so I'm already building in
provisions for this capability.  

Regarding coding triggers: BOORRRING!  I'll use a code generator to generate
the before-and-after (trapping) logic as a /COPY member and base the
contents on the system's list of columns.  

I will have program and user names in the table; I'll pick up the date/time
from the application record's change timestamp.

I'll Google _GENUUID and get educated.

Thanks again,
Reeve

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> bounces@xxxxxxxxxxxx] On Behalf Of Walden H. Leverich
> Sent: Monday, July 12, 2004 1:36 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: Fastest way to get a unique identifier/tracking column
> changes
> 
> Reeve,
> 
> >I've decided to build a single file with before and after values, etc.
> >for all the tables by using triggers.
> 
> We have a medical industry application (not iSeries based) that does
> exactly that. It works like a champ. From a design view you've got the
> right idea.
> 
> >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. ... Using SQL's AS
> IDENTITY with the
> table name could work to provide a key to a specific record.
> 
> If I understand you correctly, you're willing (able) to change the
> original tables to add a new field. Why not use a GUID? Every table
> we're auditing has a column we call RowGUID which is a GUID field. On
> the iSeries it would be a 16 byte character field. This field (and only
> this field) is the key to the audit table. On the audit table we call it
> OwningRowGUID. Want to find all the changes to a row in any table?
> Run:
> 
> Select ColumnName, OldValue, NewValue, UpdateDate, UpdateUser
>       from AuditLog
>       where OwningRowGUID = :RowGUID
> 
> And you're all set.
> 
> Some things we've done (or wish we'd done) to make our life easier:
> 
> 1) Don't store the table and column name on the audit log. You've got
> LOTS of rows in that table and it grows like wildfire. Create another
> table called RowColumnLookup in which you store an identity column and
> the original table, column name. Then you need to store a 4 byte int on
> the audit row, not 2 10 byte names. (and in our case the names can be
> >
> 10 bytes so it's even worse)
> 
> 2) Don't write the triggers by hand. Once you write 10 to 20 of them
> you'll realize that they're all the same with different column names.
> Write a program that you pass the table name to and it reads the
> schema
> (or dds) and writes the RPG you need. After you've written a few by
> hand
> you'll realize how easy that really is.
> 
> 3) In the trigger allocate a new GUID to represent the change you're
> processing. Remember, in this design you'll have multiple rows in the
> audit log for a single change in the original table. If the user changes
> 5 columns in a row you'll be inserting 5 rows into the audit table. This
> GUID will be the same for all 5 rows so you can tell these all came from
> the same update.
> 
> 4) If you have long fields (we have some varchars that can be up to
> 8000
> characters) then consider holding "small" values on the base audit
> table, and having a second table that has the long old and new columns.
> We had to do that because SQLServer doesn't allow a row greater than
> 8K
> (one page in SQLServer) and since the old and new value could _each_
> be
> 8K we needed one row for the old value and one row for the new value.
> 
> 5) Consider storing some sort of session identifier on the audit table
> too. An obvious choice is Job name, number and user. Then you can also
> answer the query: "Tell me everything Bob did when he logged in last
> night at 8PM after we fired him at 4PM and IT didn't disable his login
> until 9AM this morning." <G>
> 
> -Walden
> 
> ------------
> Walden H Leverich III
> President & CEO
> Tech Software
> (516) 627-3800 x11
> WaldenL@xxxxxxxxxxxxxxx
> http://www.TechSoftInc.com
> 
> Quiquid latine dictum sit altum viditur.
> (Whatever is said in Latin seems profound.)
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Reeve
> Fritchman
> Sent: Monday, July 12, 2004 10:22 AM
> To: 'Midrange Systems Technical Discussion'
> 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.