Share this page

# Run incremental ETL jobs

The load script that you run during Operational Reporting deployment performs a full ETL to load all OpenText PPM data into the Operational Reporting database schema. Incremental ETL jobs are scheduled to run automatically every 24 hours thereafter. These incremental ETL jobs cover the day-to-day updates for the OpenText PPM data tables.

In this topic:

## Run incremental ETL jobs automatically

The incremental ETL job that runs automatically every 24 hours is named PPM_ETL_BATCH_JOB. You can use an Oracle command to reschedule or change the frequency of the PPM_ETL_BATCH_JOB run.

For information on how to reschedule the PPM_ETL_BATCH_JOB, see the Oracle Database Online Documentation.

## Run incremental ETL jobs manually

To manually run an incremental ETL batch job immediately:

1. Navigate to the `<`Op_Report_Home`>/install/sample` directory.

2. Open the `sample_onetime_batch.sh` file in a text editor, and then replace the parameter placeholders with valid values, as shown in the following table.

   Parameter | Value
   --- | ---
   Reporting DB Schema Name | Operational Reporting database schema name
   Reporting DB TNS Name | Operational Reporting database TNS name
   ETL BATCH JOB NAME | Any job name Example: TM_ETL_DAILY

3. Run the `sample_onetime_batch.sh` file.

   Example:

   `call sample_onetime_batch.sh REPORTING SCHEMA ORASID TEST_ETL_JOB`

4. When you are prompted, type the password for the Operational Reporting database schema.

5. To check the job progress, see Check ETL job progress.

## Check ETL job progress

To check the job progress, you can query the `RPT_EVENT_LOG_DETAIL` table, as follows:

    select event_time, lead(event_time,1) over
    (order by event_log_id desc), round((event_time - lead(event_time,1) over (order by event_log_id desc))*24*60 ,2) durations,
    t1.*
    from rpt_event_log_detail t1
    order by event_log_id desc;

where the value of `event_time` must contain both date value and time value.

If the value of `event_time` does not contain date value or time value, configure the settings in Oracle client tool as follows:

1. Open Oracle SQL Developer.

2. Click Tools > Preferences.

3. In the left pane, expand Database (if it is not already expanded).

4. Under Database, click NLS.

5. Set the date format to: YYYY-MM-DD HH24:MI:SS

6. Click OK.

To view the status of an incremental ETL job, you can query the job control tables (RPT_ETL_JOB and RPT_EVENT_LOG_DETAIL tables).

## Verify successful incremental ETL jobs

To determine whether the last incremental ETL job run completed successfully, run the following:

    select event_time, lead(event_time,1) over
    (order by event_log_id desc), round((event_time - lead(event_time,1) over (order by event_log_id desc))*24*60 ,2) durations,
    t1.*
    from rpt_event_log_detail t1
    order by event_log_id desc;

    SELECT * FROM rpt_etl_job ORDER BY etl_job_id desc;

where the value of `event_time` must contain both date value and time value.

If the value of `event_time` does not contain date value or time value, configure the settings in Oracle client tool. For detailed steps, see If the value of event_time does not contain date value or time value, configure the settings in Oracle client tool as follows: .

Note: We recommend that you delete the contents of the RPT_EVENT_LOG_DETAIL and
RPT_ETL_JOB_ORDERr tables at least once a month to prevent them from becoming too large. You must delete the contents manually.

## Check ETL job count

To see the count of incremental ETL jobs, run the following script:

    select * from rpt_event_log_detail where msg like '%The count of%';

It would return message like the following:

    The count of CM_ETL_DAILY:1
    The count of RM_ETL_DAILY:2
    The count of TM_ETL_DAILY:3
    The count of FM_ETL_DAILY:0
    The count of PM_ETL_DAILY:1

## See also

- (Optional) Force a full ETL

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: