Re: Data aging

From: Bill Despeaux <wjd_at_erols.com>
Date: 14 Jan 1999 03:51:05 GMT
Message-ID: <01be3f70$ed936720$f2ceaccf@default>

At a high level, this is pretty easy. If your database has lots of triggers,
dates in unique indexes or really big tables - it can get ugly.

One of my clients evaluated several data aging tools and they all wanted to copy the data. None of the products would age the data in place. I told them that any good programmer could whip up a data aging script in a matter of minutes so guess who got stuck with it...

Write some dynamic sql that queries dba_tab_columns for all table/columns with a data type of DATE (either a specific schema or where owner not like 'SYS%')
and generates update statements that use the add_months function (or just add the
number of days) to age the data to the desired date. With a little extra effort,
your script can ask you how much you want to age the data as well...

A quick hack at what the SQL should look like (I have the real code at work)
so verify the column names and syntax

select 'update table '||owner||'.'||table_name||' set '||column_name||' = add_months('||column_name||',12);'
from dba_tab_columns
where owner not like 'SYS%';

looking up at this I remember that the second column_name in the select had to
be in qoutes. to do this you can escape them or put another character in there,
then edit the output file to replace the markers with quotes.

things to look out for:

1) disable any update triggers on these tables
2) disable any unique constraints on the date columns
3) disable FK constraints on these cols
4) this can use a lot of rollback - you may have to break the update into

ranges
5) like any batch update, this can take a while... 6) consider rebuilding any indexes which include dates if performance degrades.
7) if your source data includes 1996 - know that data from Feb 29, 1996 can

    cause duplicates if aged to a non-leap year 8) aging non leap year data to 2000 can cause a gap in your data.

HTH, Bill.

Anne Bella Joseph <abj_at_idt.net> wrote in article <369C5DAC.5DCC_at_idt.net>...
> Hi,
> Has anybody done data aging to prepare a Y2K test data by advancing the
> date (for example to year 2000) in date fields for the tables in a
> particular instance (for a particular schema owner)?? Any SQL scripts
> available???
>
> Thanks in advance.
>
> jkorah_at_bcbsmn.com
>
Received on Wed Jan 13 1999 - 21:51:05 CST