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

I believe that the gent was talking about this capability:
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymst79.htm
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymst80.htm
And these are a sequential number, not a UUID, GUID item.  Where are these 
stored?  In the DB itself?  Basically, how is this handled by H/A 
software?

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





"Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
07/12/2004 02:26 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

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






>I just realized there's a big problem in using a system-generated
unique
>number: what happens when I go to an HAS solution? 

If it's a GUID, no problem. The numbers aren't unique on a system,
they're unique period. Regardless of what system generates them. They're
actually ideal for this very reason, you can generate keys on any system
and have uniqueness across all systems.

-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
Sent: Monday, July 12, 2004 2:52 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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.

--
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.