We can leverage the metadata collected in the Informatica repository for many interesting analysis, few of the scenarios where I have leveraged the Informatica Metadata are as following.
This SQL Queries can be executed in Oracle database with no changes and requires little modification with other databases.
Failed Sessions
The following query lists the failed sessions. To make it work for the last ‘n’ days, replace SYSDATE-1 with SYSDATE – n
QUERY:
SELECT SUBJECT_AREA AS FOLDER_NAME,
SESSION_NAME,
LAST_ERROR AS ERROR_MESSAGE,
DECODE (RUN_STATUS_CODE,3,’Failed’,4,’Stopped’,5,’Aborted’) AS STATUS,
ACTUAL_START AS START_TIME,
SESSION_TIMESTAMP
FROM REP_SESS_LOG
WHERE RUN_STATUS_CODE != 1
AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE)
RESULT:
Long running Sessions
The following query lists long running sessions. To make it work for the last ‘n’ days, replace SYSDATE-1 with SYSDATE – n
QUERY:
SELECT SUBJECT_AREA AS FOLDER_NAME,
SESSION_NAME,
SUCCESSFUL_SOURCE_ROWS AS SOURCE_ROWS,
SUCCESSFUL_ROWS AS TARGET_ROWS,
ACTUAL_START AS START_TIME,
SESSION_TIMESTAMP
FROM REP_SESS_LOG
WHERE RUN_STATUS_CODE = 1
AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE)
AND (SESSION_TIMESTAMP – ACTUAL_START) > (10/(24*60))
ORDER BY SESSION_TIMESTAMP
RESULT:
Invalid Tasks
The following query lists folder names and task name, version number, and last saved for all invalid tasks.
QUERY:
SELECT SUBJECT_AREA AS FOLDER_NAME,
DECODE(IS_REUSABLE,1,’Reusable’,’ ‘) || ‘ ‘ ||TASK_TYPE_NAME AS TASK_TYPE,
TASK_NAME AS OBJECT_NAME,
VERSION_NUMBER,
LAST_SAVED
FROM REP_ALL_TASKS
WHERE IS_VALID=0
AND IS_ENABLED=1
ORDER BY SUBJECT_AREA,TASK_NAME
RESULT:
Thanks for reading, do you have other scenarios where Workflow Metadata has been effective …wish you a very happy new year 2011.
