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.