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

Answer

It is recommended to delete objects from the sysutils database based on a timestamp. Here is an example of how:

Step 1 In the sysutils database, delete the records of all space backups completed before the specified time (change the time in italics for your query):

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_oid and obj_type != 'L');
• If you also want to expire expire logical logs after expiring the space backups as described above:
• Locate the first log that needs to stay by running the following :

select min (ins_first_log) from bar_instance where ins_first_log != 0;

• Convert the returned value of 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 a 'still in use' error :

692: Key value for constraint (informix.u101_4) is still being referenced.
You may need to force the delete in order to remove all the correct rows:

delete from bar_action where act_oid in (select obj_oid from bar_object where obj_name < "ins_first_log");

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