FDMEE: System Maintenance Tasks blog series where we are going to cover the Maintain Process Tables script.
In this fourth post, we will focus on the ‘Maintain Process Tables’ task and will see how to manually execute ‘Maintain Process Tables’ purge script.
Important Note:
- This post has been written and associated activities have been demonstrated on FDMEE version 11.1.2.4.210.
- There is no outage required on the FDMEE application to execute these purge scripts. But it is advisable not to run any data loads during the purge activity.
- Based on the data retention policy of your organization, you should pre-decide how many days of data you want to retain for FDMEE Application folders.
- Don't forget to take complete FDMEE schema backup before attempting to run these purge scripts.
The purpose of running Maintain Process Tables script is to purge old/historical Process IDs and related details from associated execution tables in FDMEE. This will not only reduce the overall FDMEE schema size but also help improve the performance of your FDMEE application.
The “Maintain Process Tables” script cleans up the Process IDs in the Process details related FDMEE tables by the number of days you enter as input parameter while running the script.
It deletes entries from following FDMEE execution tables (AIF_ tables):
- AIF_PROCESSES
- AIF_PROCESS_DETAILS
- AIF_PROCESS_LOGS
- AIF_PROCESS_PARAMETERS
- AIF_PROCESS_PERIODS
- AIF_PROCESS_STEPS
- AIF_BAL_RULE_LOADS
- AIF_BAL_RULE_LOAD_PARAMS
- AIF_BATCH_JOBS
- AIF_BATCH_LOAD_AUDIT
- AIF_TEMP
Before proceeding further, with purging steps, I would suggest you to check and note down the pre-purge FDMEE schema size to see how much space has been freed up post purge activity.
Step-by-Step process to execute ‘Maintain Process Tables’ purge script
1- Log in to Workspace with Admin credentials.
2- Open FDMEE console (Data Management) from Workspace and on the Workflow tab, under Other, select Script Execution:
3- In Script Execution, and then in Custom Script Group, select System Maintenance Tasks.
4- From the Scripts grid, select Maintain Process Tables script and Click Execute.
5- It will prompt to enter the following parameter value on the Execute Script screen.
Days to keep records
Enter the number of days you want to retain entries for in FDMEE Process Tables from today’s date. For example, if you want to delete all the entries except all 3 months (90 days) entries in FDMEE Process Tables, enter 90 in Days to keep records.
Note: Maintain Process Tables scrip takes some time to execute so it’s advisable not to purge too much in one go.
6- Keep Execution mode selected as Online. The online method processes the report immediately.
Execution modes:
online — ODI processes the data in sync mode (immediate processing).
offline — ODI processes the data in async mode (runs in background).
7- Optionally, If you want to schedule the purge script run rather than running it instantly, Click on Schedule option and enter the scheduling details as per your requirement.
8- If you want to instantly run the purge script, then directly click on OK (not on the Schedule option).
You will see a message informing the process ID of your script run.
9- To see the status of your script execution, click on the Process Details link under the Workflow tab—Monitor—Process Details.
Click on the Show button for the Process ID of your script run to open the corresponding log file.
In the log file, look for the message "Maintain Process Tables: Completed" to confirm that script has run successfully without any error. If you encounter any errors, troubleshoot them based on the cause mentioned in the log file. In our case, we see no issue in the script execution.
Once you are done with running the Maintain Process Tables purge script, you can check your FDMEE schema size to see how much space has been cleared as a result of FDMEE Process Tables purging.
Here it’s important to note that after running the FDMEE Process Tables purge script, your DBA needs to do FDMEE tables reorganization activity in order to reclaim freed space.
Actually, when you delete the data from the Oracle tables, the data blocks will go onto the free list for that table. That free space will be used for the subsequent insert and update operations into the table. You need to perform table reorganization to reclaim that free space.
Note: Reorganization of tables requires outage in your Hyperion environment so you have to stop all your application services prior to the reorganization activity to be performed by your application DBA.
Post tables reorganization by DBA, compare pre and post purge size of your FDMEE schema to see how much has the schema size actually reduced.
That's all for this post.
Maintain Process Tables purge script can also be automated to run using Executescript batch file located in the folder:
<drive>\Oracle\Middleware\user_projects\epmsystem1_fdm\FinancialDataQuality.
We will cover the automation of Maintain Process Tables purge script in a separate post of this blog series.
No comments:
Post a Comment