Useful Informatica Metadata Queries - Part I

Below are the list of Informatica metadata queries that will be useful for maintenance and support. SQL queries are written for metadata database - MS SQL Server

--Query to check currently running tasks
SELECT "START_TIME","WORKFLOW_NAME","SUBJECT_AREA","WORKFLOW_ID","WORKFLOW_RUN_ID","INSTANCE_NAME","TASK_ID","TASK_TYPE_NAME","TASK_TYPE","END_TIME","RUN_ERR_CODE","RUN_ERR_MSG","RUN_STATUS_CODE","TASK_NAME","SERVER_NAME"
FROM REP_TASK_INST_RUN
WHERE RUN_STATUS_CODE=6

--Query to check the list of tasks that has failed in the interval given below
SELECT
       RTI.SUBJECT_AREA AS FOLDER,
       RTI.WORKFLOW_NAME AS WORKFLOW,
       RTI.START_TIME AS START_TIME,      
       RTI.INSTANCE_NAME AS INSTANCE_NAME,
  CASE RTI.RUN_STATUS_CODE
       WHEN  3 THEN 'FAILED'
           WHEN   4 THEN 'STOPPED'
            WHEN  5 THEN 'ABORTED'
            WHEN  15 THEN 'TERMINATED'
END,
       RTI.RUN_ERR_CODE AS ERROR_CODE,
       RTI.RUN_ERR_MSG AS ERROR_MSG,
       RTI.END_TIME AS END_TIME
FROM
       REP_TASK_INST_RUN RTI
       WHERE RTI.RUN_STATUS_CODE IN (3,4,5,14,15)
       AND RTI.START_TIME > '2014-11-25 00:00:00'      
ORDER BY
       RTI.START_TIME DESC