BO XI 3.1 SP3 issue related to daylight savings time change

There is a quite serious issue in BO XI 3.1 SP3 related to the daylight savings time change. See the SAP note 1448881 for details.

After time change, BO generates thousands of failed instances for scheduled reports.  The CMS database is growing very fast. BO system becomes very slow and stops working because of lack of space in tablespace or on the disk.

The quickest way to check if you have the issue is to connect to CMS database and query the number of rows in cms_infoobjects6.

SELECT Count(*) FROM CMS_INFOOBJECTS6

The normal amount of rows is usually less then 10 thousands. If there are more then 100 thousands rows, the system is probably affected by the issue.

To resolve the issue the reports should be rescheduled and the failed instances should be removed (see the SAP note for details). The error is fixed in FP3.6.

The approach proposed by BO is to use JSP web-application to remove failed instances. This does not work well if you have millions of failed instances in your system. It may take a couple of weeks to remove all failed instances. Below is java console version of the tool.

import java.util.Date;

import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.framework.ISessionMgr;
import com.crystaldecisions.sdk.occa.infostore.IInfoObject;
import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;
import com.crystaldecisions.sdk.occa.infostore.IInfoStore;

public class Program {
    public static void main(String[] args) throws Exception {
        IEnterpriseSession enterpriseSession = null;
        try {
            System.out.println("Connecting...");
            ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
            enterpriseSession = sessionMgr.logon(
                    "Administrator",
                    "",
                    "localhost",
                    "secEnterprise");
            IInfoStore iStore = (IInfoStore) enterpriseSession.getService("InfoStore");
            String query = "Select Top 1 SI_ID from CI_InfoObjects"
                         + " Where SI_Instance = 1 And SI_SCHEDULE_STATUS= 3";
            int batchSize = 10000;
            System.out.println("Retrieving the number of failed instances...");
            IInfoObjects queryResult = iStore.query(query);
            System.out.println("Number of failed instances found: " + queryResult.getResultSize());
            int numOfBatches = 0;
            if ((queryResult.getResultSize() % batchSize) == 0) {
                numOfBatches = queryResult.getResultSize() / batchSize;
            } else {
                  numOfBatches = queryResult.getResultSize() / batchSize + 1;
            }
            System.out.println("Deleting in " + numOfBatches + " batches");
            int lastInstanceProcessedID = 0;
            for (int i = 0; i < numOfBatches; i++){
               String deleteObjectsQuery = "Select Top " + batchSize
                       + " SI_ID from CI_InfoObjects Where SI_Instance = 1"
                       + " And SI_Schedule_Status = 3 And SI_ID > " + lastInstanceProcessedID
                       + " Order By SI_ID ASC";
               System.out.println(new Date().toString() + "\tDeleting batch number:" + (i + 1));
               System.out.println(new Date().toString() + "\t Retrieving the instances list...");
               IInfoObjects deleteObjects = iStore.query(deleteObjectsQuery);
               System.out.println(new Date().toString() + "\t Deleting...");
               for (int k = 0; k < deleteObjects.size(); k++){
                    lastInstanceProcessedID = ((IInfoObject)deleteObjects.get(k)).getID();
                    deleteObjects.delete((IInfoObject)deleteObjects.get(k));
               }
               System.out.println(new Date().toString() + "\t Comitting...");
               iStore.commit(deleteObjects);
               System.out.println(new Date().toString() + "\t Completed batch number:" + (i + 1));
            }
        } catch (SDKException ex) {
            ex.printStackTrace();
        } finally {
            if (enterpriseSession != null)
                enterpriseSession.logoff();
        }
        System.out.println("Finished!");
    }
}

Compiling

Compiling from command line
http://bukhantsov.org/2012/01/compiling-and-running-bo-sdk-java-tool-from-command-line/

Compiling from Eclipse
http://bukhantsov.org/2011/08/getting-started-with-businessobjects-java-sdk/