Session Run Details

Displays session run details for any start date by repository by folder. This is a primary report in an analytic workflow.


Click on the text to copy the query report to your clipboard.


select "FAILED_SOURCE_ROWS",
"SERVER_NAME",
"SESSION_TIMESTAMP",
"SUCCESSFUL_SOURCE_ROWS",
"SESSION_INSTANCE_NAME",
"LAST_ERROR",
"FIRST_ERROR_MSG",
"SESSION_TIMESTAMP_LOG",
"SUCCESSFUL_ROWS",
"WORKFLOW_NAME",
"FAILED_ROWS",
"SESSION_NAME",
"ACTUAL_START",
case when ("RUN_STATUS_CODE" <= 9) then (case when ("RUN_STATUS_CODE" = 1) then ('Succeeded') else (case when ("RUN_STATUS_CODE" = 2) then ('Disabled') else (case when ("RUN_STATUS_CODE" = 3) then ('Failed') else (case when ("RUN_STATUS_CODE" = 4) then ('Stopped') else (case when ("RUN_STATUS_CODE" = 5) then ('Aborted') else (case when ("RUN_STATUS_CODE" = 6) then ('Running') else (case when ("RUN_STATUS_CODE" = 7) then ('Suspending') else (case when ("RUN_STATUS_CODE" = 8) then ('Suspended') else (case when ("RUN_STATUS_CODE" = 9) then ('Stopping') else ('') end) end) end) end) end) end) end) end) end) else (case when ("RUN_STATUS_CODE" = 10) then ('Aborting') else (case when ("RUN_STATUS_CODE" = 11) then ('Waiting') else (case when ("RUN_STATUS_CODE" = 12) then ('Scheduled') else (case when ("RUN_STATUS_CODE" = 13) then ('UnScheduled') else (case when ("RUN_STATUS_CODE" = 14) then ('Unknown') else (case when ("RUN_STATUS_CODE" = 15) then ('Terminated') else ('') end) end) end) end) end) end) end as "Calc_RUN_STATUS_CODE",
"LAST_ERROR_CODE",
"SESSION_LOG_FILE",
"REPOSITORY_NAME",
"MAPPING_NAME",
"TOTAL_ERR",
"SUBJECT_AREA",
"FIRST_ERROR_CODE"
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, to_char(SESSION_LOG.ACTUAL_START,'mm/dd/yy HH:MI:SS AM') as ACTUAL_START , SESSION_LOG.SESSION_TIMESTAMP as SESSION_TIMESTAMP, SESSION_LOG.RUN_STATUS_CODE as RUN_STATUS_CODE, SESSION_LOG.SESSION_LOG_FILE as SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG as FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR as LAST_ERROR, (SUM(SESSION_LOG.SUCCESSFUL_SOURCE_ROWS)) as SUCCESSFUL_SOURCE_ROWS, (SUM(SESSION_LOG.FAILED_SOURCE_ROWS)) as FAILED_SOURCE_ROWS, (SUM(SESSION_LOG.SUCCESSFUL_ROWS)) as SUCCESSFUL_ROWS, (SUM(SESSION_LOG.FAILED_ROWS)) as FAILED_ROWS, (SUM(SESSION_LOG.TOTAL_ERR)) as TOTAL_ERR, (SUM( CASE WHEN SESSION_LOG.SESSION_TIMESTAMP IS NOT NULL THEN (SESSION_LOG.SESSION_TIMESTAMP - SESSION_LOG.ACTUAL_START) * 86400 END)) as SESSION_TIMESTAMP_LOG FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_TASK_INST_RUN SESSION_TASK_INST_RUN WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND SESSION_LOG.SESSION_ID = SESSION_TASK_INST_RUN.TASK_ID AND SESSION_LOG.INSTANCE_ID = SESSION_TASK_INST_RUN.INSTANCE_ID AND SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TASK_INST_RUN.TASK_VERSION_NUMBER AND SESSION_LOG.WORKFLOW_ID = SESSION_TASK_INST_RUN.WORKFLOW_ID AND SESSION_LOG.WORKFLOW_VERSION_NUMBER = SESSION_TASK_INST_RUN.VERSION_NUMBER AND SESSION_LOG.WORKFLOW_RUN_ID = SESSION_TASK_INST_RUN.WORKFLOW_RUN_ID AND SESSION_TASK_INST_RUN.TASK_TYPE = 68 ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_LOG.ACTUAL_START, SESSION_LOG.SESSION_TIMESTAMP, SESSION_LOG.RUN_STATUS_CODE, SESSION_LOG.SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR) "query"
--where "SUBJECT_AREA" in ('<Folder_Name>') and "SESSION_NAME" in ('<Session_Name>') and "SESSION_TIMESTAMP" between DATE '<Start_Date_Between>' and DATE '<Date_And>'
order by "REPOSITORY_NAME", "SUBJECT_AREA"

2 Comments

    1. You welcome Kareem, let me know if this query helped you.

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*