Managing ETL Process for Threshold Analyzer Utility

7.9 Managing ETL Process for Threshold Analyzer Utility

For inserting and updating records into the

KDD_TA_ML_DATA,
                KDD_TA_BC_DATA,

and KDD_TA_TC_DATA tables, there are two shell scripts that are used to call the database procedures. These are:

run_insert_ta_utility.sh – This script calls theP_TA_ML_INSERT_BREAKS, P_TA_BC_INSERT_BREAKS, and P_TA_TC_INSERT_BREAKS procedures, which insert data into the KDD_TA_ML_DATA, KDD_TA_BC_DATA, and KDD_TA_TC_DATA tables, respectively, based on the CREAT_TS of the events in relation to the LAST_RUN_DT from KDD_TA_LAST_RUN (values for RUN_TYPE_CD are ML_I, BC_I, and TC_I).
run_update_ta_utility.sh – This script calls the P_TA_ML_UPDATE, P_TA_BC_UPDATE, and P_TA_TC_UPDATE procedures, which updateQLTY_RTNG_CD in the KDD_TA_ML_DATA, KDD_TA_BC_DATA, and KDD_TA_TC_DATA tables, respectively, for any Review closed since the last run based on LAST_RUN_DT from KDD_TA_LAST_RUN (values for RUN_TYPE_CD are ML_U, BC_U,and TC_U). The CLS_CLASS_CD value from KDD_REVIEW is used as the new QLTY_RTNG_CD.

The log for these scripts is written in the run_stored_procedure.log file under the <OFSAAI Installed Directory>/database/db_tools/logs directory.

The LAST_RUN_DT column in the KDD_TA_LAST_RUN table is only updated for inserts and updates if at least one or more records were inserted or updated. The LAST_RUN_DT column is not updated for significant errors that resulted in no records being updated. These scripts are a part of the database tools and reside in the<OFSAAI Installed Directory>/database/db_tools/bin directory.

You can run this utility anytime, that is, it is not necessary to run this utility during specific processing activities.

Running Threshold Analyzer

To run the threshold analyzer, follow these steps:

Go to the ATOMIC schema and execute the following query:

select distinct (creat_ts)
from kdd_review t
where t.review_type_cd = 'AL'
and SCNRO_DISPL_NM <> 'User Defined'
and PRCSNG_BATCH_NM = 'DLY';

Set date as per dates returned from above SQL. Say CREATE_TS is 05/21/2013 in kdd_review table than we will set a date 05/17/2013 (Friday of last week) from the $FICHOME/database/ db_tools/bin folder.

Execute the following command:

start_mantas_batch.sh DLY
set_mantas_date.sh 20130517 --(Friday of last week)

Execute DRM utility to create partitions, refer to Table 34 on page 119 for parameter values: run_drm_utility.sh <Partition> <Weekly> <schema> <Table name> <drm_weekly_proc_fl>
There should be different variations for each Oracle product. For example:
```
run_drm_utility.sh P W ATOMIC KDD_TA_ML_DATA N
run_drm_utility.sh P W ATOMIC KDD_TA_BC_DATA N
run_drm_utility.sh P W ATOMIC KDD_TA_TC_DATA N
```
Execute the following Insert and Update Threshold Analyzer scripts from $FICHOME/database/ db_tools/bin folder:
```
run_insert_ta_utility.sh
run_update_ta_utility.sh
```
Repeat the above process if you have more than one date returned from the query in Step1.