IBM Support

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

Technote (FAQ)


Is there a way to clean up the bar_instance table in SYSUTILS without using onsymsync?


The bar_instance table is used to track the list of objects that have been backed up. Over time this table can grow to a large size. Onsmsync can be run to check the storage manager for outdated objects, but it can take a long time to delete objects on a large system. Using an SQL query can be a much faster way to expire the objects when onsymsync is taking too long .


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

Step 2 -There are some additional 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);

Document information

More support for: Informix Servers

Software version: 11.5, 11.7, 12.1

Operating system(s): AIX, HP-UX, Linux, OS X, Solaris, Windows

Reference #: 1589855

Modified date: 01 June 2015

Translate this page: