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.

ERwin Data modeling basics - Creating a new model, entity and attribute

CA ERwin Data Modeler (ERwin) is a software tool for data modeling (data requirements analysis, database design etc.) of custom developed information systems, including databases of transactional systems and data marts.

In this article, let us start with some of the basics of ERwin modeler tool


Creating a new data model

This tool is predominantly used for creating Logical & Physical data models. To create a new model, click File --> New

Once a new model screen appears, you can start working on your requirement.

Creating a new data model from existing template

To create a new model from existing template, click File --> New    
Select the highlighted icon. Browse through folders to select an existing model/template

Select an existing model/template to start working on a new model with the existing template

Note:

In case you are using an existing template, you can use the below tips:

1. Using an existing physical model, you can create a logical/physical model
2. Using an existing logical model, you can create a logical/physical model
3. But, using an existing logical model, you cannot create a physical model and vice versa.

 Working with entities and attributes:

To know about entity / attributes, please refer the below links:
http://gerardnico.com/wiki/data_modeling/entity

http://gerardnico.com/wiki/data_modeling/attribute

Creating a new entity:

Click on the Entity icon in tool bar and click on the model to create a new entity

Editing Entity Name:

Click on the Entity Name to edit the entity.

Editing Attribute Name:

Method 1:

Click on the Attribute name for editing its name. By pressing 'Tab' key, a new attribute will be created.

Method 2:

Double click on the attribute name to get the below screen, where name, data type & constraints can be defined or modified.