Hi all - been a while since I last posted but have a bit of an issue and I wondered if anyone else has come across a solution. At the moment it's looking like an expensive custom build.
In short the access history is huge - we made the jump from IC to ICP a few months ago so we would no longer need a 12 hour full refresh and could just do the routine 'data refresh' rather than full bulk transfer. In a nutshell:
· Our access history has over 141 million records in it.
· A data refresh (not full bulk export) in ICP started at 9:15 am does not finish the same day.
· A data refresh done at 03:00 am does complete, but takes 19.5 hours.
· A few days ago I was able to see the BAH file (access hist) was being processed at 8:50am, which is one of the last files processed in the refresh – it looks like this took 12 hours plus to process.
· During the refresh, queries get locked and there are delays for info going into ICP – I have seen delays of over 450 seconds shown on the monitor.
· Obviously the full bulk refresh takes a lot longer due to this (as it did with original Infocenter).
· For the meantime, I’ve changed the data refresh to only run on a Sunday so it doesn’t impact the business.
The fix would be to remove a lot of the older records from the access history. As I understand it, the tables in ICP replicate the structure found in OpenGI. As such all we really want is a once off process that deletes records from OpenGI’s equivalent of icp_braccesshist.
Getting rid of records prior to 01/01/2015 would approximately halve the data we have, but I’d be inclined to get rid of everything prior to 01/01/2017, which would lose over 90 million records.
In short we just need the OpenGI equivalent of the below.
delete
from
icp_braccesshist
where
[#date] < '20170101'
Has anyone else had this issue and/or found a solution? I don't want to disabled the access hist export as we use the data for a few things.
Kind regards
James