Export Semarchy xDM Integration Batch Logs

-- 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 bound variable 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 41 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
order by TASK_START_DATE ;

-- 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 commented placeholder: --BatchIDPlaceholder
-- You must edit the query with your specified batchid before executing
-- This query works only with PostgreSQL. Oracle users should use the Oracle version of this query.

with recursive LOGS as (
  select 
    1::int as LVL, 
    TD.NAME as TASK_GROUP1, 
    NULL as TASK_GROUP2, 
    NULL as TASK_GROUP3,
    B.BATCHID, 
    JL.START_DATE as JOB_START_DATE, 
    JL.END_DATE as JOB_END_DATE, 
    JL.ROW_COUNT as JOB_ROW_COUNT, 
    JL.ERR_COUNT as JOB_REJECTS_COUNT, 
    JL.STATUS as JOB_STATUS, 
    JL.MESSAGE as JOB_MESSAGE,
    JD.NAME as JOB_NAME, 
    TD.NAME as TASK_NAME, 
    TD.CLASSNAME, 
    TD.SQL as TASK_SQL,
    TL.START_DATE as TASK_START_DATE, 
    TL.END_DATE as TASK_END_DATE, 
    TL.ROW_COUNT as TASK_ROW_COUNT, 
    TL.UUID, 
    TL.R_TASKLOG, 
    TL.ORD_POS, 
    TL.MESSAGE as TASK_MESSAGE, 
    TL.STATUS as TASK_STATUS
  from         MTA_TASK_LOG     TL
    inner join MTA_TASK_DEF     TD on ( TD.UUID = TL.R_TASKDEF )
    inner join MTA_JOB_DEF      JD on ( JD.UUID = TD.O_JOBDEF)
    inner join MTA_JOB_LOG      JL on ( JL.UUID = TL.O_JOBLOG )
    inner join MTA_JOB_INSTANCE JI on ( JI.UUID = JL.R_JOBINSTANCE )
    inner join MTA_INTEG_BATCH   B on ( B.R_JOBINSTANCE  = JI.UUID )  
  where B.BATCHID = --BatchIDPlaceholder
    and R_TASKLOG is null /* equivalent to "start with" part in Oracle connect_by query */
union all
  select 
    (parent_task.LVL + 1), 
    parent_task.TASK_GROUP1, 
    case when parent_task.LVL = 1 then TD.NAME when parent_task.LVL > 1 then parent_task.TASK_GROUP2 else NULL end::text, 
    case when parent_task.LVL = 2 then TD.NAME when parent_task.LVL > 2 then parent_task.TASK_GROUP3 else NULL end::text,
    B.BATCHID, 
    JL.START_DATE as JOB_START_DATE, JL.END_DATE as JOB_END_DATE, JL.ROW_COUNT as JOB_ROW_COUNT, JL.ERR_COUNT as JOB_REJECTS_COUNT, JL.STATUS as JOB_STATUS, JL.MESSAGE as JOB_MESSAGE,
    JD.NAME as JOB_NAME, 
    TD.NAME as TASK_NAME, TD.CLASSNAME, TD.SQL as TASK_SQL,
    TL.START_DATE, TL.END_DATE, TL.ROW_COUNT, TL.UUID, TL.R_TASKLOG, TL.ORD_POS, TL.MESSAGE, TL.STATUS
  from         MTA_TASK_LOG     TL
    inner join MTA_TASK_DEF     TD on ( TD.UUID = TL.R_TASKDEF )
    inner join MTA_JOB_DEF      JD on ( JD.UUID = TD.O_JOBDEF)
    inner join MTA_JOB_LOG      JL on ( JL.UUID = TL.O_JOBLOG )
    inner join MTA_JOB_INSTANCE JI on ( JI.UUID = JL.R_JOBINSTANCE )
    inner join MTA_INTEG_BATCH   B on ( B.R_JOBINSTANCE  = JI.UUID )  
    inner join LOGS parent_task on ( parent_task.UUID = TL.R_TASKLOG ) /* equivalent to "connect by" part in Oracle connect_by query */
)
select 
  BATCHID, JOB_NAME, JOB_START_DATE, JOB_END_DATE, JOB_ROW_COUNT, JOB_REJECTS_COUNT, JOB_STATUS, JOB_MESSAGE,
  LVL,
  TASK_GROUP1, TASK_GROUP2, TASK_GROUP3, 
  TASK_NAME, TASK_SQL,
  EXTRACT( EPOCH FROM ( TASK_END_DATE - TASK_START_DATE ) ) DUR_SEC,
  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') DURATION,
  TASK_ROW_COUNT,
  TASK_STATUS,
  TASK_START_DATE,
  TASK_END_DATE
from LOGS
where CLASSNAME not like 'Group%' /* Task Groups are not interesting here. You can remove this line to see all logs in the xDM GUI (even ones with no executed SQL). */
order by TASK_START_DATE
;