How to manually clean up the bar_instance table in SYSUTILS with out using onsymsync
Over time the bar_instance table contains objects and the table will grow to a hugh size. The fact that onsmsync query's the storage manager for outdated objects though unfortunate is expected and can take a long time to delete this objects on a large system. The SQL is a quicker way to expire the objects when onsymsync is taking too long .
You can delete based on a certain timestamp. To truncate the sysutils database Truncating the sysutils database based on a timestamp. You can do the following:
Delete the records of all space backups completed before the specified time (assuming the time is 7 PM on November 25, 2011):
delete from bar_instance where ins_aid in (select act_aid from bar_action, bar_object where act_end < "2011-11-25 19:00" and obj_oid = act_aid and obj_type != 'L');
If you must expire logical logs, then get the first log you need to keep by running the following after expiring the space backups as listed above:
select min (ins_first_log) from bar_instance
where ins_first_log != 0;
Then convert ins_first_log to a string and delete from bar_instance
where ins_oid = (select obj_oid from bar_object where obj_name < :ins_first_log);
delete from bar_object
where obj_name < :ins_first_log;
If you receive the following error :
692: Key value for constraint (informix.u101_4) is still being referenced.
Please change the delete to below and it will delete the correct rows.
where act_oid in (select obj_oid
where obj_name < "ins_first_log");
There are still some basic cleanup tasks to run after reducing the size of the bar_instance table.
Delete all bar_action rows that do not correspond to objects still in the bar_instance table:
delete from bar_action
where act_aid not in (select ins_aid from bar_instance);
Delete the bar_object rows that do not correspond to objects still in the bar_instance table:
delete from bar_object where obj_oid not in (select ins_oid from bar_instance);