Different name but the requirement or functionality is same:-
1) An ETL/Informatica Framework for Operational Metadata Logging
2) Process Control / Audit of Workflows in Informatica
3) ETL Audit and Control information
4) A Scheduler framework for Audit - Balance and Control
5) Collecting Session Stat for audit purpose
below is the approach to implement the above requirement:-
Approach:-
The above requirement can be implemented using Informatica repository Views:-
The 2 vies used for this framework are:-
REP_SESS_LOG and REP_SESS_TBL_LOG one can find these view in the data base where Informatica repository server installed.
Let's understand what blow query will do.
Both query's will return last session run related records.
for example in the development Env we have 6 months of data in the views, out of that we will get only last run information for each session in a folder (as we are filtering data based on the folder first) **Where SUBJECT_AREA=PROJECT_FOLDER.
Querys:-
SELECT
A.SUBJECT_AREA AS FOLDER_NAME,
B.WORKFLOW_NAME AS WORKFLOW_NAME ,
B.SESSION_NAME AS SESSION_NAME,
B.MAPPING_NAME AS MAPPING_NAME,
A.TYPE_NAME AS TABLE_TYPE_NAME ,
A.TABLE_NAME AS TARGET_TABLE_NAME ,
A.TABLE_INSTANCE_NAME AS TARGET_INSTANCE_NAME,
B.SUCCESSFUL_SOURCE_ROWS AS TOTAL_SRC_RECORD_COUNT,
B.FAILED_SOURCE_ROWS AS REJECTED_SRC_RECORD_COUNT,
A.SUCCESSFUL_AFFECTED_ROWS AS AFFECTED_TGT_RECORD_COUNT,
A.FAILED_ROWS AS REJECTED_TGT_RECORD_COUNT,
TO_CHAR(B.ACTUAL_START,'MM/DD/YYYY HH24:MI:SS') AS SESSION_START_DATE,
TO_CHAR(B.SESSION_TIMESTAMP,'MM/DD/YYYY HH24:MI:SS') AS SESSION_END_DATE,
TO_CHAR(A.START_TIME ,'MM/DD/YYYY HH24:MI:SS') AS TARGET_LOAD_START_TIME,
TO_CHAR(A.END_TIME,'MM/DD/YYYY HH24:MI:SS') AS TARGET_LOAD_END_TIME,
THROUGHPUT AS TARGET_THROUGHPUT,
B.FIRST_ERROR_MSG AS FIRST_ERROR_MSG,
B.FIRST_ERROR_CODE AS FIRST_ERROR_CODE,
B.LAST_ERROR AS LAST_ERROR,
B.LAST_ERROR_CODE AS LAST_ERROR_CODE
FROM
DEV9.REP_SESS_TBL_LOG A , DEV9.REP_SESS_LOG B
WHERE
A.WORKFLOW_ID=B.WORKFLOW_ID
AND
A.SESSION_ID=B.SESSION_ID
AND
A.END_TIME<=B.ACTUAL_START
AND
A.END_TIME>=B.SESSION_TIMESTAMP
AND
A.SUBJECT_AREA='BI_CORE_Work'
ORDER BY
A.START_TIME DESC;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
SELECT * FROM (SELECT
SUBJECT_AREA AS FOLDER_NAME,
WORKFLOW_NAME,
SESSION_NAME,
MAPPING_NAME,
TRUNC (MOD ( (SESSION_TIMESTAMP - ACTUAL_START)* 24, 24))
|| ' HOUR '|| TRUNC (MOD ( (SESSION_TIMESTAMP - ACTUAL_START) * 24 * 60, 60))
|| ' MIN '|| TRUNC ( MOD ( (SESSION_TIMESTAMP - ACTUAL_START) * 24 * 60 * 60, 60))
|| ' SEC ' AS RUN_TIME,
ACTUAL_START AS SESSION_START_DATE,
SESSION_TIMESTAMP AS SESSION_END_DATE,
SUCCESSFUL_SOURCE_ROWS AS SRC_SUCCESSFUL_RECORD_COUNT,
SUCCESSFUL_ROWS AS TGT_SUCCESSFUL_RECORD_COUNT,
FAILED_SOURCE_ROWS AS SRC_REJECTED_RECORD_COUNT,
FAILED_ROWS AS TGT_REJECTED_ROWS_COUNT,
FIRST_ERROR_CODE AS ERROR_CODE,
DECODE (RUN_STATUS_CODE, 1, 'SUCCEEDED', 2, 'DISABLED', 3, 'FAILED', 4, 'STOPPED', 5, 'ABORTED',
6, 'RUNNING', 7, 'SUSPENDING', 8, 'SUSPENDED', 9, 'STOPPING', 10, 'ABORTING', 11, 'WAITING',
12, 'SCHEDULED', 13, 'UNSCHEDULED', 14, 'UNKNOWN', 15, 'TERMINATED', 'UNKNOWN') AS RUN_STATUS,
FIRST_ERROR_MSG AS FIRST_ERR_OF_THE_MAPPING,
LAST_ERROR AS LAST_ERR_OF_THE_MAPPING,
ROW_NUMBER() OVER(PARTITION BY SESSION_ID,WORKFLOW_ID
ORDER BY workflow_run_id DESC) AS LATES_VESRION_ID
FROM
DEV9.REP_SESS_LOG
WHERE
SUBJECT_AREA='BI_CORE_Work' AND SESSION_NAME='s_m_veeba_bi_load_STG_SRVC_stg')
WHERE LATES_VESRION_ID=1;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT
A.SUBJECT_AREA AS FOLDER_NAME,
B.WORKFLOW_NAME AS WORKFLOW_NAME ,
B.SESSION_NAME AS SESSION_NAME,
B.MAPPING_NAME AS MAPPING_NAME,
A.TYPE_NAME AS TABLE_TYPE_NAME ,
A.TABLE_NAME AS TARGET_TABLE_NAME ,
A.TABLE_INSTANCE_NAME AS TARGET_INSTANCE_NAME,
B.SUCCESSFUL_SOURCE_ROWS AS TOTAL_SRC_RECORD_COUNT,
B.FAILED_SOURCE_ROWS AS REJECTED_SRC_RECORD_COUNT,
A.SUCCESSFUL_AFFECTED_ROWS AS AFFECTED_TGT_RECORD_COUNT,
A.FAILED_ROWS AS REJECTED_TGT_RECORD_COUNT,
TO_CHAR(B.ACTUAL_START,'MM/DD/YYYY HH24:MI:SS') AS SESSION_START_DATE,
TO_CHAR(B.SESSION_TIMESTAMP,'MM/DD/YYYY HH24:MI:SS') AS SESSION_END_DATE,
TO_CHAR(A.START_TIME ,'MM/DD/YYYY HH24:MI:SS') AS TARGET_LOAD_START_TIME,
TO_CHAR(A.END_TIME,'MM/DD/YYYY HH24:MI:SS') AS TARGET_LOAD_END_TIME,
THROUGHPUT AS TARGET_THROUGHPUT,
B.FIRST_ERROR_MSG AS FIRST_ERROR_MSG,
B.FIRST_ERROR_CODE AS FIRST_ERROR_CODE,
B.LAST_ERROR AS LAST_ERROR,
B.LAST_ERROR_CODE AS LAST_ERROR_CODE
FROM
DEV9.REP_SESS_TBL_LOG A , DEV9.REP_SESS_LOG B
WHERE
A.WORKFLOW_ID=B.WORKFLOW_ID
AND
A.SESSION_ID=B.SESSION_ID
AND
A.END_TIME>=B.ACTUAL_START
AND
A.END_TIME<=B.SESSION_TIMESTAMP
AND
A.SUBJECT_AREA='BI_CORE_Work'
ORDER BY
A.START_TIME DESC;
