Scenario:
A user moved to a new Strategy Intelligence Server 9.x. There is still some data in the statistics tables with the old Intelligence Server machine name but that machine and Intelligence Server installation no longer exist. The user wishes to clean up the statistics database to remove reference to old deleted machine name.
The following procedure describes how to purge statistics for deleted Intelligence Server machine names:
Data from all the other statistics tables other than IS_SESSION_STATS can be deleted by performing a Statistics data purge from the project level for all the required projects. The DELETE SQL generated for a project level Statistics purge does not have a reference to the Intelligence Server machine name and just has reference to the project GUID. The WHERE clause generated is based on the date range chosen for the statistics purge. An example DELETE SQL is given below.
Sample Code/Error
Delete from IS_SCHEDULE_STATS where (PROJECTID ='B19DEDCC11D4E0EFC000EB9495D0F44F' OR PROJECTID IS NULL) AND (RECORDTIME BETWEEN To_Date('08-06-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND To_Date('09-06-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
To purge data corresponding to the non-existent Intelligence Server machine from the IS_SESSION_STATS table, the following SQL can be used:
Sample Code/Error
SELECT * FROM IS_SESSION_STATS WHERE SERVERMACHINE LIKE '<NAME_OF_MACHINE>%'
where <NAME_OF_MACHINE> is the physical machine name of the old Strategy Intelligence Server. The wild card '%' needs to be appended to the machine name entry as the data in the SERVERMACHINE column in IS_SESSION_STATS is of the form 'MACHINENAME:PORT NUMBER OF INTELLIGENCE SERVER OPERATION'. For example: 'TESTMACHINE:34952'
