Datamart Purging Problem

[Bazza]

We have started to experience a strange problem with DataMart Purging where all of a sudden we no longer seem to be purging any data.

When the ETL_Purge Runs the last entries in the log are

'
ETL_Purging: Thu Dec 27 12:28;01 GMT 2007 Options: (jdbcurl=jdbc:oracle:thin@x.x.x.x:1521:SID, user=<user>, loglevel=fine, log=purge.log, pass=,<pass>
ETL_Purging: Thu Dec 27 12:28;01 GMT 2007 Connecting database url jdbcurl=jdbc:oracle:thin@x.x.x.x:1521:SID
Starter: Thu Dec 27 12:28:01 2007 [ETL_Purging] will be started on Fri 28 12:28:00 in 86399 seconds
ETL_Purging: Thu Dec 27 12:28;02 GMT 2007 Connected database
ETL_Purging: Oracle Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
ETL_Purging: With partitioning, OLAP and Oracle Data Mining options
ETL_Purging: JServer Release 9.2.0.4.0 - Production
ETL_Purging: Thu Dec 27 12:28;02 GMT 2007 Connected as <User> using Oracle JDBC driver version 9.0.1.0.0
'

There is then no further logging regarding the purge process and purge.log is not created

The purge.properties file is as below

log=purge.log
loglevel=fine
user=<Datamart User>
pass=<DataMart Pass>
jdbcurl=jdbc:oracle:thin@x.x.x.x:1521:SID

The DataMart table PURGING_LOG shows a number of Symptoms

You can either see a Purge Job had has a Start Time but no End Time or there is no entry at all

If anyone has seen this before I would be gratefull of any advice

[eugene]

Hi Bazza, silly question, but are the purge parameters setup correctly in ETL Assistant for the respective tables in Datamart to be purged.

Eugene

[Bazza]

Have checked those and they look fine.

As an example we have some tables set to hold 1 month and purge on the 15th and some of these tables have 6 months+ worth of data in them

[sriven3]

Have you tried running individual purge procedures manually and check the status in log tables.

[Bazza]

We have tried running the purge manually over christmas and the PURGING_LOG seems to suggest that just under 5 million rows have been purged on the 27th December the problem here is that there are nearly 300 Million rows in just the 1 table it is attempting to purge

[sriven3]

I don’t have experience with datamart features , but have purged data through running individual procedures manually , but there are bugs(this based on version) in procedures and process wont cleanly 100% but works better for cleaning upto 70% , hoping this solution helps , here are the steps.
1) Execute the all merge procedures first start with IR merge process.
2) Execute the all purge procedures first start with IR purge process with larger input value 60 (60 days) , pick this number based on when the last date records created in you find in call table or any other table, Since you have large volume records keep executing this process with reducing input value.

Note – how input value works is based on no of days for which you want hold data , example for input value 60 process will try purge data created before 60days from sysdate , also since you have large volume of data each purge cycle will take close 8 – 14 hrs for complete.

[victor]

Hi,

I am afraid to be wrong, but I remember hearing a while back that Purge would start working from January 1st. I know it sounds ridiculous so I will check with our engineers again, but a while back we had a similar problem where purge did not work, even though everything was preset. And I remember we did something to get it to work... I know I am not being too helpful right now, sorry, so let me leaf through the correspondence from that time and get back to you!

BTW, I have never seen loglevel=fine before!!! I always thought it were numbers...

Vic

[catanirex]

Have you tried to rebuild the indexes in DataMart?
Maybe the purge does take long time since so may rows sould be deleted, and the indexes needs to be rebuild?

[René]

[quote author=Bazza link=topic=2681.msg10621#msg10621 date=1199285528]
We have tried running the purge manually over christmas and the PURGING_LOG seems to suggest that just under 5 million rows have been purged on the 27th December the problem here is that there are nearly 300 Million rows in just the 1 table it is attempting to purge
[/quote]

You need to be patient. I'm sure that Genesys has implemented some restrictions in the purge module to avoid situation of purging several tens of millions rows in one batch. Trying to purge 300 millions of rows at once will make your database server inaccessible for a long long time... and high probability such operation will fail...

René

[Bazza]

We aremaking slow progress on this, but up till now we have re-built the indexes on the table which is being purged and this has reduced the index size from 21GB to 11GB. The database is currently not struggling to cope with the workload, it appears from the tracing that we have completed at the Database that the purge process used in Datamart 7.0 is not very effecient and our estimate is currently to purge the single largest table will likley take in excess of 50 days.

We are going to upgrade Datamart to 7.2 as this has some procedural improvements in the purge process along with the ability to utilise some of the more advanced Oracle functionality.

We hope to have this implemented in the next 10 days and will report back on progress once this is completed