Page Comparison

-- 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
;