Removing the Failed Instances Manually From the Database

The error in BO XI 3.1 SP3 causes millions of failed instances generated (SAP note 1448881 "Multiple instances spawned after daylight savings time change"). If the problem is not solved timely, the BO is getting slow, services is starting to fail often. It is usually possible to remove the instances programmically (SAP note 1568718 "How to delete all failed instances in XI3.1 programmatically"), however this script does not work if the number of the failed instances large – millions of records.

SAP note 1654183 "How to delete all the failed instances in SAP BusinessObjects Enterprise manually" is not that clear and more over it is incorrect. The correct script (for Oracle) is given below.

1. Stop SIA.
2. Make backup of CMS database and FileStore folder.
3. Execute the following script:

CREATE TABLE cms_infoobjects6_temp AS (
    SELECT * FROM cms_infoobjects6
    WHERE schedulestatus != 3 
    OR schedulestatus IS NULL);

TRUNCATE TABLE cms_infoobjects6;

INSERT INTO cms_infoobjects6 
    SELECT * FROM cms_infoobjects6_temp;

COMMIT;

DROP TABLE cms_infoobjects6_temp;

4. Start SIA and test the system.