EPM 11.1.2.3 – Purging LCM migration status report

Prior to 11.1.2.3 it was possible to purge migration status reports from within Shared Services.

In 11.1.2.3 for some reason the option has disappeared even though the documentation states it should be possible.

I am not sure if it was mistakenly removed with the change of Shared Services being embedded into workspace and if so then maybe it will return, delving into the underlying code the java server page that used to be called purgeMSR.jsp still exists and so do the Java classes surrounding it.

So what are the options to purge the data, well in 11.1.2.3 a new Shared Services registry setting was introduced which defines after how many days the data will automatically be purged, maybe this is the reason why the option was removed form Shared Services? It would nice to have both options if that was the case.

If a registry report is run then under Shared Services Product you will see the new property MSR.PURGE.EARLIER.TO.DAYS which has a default value of 30.

 

Analysing the Shared Services logs highlights the purge property being read from the registry and then being executed.

Please note the checking and purging of the migration status data is automatically run every 24 hours.

Changing the number of days to purge value can be achieved by a through a few different ways.

A properties file can be exported from directly from the registry through Shared Services:

The properties file can then be edited and updated with the new required property value.

Once updated use the “Import after Edit” option to load the new value back into the Shared Services registry.

The value can also be viewed and updated using the epmsys_registry command line tool.

To view the current value use:

epmsys_registry.bat view SHARED_SERVICES_PRODUCT/@MSR.PURGE.EARLIERTO.DAYS

To update the value use:

epmsys_registry.bat updateproperty SHARED_SERVICES_PRODUCT/@MSR.PURGE.EARLIERTO.DAYS <newvalue>

If you are happy with just using this method then that’s good but I wanted to look further into what was being run behind the scenes.

After spending time researching I managed to track down the code that the purge runs though I think it is first worth pointing out how the LCM migration data is stored.

In the Shared Services relational database/schema there are three tables which store all the migration information.

LCM_MIGRATION

This table is used for inserting the information related to migration. Whenever a migration is requested from LCM Command utility or LCM UI this table is updated first with the migration specific information

LCM_MIGRATION_TASK

This table contains details of the all the tasks in a single migration. A single migration can contain multiple tasks

LCM_MIGRATION_TASK_DETAILS

Detailed failure information for each of the tasks in a migration. This table is used for populating the MSR details page in the UI for failed migrations

If you are interested in understanding the details of each of the fields in the tables then I recommend checking the Relational Data Models document.

When a purge is run the following SQL statements are run against the LCM migration tables.

DELETE FROM LCM_MIGRATION_TASK_DETAILS WHERE MIGRATION_ID IN ( SELECT MIGRATION_ID FROM LCM_MIGRATION WHERE PKG_STATUS IN ('F', 'S','W') AND START_TIME <= ?)

DELETE FROM LCM_MIGRATION_TASK WHERE MIGRATION_ID IN ( SELECT MIGRATION_ID FROM LCM_MIGRATION WHERE PKG_STATUS IN ('F', 'S','W') AND START_TIME <= ?)

SELECT LOG_FILE, PCKGFILE_NAME FROM LCM_MIGRATION WHERE MIGRATION_ID IN (SELECT MIGRATION_ID FROM LCM_MIGRATION WHERE PKG_STATUS IN ('F', 'S','W') AND START_TIME <= ?)

DELETE FROM LCM_MIGRATION WHERE MIGRATION_ID IN ( SELECT MIGRATION_ID FROM LCM_MIGRATION WHERE PKG_STATUS IN ('F', 'S','W') AND START_TIME <= ?)

In each of the statements the question mark holds the time to purge from and any data older than the value passed in will be removed.

The “F”,”S” and “W” status values stand for “Failed”, “Successful” and “Warning”.

Even though the Migration Status Report displays the full date and time this is not the way it is stored in the relational tables.

The date in the tables is stored in Unix time which basically means the number of seconds that have elapsed since 00:00:00 January 1st 1970 not including leap seconds.

So if you are planning to run the SQL then you will also need to calculate the time which can be done by many different ways including SQL or there is even a website which will convert a date for you.

One of the SQL statements also selects the log files and package name files.

This is because each time a LCM migration is run a package xml file is generated which is basically the same as the migration definition file and if the migration is run by the command line utility a log file is created.

When a purge is run these files are also automatically deleted so if you are going to be running the above SQL then it is worth considering building into the process the removal of these files.

So there we go a couple of options to purge LCM migration data but I didn’t want to stop there and looked at tapping into the Java classes that are available.

The classes that are used by the purge routines can be found in lcmWeb.jar which can be extracted from the Shared Services web application.

The jar can be extracted from the interop.ear file or the foundation managed server temporary directory.

I created a very simple Java class which the value for the number of days to purge from can be passed in and the data is then automatically purged.

The value to be passed in:

• -1 - Deletes all migration data
• 0 - Deletes all migration data performed prior to today
• N - Deletes all migration data before a specified number of days from today

The class was then compiled.

I created a batch script to include the classpath to the necessary jar files and the EPM oracle instance variable which is required otherwise the Java code will not run successfully.

And that is all there is to it so now the LCM migration data can easily be purged from command line by calling the script passing in the purge value.