Page Comparison

...

Panel

bgColor	#fff
title	Description of Steps

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

Code Block

language	sql
title	SQL query

-- 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.
-- pgAdmin4 and other tools do not support bind variables, so you must edit the query before executing:
--   Replace ":BATCHID" in line with your specified batchid

with LOGS as (
    select
        BATCHID
        ,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 = :BATCHID /* SQLDeveloper will prompt for a batch ID. In other tools, replace :BATCHID with your batch ID before executing. */
)
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 ;

Panel

bgColor	#fff
title	Script

/*

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.

*/

Code Block

language	sql
title	SQL script

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;

Download the query as a text document:

...

Versions Compared

Old Version 4

New Version 5

Key