Re: "Re-sequencing" a pseudo-key

*Am I right in surmising that your legacy system is a sales processing
> system? If so, given that you restarted with negative numbers several years
> ago, and expect to reach zero 1-1 1/2 year from now, did you consider
> archiving all rows with positive RECIDs? *

You're partly right, but it is not "just" sales processing ;-) It's our ERP system called Concorde XAL (which is the predecessor of what has now become Microsoft Dynamics AX.)

This system started life as a fairly standard off the shelf ERP system in '96. It is sales, purchasing, customer relations, vendor relations, products, inventory, warehouse management, general ledger, human resources, anything. It is also a development environment and from '96 onwards we have been (and still continuously do) modifying, changing and adding code, so by now it is much more in-house built application - the sales, purchasing, warehouse management etc. is still there, just modifyed a lot by us. And then new bits of application has been added for task management, SMS handling, point of sale, automatic robot warehouse, wheelguide, etc etc etc. But it is still mostly written (augmented with a good deal of SQL) in the legacy language, legacy compiler and legacy runtime engine that runs the whole thing and that particular bit (the kernel) is unchangeable by us - we don't have the kernel source code of the engine - and therefore cannot change 32-bit to 64-bit and cannot change the "sequence caching" the kernel uses. I'd love to switch to some more modern development environment, but there's 18 years worth of code tailored to make the business successfull, so it's pretty darn hard to ditch ;-)

So archiving anything with positive RECIDs will also archive old but still active customers, products, vendors, employees, etc. Sorry, but that is a no-go :-(

(Also boss would throw a fit if he had to start a different app to view old data - seen from his point of view one of the causes of the success of the company is that *everything* we know about *anything* is *immediately* available on demand in *one* system :-)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

On Mon, Oct 13, 2014 at 2:46 AM, De DBA <dedba_at_tpg.com.au> wrote:

> Hi Kim,
>
> Am I right in surmising that your legacy system is a sales processing
> system? If so, given that you restarted with negative numbers several years
> ago, and expect to reach zero 1-1 1/2 year from now, did you consider
> archiving all rows with positive RECIDs?
>
> To do this, you could simply clone the database and remove the positive
> RECIDs from the production. When the production hits the maximum positive
> RECID several years from now, you could copy the remaining negatives from
> production to the archival clone and remove the negatives from production.
> When production hits 0 again in a decade's time or so, you store the now
> complete archival clone to some permanent storage and start with a new
> clone.. You would of course need two incarnations of your legacy app, one
> connecting to the archive, with only reporting functionality enabled, and
> the live one.
>
> This way historical data from the past decade is still online, if
> immutable, and new data can continue to be added. "Recent" historical data
> (i.e. the negative RECIDs) is in both the live and the archival database,
> providing a good overlap for reporting. Really old data will be available
> through a second interface for long-term trend studies etc and ancient data
> on request...
>
> Just a thought..
>
> Cheers,
> Tony
>
>

--
http://www.freelists.org/webpage/oracle-l