Share this page

# Purge Stale Data Using the PPM Purge Tool

This section provides detailed instructions on how to use the PPM Purge Tool to purge stale database data.

To purge stale data,

1. Copy and extract the `<PPM_Home>/bin/purge/ppm-purge.zip` package to a different server than the DB server.

   For security reasons, it is highly recommended that this tool should be installed on a dedicated server that only the tool users with both the SYS DBA and administrator access grants can have access to, rather than on an end-user's machine.

2. (Required if you use BusinessObject on PPM) Stop the capture process of Oracle Streams.

   For information about how to stop a capture process, see the Oracle documentation.

3. Stop the PPM Server. If in clustered configuration, stop all nodes in the cluster.

4. Set the JAVA_HOME value.

   - To set the JAVA_HOME value in Windows,

     Note: The steps described in the following procedure are for Windows 7. The exact steps may differ, depending on your Windows operating system.

     1. Open the Control Panel.

     2. Open the System window.

     3. Click Advanced system settings in the navigation pane.

        The System Properties dialog box opens.

     4. Click Environment Variables on the Advanced tab.

     5. Under System Variables, click New.

        The New System Variable dialog box opens.

     6. In the Variable name box, type `JAVA_HOME.`

     7. In the Variable Value box, type the full Java install directory path, and click OK.

   - To set the value of JAVA_HOME in DOS, run the following:

     `set JAVA_HOME=<JDK_Home>`

   - To set the value of `JAVA_HOME` in UNIX using the Bourne shell (SH, BASH, or KSH), run the following:

         JAVA_HOME=<JDK_Home>; export JAVA_HOME

   For more information about setting JAVA_HOME value, see the Verifying that the JAVA_HOME Environment Variable Is Set section in the Installation and Administration Guide.

5. Navigate to the `<purge_home>` directory that you extracted in step 1, and start the purge tool server.

   Run the following script to star the purge tool server:

   - On Windows system, run the `kPurgeStart.bat` file
   - On Unix system, run the `kPurgeStart.sh` file

6. Open a web browser, type `http://localhost:8080` or a specified IP address with access control in the Address bar, and press Enter.

   Note:

   - We recommend you use domain name to access the Purge Tool web server, instead of using IP address.
   - The default port is `8080`. To use a different port, you can configure the port value in the `<ppm_purge_home>/jetty/start.d/http.ini` file.
   - By default, the Purge Tool allows access from the localhost only. However, you can control whether you allow remote access to the Purge Tool or not. For more information, see Restrict Remote Access to the PPM Purge Tool to Specified IP Addresses.

   The PPM Purge Tool login page opens in your browser.

7. Configure PPM Server and Oracle database related parameters.

   Go to the `<ppm_purge_home>/jetty/conf` directory and configure the parameters in the `ppminfo.conf` file as described in the table below.

   Note: Only the DB account configured in the `ppminfo.conf` file has access to the PPM Purge Tool.

   Parameter name | Description | Sample Value
   --- | --- | ---
   DB_USERNAME | Username of the PPM database schema for connecting the Oracle database. It is usually the same as the value of the DB_USERNAME parameter in the `server.conf` file. | DB_USERNAME= `knta`
   RMI_URL_LIST | RMI URL of the PPM Server. When there are multiple PPM Server nodes, the RMI URLs are separated by commas. See the RMI_URL parameter value in the `server.conf` file. | RMI_URL= `rmi://localhost :1099` `/KintanaServer`
   JDBC_URL | JDBC URL for the Oracle database. See the JDBC_URL parameter value in the `server.conf` file. | JDBC_URL= `jdbc:oracle:thin` `:@localhost:1521` `:ORCL`

8. Provide the DB account information you just configured in the DB User Name and DB Password fields.

9. Click Login.

   The PPM Purge Tool opens to its Database Statistics Overview tab.

   The Database Statistics Overview tab dynamically displays the current statistics of all entities found in the Oracle database.

   There are two date types available for all the entities: Creation date (default) and Update date. You can choose to display the database statistics overview by Creation date or Update date.

   The entities found in the Oracle database are categorized into two statuses for simplification purpose: Open and Closed. The following table describes specific statuses categorized as Closed or Open for each entity type:

   Entity Type | Closed | Open
   --- | --- | ---
   Request (status) | Cancelled Closed Closed (Approved) Closed (Not Approved) Closed - Failed Closed - Rejected Closed – Successful | All the other Request statuses
   Project (status) | Cancelled Closed Closed (Approved) Closed (Not Approved) Closed - Failed Closed - Rejected Closed – Successful | All the other Project statuses
   Staffing Profile (status) | Completed Cancelled | All the other Staffing Profile statuses
   Program (status) | Completed Cancelled | All the other Program statuses
   Scenario Comparison (active_flag) | N/A | All the other Scenario Comparison statuses
   Timesheet (status) | CANCELLED CLOSED | All the other Timesheet statuses
   Package (status) | Cancelled Closed [Failure] Closed [Mixed] Closed [Success] | All the other Package statuses
   Release (status) | Closed | All the other Release statuses
   Portfolio | N/A | N/A

10. Go to the Run PPM Purge Tool tab.

    You can define and add your purge criteria by specifying desired values for the fields on this page. You can add as many criteria as you like.

    Note: In case there are any specific data that you want to retain, go to the Allow List tab to specify your retention criteria. Otherwise leave the Allow List tab empty.

11. On the Purge Criteria tab, define system level or entity level purging criteria as described in the following table.

    Field | Description
    --- | ---
    Date type | Specify date type for all entities in the Oracle database by selecting Creation Date or Update date from the Date type drop-down list.
    Criteria | Allows you to define a system level or an entity level purging criteria: To define a system level purging criterion, select All. To define an entity level purging criteria, select an entity from the list of supported entities: Request Project Timesheet StaffingProfile Program ScenarioComparison Package Portfolio Release To specify purging criteria for multiple entities, you need to add them one at a time.
    Entity Type | Allows you to select All or a specific entity type (if available) for the entities you specify in the Criteria field: Request, Project, Program, or Package. Note that the available entity type options vary with the entity you select. Note: This field is not applicable for the following entities: Timesheet, StaffingProfile, ScenarioComparison, Protfolio, and Release. To specify multiple entity types for an entity, you need to add them one at a time.
    Status | Allows you to multi-select statuses for the entity type you want to purge. The available statuses vary with entities you specify in the Criteria field. You can click Check all or Uncheck all to select or deselect all options. Note: The Status field is not applicable to the Portfolio entity. The Status field is not applicable when you select All in the Entity Type field.
    Date Before | Click the calendar icon to select a date. This defines that you want to purge data with their creation date or update date earlier than the specified date (not including data created or updated on the selected date). You can also manually enter a valid date value.

12. After you have specified a criterion, click Add.

    To add multiple entities and/or entity types, you need to add them one at a time.

    Note: If you add a criteria that was already added, the new one will override the existing one.

13. If there are any specific data that you want retain from the purging criteria you already specified, go to the Allow List tab and specify the retention criteria.

    Otherwise, leave the Allow List tab empty.

14. Review the purging criteria and click Next.

    The purge tool starts to identify and add tags to purgeable data.

15. When the purge tool finished identifying and tagging purgeable data, click Next.

    The summary page displays an overview of purgeable entities and non-purgeable entities.

    Click View Details to view entities that have dependencies.

16. (Optional) Run some SQL scripts in the database to view detailed lists of purgeable entities.

    Detailed lists of purgeable entities are not displayed here due to potential large amount of the purgeable entities. However, you can run some SQL scripts in the database to view the detailed lists.

    Note: Skip this step if you do not need to view the detailed lists of purgeable entities.

    1. Log on to the database.

    2. Run SQL scripts as described in the table below to view the detailed list of purgeable entities for a concerning entity type.

       Entity Type | Run the following script
       --- | ---
       Request | `SELECT * FROM KCRT_REQUESTS T1 WHERE EXISTS(SELECT 1 FROM P_REQUEST T2 WHERE T1.REQUEST_ID=T2.REQUEST_ID)`
       Project | `SELECT * FROM PM_PROJECTS T1 WHERE EXISTS(SELECT 1 FROM P_PROJECT T2 WHERE T1.PROJECT_ID=T2.PROJECT_ID)`
       Timesheet | `SELECT * FROM TM_TIME_SHEETS T1 WHERE EXISTS(SELECT 1 FROM P_TIME_SHEET T2 WHERE T1.TIME_SHEET_ID=T2.TIME_SHEET_ID)`
       Staffing Profile | `SELECT * FROM RSC_STAFFING_PROFILES T1 WHERE EXISTS(SELECT 1 FROM P_STAFFING_PROFILE T2 WHERE T1.STAFFING_PROFILE_ID=T2.STAFFING_PROFILE_ID)`
       Financial Summary | `SELECT * FROM FM_FINANCIAL_SUMMARY T1 WHERE EXISTS(SELECT 1 FROM P_FINANCIAL_SUMMARY T2 WHERE T1.FINANCIAL_SUMMARY_ID=T2.FINANCIAL_SUMMARY_ID)`
       Portfolio | `SELECT * FROM PFM_PORTFOLIOS T1 WHERE EXISTS(SELECT 1 FROM P_PORTFOLIO T2 WHERE T1.PORTFOLIO_ID=T2.PORTFOLIO_ID)`
       Program | `SELECT * FROM PGM_PROGRAMS T1 WHERE EXISTS(SELECT 1 FROM P_PROGRAM T2 WHERE T1.PROGRAM_ID=T2.PROGRAM_ID)`
       Release | `SELECT * FROM KREL_RELEASES T1 WHERE EXISTS(SELECT 1 FROM P_RELEASE T2 WHERE T1.RELEASE_ID=T2.RELEASE_ID)`
       Package | `SELECT * FROM KDLV_PACKAGES T1 WHERE EXISTS(SELECT 1 FROM P_PACKAGE T2 WHERE T1.PACKAGE_ID=T2.PACKAGE_ID)`

17. Go back to the pre-processing summary page of the PPM Purge Tool, click Next.

    The purge tool moves to Step 3. Start Purge page.

18. Follow the screen instructions to create the PPM_PURGE_DIR directory and grant read/write privileges to it.

    The PPM_PURGE_DIR directory will hold the external tables that the purge tool created on each run to store the purged data. For example, you can run the following to replace the `/home/oracle/oracle11g/product/11.2.0/dbhome_1/backup` directory with your own directory on the Oracle database server:

        CREATE DIRECTORY PPM_PURGE_DIR AS '//home/oracle/oracle11g/product/11.2.0/dbhome_1/backup';

        GRANT READ, WRITE ON DIRECTORY PPM_PURGE_DIR TO;

19. Check that PPM server or all nodes in the PPM cluster are stopped, and provide comments for the current purge operation in the Messages text box.

20. Click Start Purging.

    Wait for the current purging operation to finish.

    Caution: We strongly recommend you not to click Logout or move to other tab pages while the purging is in progress.

21. Click Next when purging finishes.

    The Purge results <Purge_ID> page displays.

22. To view or download historical purge criteria and summary reports, go to the Purge History tab.

    By clicking Download XML, you can download an XML file that contains all purge criteria that you or another DB administrator specified for that purge operation. You can find the XML file as follows: `C:/Users/<User>/Downloads/purgeCriteria_<purge_ID>.xml`. Below is the content of the XML file for purge ID 30020:

    By clicking Summary Report, you can view a snapshot of the database statistics for a purge operation, like the following.

23. (Required if you use BusinessObject on PPM) After the purging is done, perform a full ETL.

    For information about how to perform a full ETL, see the Operational Reporting Administrator’s Guide.

https://admhelp.microfocus.com PPM 8942
Tips to improve your search results... OpenText™ Project and Portfolio Management Help Center PPM Help 26.2-26.3 All View Local Help Center View Online Help Center Version: