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.

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.

Back to top

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.bat file (or 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.bat (or sample_onetime_batch.sh) file.

    Example:

    call sample_onetime_batch.bat 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.

Back to top

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).

Back to top

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.

Back to top

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

Back to top

See also: