Cleaning up Large Numbers of Instances

former_member203619 · ‎03-18-2013

One issue that occurs occasionally is when a report ends up with a large number of instances. Each instance takes up space on the hard drive and can also making finding a specific instance difficult. The solution within Enterprise is to set a limit on the number of instances, or to use the history page to clean up the extra instances. Unfortunately, if you have tens of thousands of instances, the above solutions can be very time consuming (Setting a limit will cause the extra instances to be deleted - but it can take a long time, and will slow down the CMS server).

The scripts below show how to delete large amounts of instances quickly while limiting the load on the CMS server. The way this is done is to break up the queries into small blocks (500-1000).

Warning

The code given below can be very destructive if not used properly. Please ensure that you have made a backup of your CMS database and your Input and Output FRS prior to running any code.

Note:

The java code in these scripts is based off the batch scripting template found here: http://scn.sap.com/docs/DOC-38620

For other scripts and information on how to run these scripts see here:

shawn.penner/blog/2013/06/04/scripts-and-samples

Delete Mass Instances - Basic

This first script is the simplest form of the mass deletion script. It simply queries for all instances of a specific report and deletes them.

Notes:

•You will need to change the parentReportID value to the SI_ID of your report template.
•You will need to change the username, password, and CMS name to the values specific to your enterprise server.

Delete Mass Instances

<%@ page import = "com.crystaldecisions.sdk.exception.SDKException,
com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
java.util.*"
%>
<%
// User Credentials
String username = "Administrator";
String password = "MyPassword";
String cmsname = "MyEnterpriseServer";
String authType = "secEnterprise";

String parentReportID = "12345";
int batchSize = 500;

IEnterpriseSession enterpriseSession = null;
IInfoStore infoStore;
IInfoObjects boInfoObjects;

// Log onto Enterprise
enterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authType);
infoStore = (IInfoStore)enterpriseSession.getService("", "InfoStore");

// The SI_ID to start at when searching
int max_id = 0;

for(;;) {

// Loop through all objects - only retrieve the SI_ID in order to make the query run faster.
boInfoObjects = (IInfoObjects)infoStore.query("Select TOP " + batchSize + " SI_ID FROM CI_INFOOBJECTS WHERE SI_INSTANCE=1 AND SI_PARENTID= " + parentReportID + " AND SI_ID > " + max_id + " ORDER BY SI_ID ASC");

// If there are no more objects then we're done.
if(boInfoObjects.size() == 0)
break;

for(Iterator boCount = boInfoObjects.iterator() ; boCount.hasNext() ; ) {
IInfoObject boObject = (IInfoObject)boCount.next();

max_id = boObject.getID();
boObject.deleteNow();
boInfoObjects.delete(boObject);

}
infoStore.commit(boInfoObjects);
}
out.println("Completed");

%>

Delete all Failed Instances

This script was designed to delete all failed instances from all reports on a system. It uses the Infoobject query to filter based on the status of the instance and will only remove instances that show as failed. Stalled or long-running instances will not be removed by this script.

Notes:

You will need to change the username, password, and CMS name to the values specific to your enterprise server.

Delete all Failed Instances

<%@ page import = "com.crystaldecisions.sdk.exception.SDKException,
com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
java.util.*"
%>
<%
// User Credentials
String username = "Administrator";
String password = "MyPassword";
String cmsname = "MyEnterpriseServer";
String authType = "secEnterprise";

int batchSize = 500;

IEnterpriseSession enterpriseSession = null;
IInfoStore infoStore;
IInfoObjects boInfoObjects;

// Log onto Enterprise
enterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authType);
infoStore = (IInfoStore)enterpriseSession.getService("", "InfoStore");

// The SI_ID to start at when searching
int max_id = 0;

for(;;) {

// Loop through all objects - only retrieve the SI_ID in order to make the query run faster.
boInfoObjects = (IInfoObjects)infoStore.query("Select TOP " + batchSize + " SI_ID FROM CI_INFOOBJECTS WHERE SI_INSTANCE=1 AND SI_SCHEDULE_STATUS=3 AND SI_ID > " + max_id + " ORDER BY SI_ID ASC");

// If there are no more objects then we're done.
if(boInfoObjects.size() == 0)
break;

for(Iterator boCount = boInfoObjects.iterator() ; boCount.hasNext() ; ) {
IInfoObject boObject = (IInfoObject)boCount.next();

max_id = boObject.getID();
boObject.deleteNow();
boInfoObjects.delete(boObject);

}
infoStore.commit(boInfoObjects);
}
out.println("Completed");

%>

Delete Mass Instances with Date and Time Filtering

This script shows how to delete all instances of a specific report that were created within a certain time period. However, it can be difficult to determine what values to use when trying to filter by date/time. The reason it can be so difficult is because the values you see listed in query builder may not be the values that are stored in the CMS database. The reason for this is when query builder returns back a value that is a date/time, it automatically converts it to the date/time for your timezone.

e.g.
If the value stored in the CMC database is March 19th, 2013 4:19:00 AM, and you are located in a PST/PDT timezone, querying for that value through query builder will return March 18th, 2013 9:19 PM.

However, any value that you put in your query will not be converted to UTC/GMT, so you need to make sure you use the UMT value in your query.

For example:

e.g.
1. Assume that you are located in the PST timezone (UTC/GMT - 7 hours)
2. You query for a report and see that the SI_SCHEDULEINFO.SI_STARTTIME of your report is 3/18/13 9:19:00 PM
3. This is equal to March 18th, 2013 9:19 PM
4. Converting this to UMT time gives March 19th, 2013 4:19:00 AM
5. To use this value in an infoobject query, you would need to use:

Select * from CI_INFOOBJECTS where SI_SCHEDULEINFO.SI_STARTTIME = '2013.03.19.04.19.00'

Another alternative is to set your Tomcat Application server to run under UMT/GMT. This will allow you to use the date/time values being returned in your queries without having to do any math to determine the real value. Setting Tomcat to run under the GMT timezone can be done by adding the startup parameter:

-Duser.timezone=GMT

Notes:

You will need to change the username, password, and CMS name to the values specific to your enterprise server.
You will need to change the parentReportID value to the SI_ID of your report template.
You will need to change the startDate and endDate parameters to your desired start and end dates.

Delete Mass Instances with Date and Time Filtering

<%@ page import = "com.crystaldecisions.sdk.exception.SDKException,
com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
java.util.*"
%>
<%
// User Credentials

String username = "Administrator";

String password = "MyPassword";

String cmsname = "MyEnterpriseServer";

String authType = "secEnterprise";

String parentReportID = "12345";
String startDate = "2013.03.18.23.30.00"; // March 18th, 2013 11:30PM
String endDate = "2013.03.19.05.30.00"; // March 19th, 2013 5:30AM

int batchSize = 500;

IEnterpriseSession enterpriseSession = null;
IInfoStore infoStore;
IInfoObjects boInfoObjects;

// Log onto Enterprise
enterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authType);
infoStore = (IInfoStore)enterpriseSession.getService("", "InfoStore");

// The SI_ID to start at when searching
int max_id = 0;

for(;;) {

// Loop through all objects - only retrieve the SI_ID in order to make the query run faster.
boInfoObjects = (IInfoObjects)infoStore.query("Select TOP " + batchSize + " SI_ID, SI_SCHEDULEINFO.SI_STARTTIME FROM CI_INFOOBJECTS WHERE SI_INSTANCE=1 AND SI_PARENTID= " + parentReportID + " AND SI_SCHEDULEINFO.SI_STARTTIME > '" + startDate + "' AND SI_SCHEDULEINFO.SI_STARTTIME < '" + endDate + "' AND SI_ID > " + max_id + " ORDER BY SI_ID ASC");

// If there are no more objects then we're done.
if(boInfoObjects.size() == 0)
break;

for(Iterator boCount = boInfoObjects.iterator() ; boCount.hasNext() ; ) {
IInfoObject boObject = (IInfoObject)boCount.next();

max_id = boObject.getID();
boObject.deleteNow();
boInfoObjects.delete(boObject);

}
infoStore.commit(boInfoObjects);
}
out.println("Completed");

%>

Delete Mass Instances with Manual Deletion

This next script was written using VBScript and it was designed to delete instances when the files in the FRS have been set to read only, or when something is preventing Enterprise from deleting the associated file when the infoobject is removed. As it is written in VBScript, it doesn't follow the normal template linked at the top of the page. Also, be aware the VBScript is supported only for use on XI R2 and older versions. It will generally work on BOE XI R3, but is not supported.

The script is designed to take in parameters to specify logon credentials and the parent report ID.

e.g.
cscript MyScript.vbs cmsName Username Password Authentication ReportID

Notes:

You will need to change the frsRootPath to the base folder of your filestore
Default location for XI R2: "C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\FileStore\"
Default location for XI R3: "C:\Program Files (x86)\Business Objects\BusinessObjects Enterprise 12.0\FileStore\"
If you are running this on a 64-bit OS such as Windows 7, you will need to specify the 32-Bit version of cscript in order to run it succesfully. Otherwise you will encounter the error "ActiveX component can't create object: 'CrystalEnterprise.SessionMgr' ".

e.g.
%windir%\SysWOW64\cscript.exe deleteInstances.vbs localhost Administrator MyPassword secEnterprise "12345"

Delete Mass Instances with Manual Deletion

Dim frsRootPath

frsRootPath = "C:\Program Files (x86)\Business Objects\BusinessObjects Enterprise 12.0\FileStore\"

'Declare CMS logon variables
Dim CMS
Dim Usnername
Dim Password
Dim Authtype

'Declare variables for Enterprise Session
Dim oEnterpriseSessionMgr
Dim ceSession

' The Report to have its instances removed
Dim parentID

' Set Batch Size
Dim DEFAULT_BATCH_SIZE
DEFAULT_BATCH_SIZE = 500

if WScript.Arguments.Count <>5 Then
WScript.Echo "Incorrect Number of parameters - " & WScript.Arguments.Count
WScript.Quit()
end if

' Retrieve the passed in arguments
CMS = WScript.Arguments.Item(0)
Username = WScript.Arguments.Item(1)
Password = WScript.Arguments.Item(2)
Authtype = WScript.Arguments.Item(3)
parentID = WScript.Arguments.Item(4)

'Load the Enterprise Session Manager
Set oEnterpriseSessionMgr = CreateObject("CrystalEnterprise.SessionMgr")

'Logon to the CMS and create iStore object
Set ceSession = oEnterpriseSessionMgr.Logon(Username, Password, CMS, Authtype)
Set iStore = ceSession.Service("","InfoStore")

Dim oInfoObjects
Dim oInfoObject
Dim endOfLoop
Dim loopCount
endOfLoop = 0
loopCount = 0

dim filesys
dim numFiles
dim filebasepath
dim fname

Set filesys = CreateObject("Scripting.FileSystemObject")

WScript.Echo "Entering Loop"

Do Until endOfLoop = 1
'Retrieve the instances to be deleted
Set oInfoObjects = iStore.Query("SELECT TOP " & DEFAULT_BATCH_SIZE & " * FROM CI_INFOOBJECTS WHERE SI_INSTANCE=1 and SI_PARENTID= " & parentID)

' There are still instances to delete
If oInfoObjects.Count >0 Then
loopCount = loopCount + 1
WScript.Echo "Deleting Batch #" & loopCount
' Loop through and delete each instance in the batch
For each oInfoObject in oInfoObjects

   ' First retrieve the number files for this infoobject
   numFiles = oInfoObject.Files.Count

   ' Retrieve the base path for those files.
   ' The path will be in the form frs://Output/a_175/109/000/28079/
   filebasepath = oInfoObject.Properties("SI_FILES").Properties("SI_PATH").Value

   ' Strip off the first 12 characters to be left with /a_175/109/000/28079/
   filebasepath = Mid(filebasepath, 13)

   ' Replace the / slashes with \ slashes
   filebasepath = Replace(filebasepath ,"/", "\")

   ' Now add on the base path
   filebasepath = frsRootPath + "Output" + filebasepath

   ' Now loop through them all
   for i = 1 to numFiles
    fname = oInfoObject.Properties("SI_FILES").Properties(("SI_FILE" & i)).Value
    If filesys.FileExists((filebasepath + fname)) Then

     ' The optional true parameter will remove a file even if its read only.
     WScript.Echo "Manually Deleting File: " + filebasepath + fname
     filesys.DeleteFile filebasepath + fname, true
      End If
   next

oInfoObjects.Delete(oInfoObject)
Next
' Commit back to Enterprise
iStore.Commit oInfoObjects
Else
endOfLoop = 1
End If
loop

' There were no more instances so we can clean up
Set oInfoObjects = Nothing
Set oInfoObject = Nothing
Set iStore = Nothing
Set ceSession = Nothing
Set oEnterpriseSessionMgr = Nothing

WScript.Echo "Script Complete"

Pause and Resume all Instances

This last script doesn't actually remove any objects, but instead will loop through every report on an Enterprise system and set the status of all scheduled jobs to pause. This can be useful if you need to perform some maintenance or testing and you don't want any report jobs running while you do your tests. The script can be run a second time to unpause all the paused instances.

Notes:

You will need to change the username, password, and CMS name to the values specific to your enterprise server.

Pause and Resume all Instances

<%@ page import = "com.crystaldecisions.sdk.exception.SDKException,
com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
java.util.*"
%>
<%
// User Credentials
String username = "Administrator";
String password = "MyPassword";
String cmsname = "MyEnterpriseServer";
String authType = "secEnterprise";

// If this is true - then set all jobs to paused.
// If this is false - then set all paused jobs to running again.
Boolean doPause = true;

int batchSize = 500;

IEnterpriseSession enterpriseSession = null;
IInfoStore infoStore;
IInfoObjects boInfoObjects;

// Log onto Enterprise
enterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authType);
infoStore = (IInfoStore)enterpriseSession.getService("", "InfoStore");

// The SI_ID to start at when searching
int max_id = 0;

for(;;) {

// Loop through all objects - only retrieve the SI_ID in order to make the query run faster.
boInfoObjects = (IInfoObjects)infoStore.query("Select TOP " + batchSize + " * FROM CI_INFOOBJECTS WHERE SI_INSTANCE=1 AND SI_ID > " + max_id + " ORDER BY SI_ID ASC");

// If there are no more objects then we're done.
if(boInfoObjects.size() == 0)
break;

for(Iterator boCount = boInfoObjects.iterator() ; boCount.hasNext() ; ) {
IInfoObject boObject = (IInfoObject)boCount.next();
// Retrieve the instance, and then pause / unpause it as desired.
ISchedulingInfo boInstanceSched = (ISchedulingInfo) boObject.getSchedulingInfo();

// Values for getStatus: 1 = Complete, 3 = Failure, 8 = Paused, 9=Pending, 0=Running
// Values for setFlags: 1 = Pause, 0 = Resume, 8 = Retry
if (doPause) {
if (boInstanceSched.getStatus() == 9 || boInstanceSched.getStatus() ==0) {
 out.println("Pausing Instance: " + boObject.getID() + " ");
 boInstanceSched.setFlags(1);
}
} else {
if (boInstanceSched.getStatus() == 😎 {
 out.println("Resuming Instance: " + boObject.getID() + " ");
 boInstanceSched.setFlags(0);
}
}
max_id = boObject.getID();
}
infoStore.commit(boInfoObjects);
}
out.println("Completed");

%>