How to manually clean up the bar_instance table in the SYSUTILS Database

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:

Step 1

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;

Step 2

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);