Investigating Performance Problems with the Certification Process

The steps to query the log details for a specified batch are as follows:

1. Identify an integration batch for investigation
2. Edit the name of the Semarchy Repository schema in the provided script
3. Run the script using Oracle SQL Developer (or pgAdmin for PostgreSQL)
4. Save/Export the results (to Excel, for example)

Step 1: Identify an integration batch for investigation

First, identify the batch to be analyzed. Go into the workbench and choose 'Data Locations'. In the Data Locations tree on the left, open the 'Latest Integration Batches' folder in the appropriate data location. For example, the BATCHID for the integtarion batch which ran on Thu Jan 25 is 1347

Step 2: Edit the name of the Semarchy Repository schema in the provided script

In the provided script, the semarchy repository schema is called SEMARCHY_REPO. Change all 11 occurrences of this to the schema name for your repository.

Step 3: Run the script using Oracle SQL Developer (or pgAdmin for PostgreSQL)

For Oracle, SQL Developer prompts for a value for BATCHID. Enter the value identified in Step 1, or enter NULL to return ALL logs (beware this can be a very large result set).

For PostgreSQL, bind variables are not supported by pgAdmin4, so you must edit the query before executing. Comment out line 48, replace '<batchid>  in line 49 with your specified batchid, and uncomment line 49.

Step 4: Save/Export the results (to Excel, for example)

/* Query to return logging details in Semarchy xDM (and Semarchy Convergence for MDM 3.x)
* This query returns details of an integration batch for a specified Batch ID.
* Note the single bind variable: :BATCHID
* SQL*Developer prompts for a value when you execute this query.
* Enter the Batch ID when prompted. Or enter NULL to return ALL logs (can be a very large result set).
*
*** Tweaks for PostgreSQL ***
*
* pgAdmin4 does not support bind variables, so you must edit the query before executing:
* Comment out line 48
* Replace '<batchid>  in line 49 with your specified batchid
* Uncomment line 49.
*/
with LOGS as (
    select
        BATCHID
        ,RANK() OVER (ORDER BY coalesce(TL4.START_DATE, TL3.START_DATE, TL2.START_DATE, TL1.START_DATE)) RNK
        ,JI.NAME JOB_INSTANCE_NAME
        ,JL.START_DATE JOB_START_DATE
        ,JL.END_DATE JOB_END_DATE
        ,JL.ROW_COUNT JOB_ROW_COUNT
        ,JL.ERR_COUNT JOB_REJECTS_COUNT
        ,JL.STATUS JOB_STATUS /* RUNNING, DONE, WARNING, ERROR */
        ,JL.MESSAGE JOB_MESSAGE
        ,TL1.NAME TASK_NAME1
        ,TL2.NAME TASK_NAME2
        ,TL3.NAME TASK_NAME3
        ,TL4.NAME TASK_NAME4
        ,TL1.MESSAGE TASK_MESSAGE1
        ,coalesce(TL4.ROW_COUNT, TL3.ROW_COUNT, TL2.ROW_COUNT, TL1.ROW_COUNT) TASK_ROW_COUNT
        ,coalesce(TL4.START_DATE, TL3.START_DATE, TL2.START_DATE, TL1.START_DATE) TASK_START_DATE
        ,coalesce(TL4.END_DATE, TL3.END_DATE, TL2.END_DATE, TL1.END_DATE) TASK_END_DATE
        ,coalesce(TL4.STATUS, TL3.STATUS, TL2.STATUS, TL1.STATUS) TASK_STATUS
        ,coalesce(TL4.MESSAGE, TL3.MESSAGE, TL2.MESSAGE, TL1.MESSAGE) TASK_MESSAGE
        ,coalesce(TD4.SQL, TD3.SQL, TD2.SQL, TD1.SQL) TASK_SQL
    from
        SEMARCHY_REPO.MTA_INTEG_BATCH B
        inner join SEMARCHY_REPO.MTA_JOB_INSTANCE JI on ( B.R_JOBINSTANCE = JI.UUID)
        inner join SEMARCHY_REPO.MTA_JOB_LOG JL on ( JI.UUID = JL.R_JOBINSTANCE)
        inner join SEMARCHY_REPO.MTA_TASK_LOG TL1 on ( JL.UUID = TL1.O_JOBLOG and TL1.R_TASKLOG is null)
        left join SEMARCHY_REPO.MTA_TASK_LOG TL2 on ( TL1.UUID = TL2.R_TASKLOG )
        left join SEMARCHY_REPO.MTA_TASK_LOG TL3 on ( TL2.UUID = TL3.R_TASKLOG )
        left join SEMARCHY_REPO.MTA_TASK_LOG TL4 on ( TL3.UUID = TL4.R_TASKLOG )
        left join SEMARCHY_REPO.MTA_TASK_DEF TD1 on ( TD1.UUID = TL1.R_TASKDEF )
        left join SEMARCHY_REPO.MTA_TASK_DEF TD2 on ( TD2.UUID = TL2.R_TASKDEF )
        left join SEMARCHY_REPO.MTA_TASK_DEF TD3 on ( TD3.UUID = TL3.R_TASKDEF )
        left join SEMARCHY_REPO.MTA_TASK_DEF TD4 on ( TD4.UUID = TL4.R_TASKDEF )
    where B.BATCHID = nvl( :BATCHID, B.BATCHID ) -- works for Oracle
    -- where B.BATCHID = coalesce( <batchid>, B.BATCHID ) -- works for PostgreSQL
    order by coalesce(TL4.START_DATE, TL3.START_DATE, TL2.START_DATE, TL1.START_DATE)
)
select
    BATCHID, JOB_INSTANCE_NAME, JOB_START_DATE, JOB_END_DATE,
    JOB_ROW_COUNT, JOB_REJECTS_COUNT, JOB_STATUS, JOB_MESSAGE,
    TASK_NAME1, TASK_NAME2, TASK_NAME3, TASK_NAME4,
    TASK_ROW_COUNT, TASK_START_DATE, TASK_END_DATE,
    to_char(extract(hour from TASK_END_DATE - TASK_START_DATE), 'fm00') || ':' ||
    to_char(extract(minute from TASK_END_DATE - TASK_START_DATE), 'fm00') || ':' ||
    to_char(extract(second from TASK_END_DATE - TASK_START_DATE), 'fm00.000') TASK_DURATION,
    TASK_STATUS,
    TASK_MESSAGE,
    TASK_SQL
from LOGS ;