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