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

Technote (FAQ)


Question

How to manually clean up the bar_instance table in SYSUTILS with out using onsymsync

Cause

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 .

Answer

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_oid 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.

delete
  from bar_action
where act_oid in (select obj_oid
                   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);

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Informix Servers

Software version:

10.0, 11.1, 11.5, 11.7, 12.1

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1589855

Modified date:

2014-02-24

Translate my page

Machine Translation

Content navigation