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
https://bukhantsov.org/2012/01/compiling-and-running-bo-sdk-java-tool-from-command-line/

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

8 thoughts on “BO XI 3.1 SP3 issue related to daylight savings time change

  1. Balaji

    Hi Can I have this file compiled in a JSP format that I can deploy inside the InfoViewApp

    Thanks for your help in advance

    Thanks
    Balaji Karunakaran

    Like

    Reply
      1. Balaji

        Hi,

        Thanks for the update !
        Even I got a chance to see that SAP code for me we have 17 million recodes and their code I need to run manually.

        I see your code as a batch that runs automatically till it ends.
        That is the reason That I asked your code in JSP file.
        More over I’m not that Tech on the Java Side

        Thanks
        Balaji K

        Like

      2. dmytro Post author

        What do you mean by running manually?

        Please find JSP here
        https://bukhantsov.org/tools/removefailedinstances.zip

        Note that nothing will be displayed in the browser until it is finished.

        You can see the progress in
        [Business Objects]\Tomcat55\logs\stdout.log

        Curious how long will it take… I had a similar issue with 15 millions instances, it took about a week or even more. Web session can expire so I assume you may need the command line tool. Let me know if you want to get the compiled JAR file.

        Like

      3. Balaji

        Hi,

        Thanks for the Prompt reply and support. Highly appropriated.
        Please do help me with the command line tool as well

        Thanks
        Balaji Karunakaran

        Like

      4. dmytro Post author

        I added jar to the same archive.

        The format of the command:

        java -jar JavaTool.jar localhost Administrator "" secEnterprise
        

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s