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

Teradata Collect Stats

1.     From Teradata 14, it is better to combine multiple collect stats on same table so that table scans occur just once.

Example-

Collect stats Column col1 on table1;

Collect stats Column col2 on table1;

Collect stats Column (col1,col3) on table1;

Instead of above one, replace it with

Collect stats

Column col1

,Column col2

,Column (col1,col3)

On table1;

2.       When a table has billions of rows and we don’t have much time window left then we can go for collecting sample statistics whose syntax given below

“Collect Statistics Using <sampling level > <column name> on <table name>”

3.       Instead of recollecting full stats on table, summary stats can be opted utilizing less resources and time consumed.

“Collect Summary Stats on <table name>”

4.       Use Help Statistics on table to see the summary of stats values collected and Show Statistics for detailed description on column stats.

show stats on edw_db.notebase column(NoteContactAuthorEpicUserID )

help Stats on edw_db.notebase

Thanks to Gokul from my team for this useful piece of information.