In environments where BDSSA ETL has been running regularly for months or years, and where a BDSSA upgrade has not occurred recently, the amount of ETL metadata which builds up can be significant.

This can result in the end-to-end time for a BDSSA upgrade, or patch install, taking a lot longer than it needs to be as the ODI scenario and its history is deleted from the BSARA_ETL_WORK DB.

How do I cleanup up this data in advance to make the BDSSA upgrades run faster?
 

The ETL uses the Oracle ODI to run the ETL (even on SQL Server, this is just a data transfer tool).  The Oracle ODI command to purge the ETL activity logs is called OdiPurgeLog and is run from the <REPORTS>/etl/bin/startcmd.[sh|bat] or <REPORTS>/shared/odi/bin/startcmd.[sh|bat] command (depending on the version of BDSSA and operating system of the BDSSA server).  The command syntax is noted below:

OdiPurgeLog [-FROMDATE=<fromdate>] [-TODATE=<todate>] [- 
CONTEXT_CODE=<context_code>] [-USER_NAME=<user_name>] [- 
AGENT_NAME=<agent_name>] [-PURGE_REPORTS=<0|1>] [- 
SESSION_STATUS=<D|E|M>] 

Description 
Allows the purge of the execution logs. 
If -FROMDATE is omitted, the purge is done starting with the oldest session. 
If -TODATE is omitted, the purge is done up to the current date 
If both parameters are omitted, the whole log is purged.  
The date format is yyyy/MM/dd

The following example command run on a Linux BDSSA Server would purge all the ETL execution logs up to Jan 31st 2013:

# ./startcmd.sh OdiPurgeLog "-TODATE=2013/01/31 00:00:00"
OracleDI: Starting Command : OdiPurgeLog -TODATE=2013/01/31 00:00:00 ...

The Windows equivalent is:

>startcmd.bat OdiPurgeLog "-TODATE=2013/01/31 00:00:00"
OracleDI: Starting Command OdiPurgeLog "-TODATE=2013/01/31 00:00:00"