Informatica Power Center Meta Data tables details and queries

 Informatica saves the Data in three levels

1) PCD Level :  These are under ISP_PCD Schema which has high level Information. This contains domain level information

2) Repository Level :  These are reposiotry level. This is the repository schema.

3)  Object Level : This permission or attributes related specific object like Workflow, Folder, Session level etc

Domain Level tables: 

------------------------------

ISP_RUN_LOG : It contains log details all workflows in a domain.

PO_IDOBJ: Provide the individual login details at domain level

PO_OSPROFILE: Os profile details

PO_USERINFO : gets user infromation

Repository level tables:

-------------------------------------

REP_SUBJECT : Folders names (OPB_SUBJECT)

REP_WFLOW_RUN : Gives information about workflow run details

REP_SESS_LOG: Provide the run stats of worklfow and sessions. Using this table we can check start time, end time, succesful_row,actual_rows.

REP_DEPLOY_GROUP :  Gives the information of Deployment groups.

REP_TASK_INST_RUN :  Gives the no of times the task runs for workflows.

REP_TASK_INST:  Gives the information at task level instance 

REP_SESSION_CNXS : Repository connections

REP_SESS_WIDGET_CNXS :  Transformation level connections. example like lookup connections

Object level tables

-------------------------------------

OPB_TASK : Gives the information at object level task  instance 

OPB_WFLOW_RUN:Gives the information at object level workflow instance

OPB_SESSION_TASK_LOG : Gives the information at  session task logs 

OPB_DTL_SWIDG_LOG : 

OPB_ROLE : Roles in Repository

OPB_REPOSIT_INFO  : Provides the current repository details in informatica

OPB_CNX : Provides the connection details (DB, MQ etc) in a specific repository

OPB_OBJECT_ACCESS :  Gives details about each object access details

OPB_SUBJECT :  Folder details

OPB_USER_GROUP :  Specifies user belong to how many groups

OPB_USER: user details in a repository

OPB_GROUPS :  groups in a repository

OPB_CNX_ATTR : provides the connection attributes like connection string ,username etc

Query to get Sequence Number

------------------------------------------

SELECT DISTINCT OM.mapping_name
	,instance_name
	,attr_value
FROM rep_widget_inst RWI
JOIN opb_mapping OM ON RWI.mapping_id = RWI.mapping_id
JOIN opb_widget_attr OWA ON OWA.widget_id = RWI.widget_id
	AND rwi.widget_type = OWA.widget_type
WHERE RWI.widget_type LIKE 'sequence%'
	AND attr_id = 4

Query to get Mapping Details

----------------------------------------

SELECT DISTINCT CONCAT (
		F.SUBJ_NAME
		,CONCAT (
			.
			,OM.MAPPING_NAME
			)
		) AS Mappinname
FROM OPB_MAPPING OM
JOIN OPB_SUBJECT F ON OM.SUBJECT_ID = F.SUBJ ID
WHERE MAPPING_NAME = 'mpl_test';

All Connection details in a repository

----------------------------------------------------

SELECT CNX SUBTYPE NAME
,C.OBJECT NAME name
,C.CONNECT STRING
,ca.attr_id
,CA.attr value data source
,C.LAST SAVED
,C.USER NAME
FROM OPB CNX C
INNER JOIN OPB MMD CNXG ON C.OBJECT ID = CA.OBJECT ID ON G.CNX OBJECT TYPE = C.OBJECT TYPE
AND G.CNX BJECT SUBTYPE - C.OBJECT SUBTYPE INNER JOIN ISP PCR EIG.OPB CNX ATTR CA
WHERE G.CNX OBJECT TYPE = 73
AND ca.attr id = 11
ORDER BY CNX SUBTYPE NAME
,connect_string
,attr value;

Workflows with connection details

------------------------------------------

SELECT WF.SUBJECT AREA AS FOLDER NAME
	,WF.WORKFLOW NAME AS T.INSTANCE NAME AS SESSION NAME
	,T.TASK TYPE NAME
	,C.CNX NAME AS CONNECTION NAME
	,V.CONNECTION SUBTYPE
	,V.HOST_NAME V.USER NAME
	,C.INSTANCE_NAME
	,C.READER_WRITER_TYPE
	,C.SESS_EXTN_OBJECT_TYPE
FROM REP_TASK_INST T
	,REP_SESS_WIDGET_CNXS C REP_WORKFLOWS WF
	,OPB_MAPPING OM
	,V_IME_CONNECTION V
WHERE T.TASK ID = C.SESSION ID
	AND WF.WORKFLOW ID = T.WORKFLOW ID
	AND C.CNX NAME = V.CONNECTION_NAME

Query to find out verbose details

------------------------------------------

SELECT t.task_id AS session_id
	,t.task_name AS session_name
	,w.workflow_name
	,w.subject_area AS folder_name
	,ca.attr_id AS attr_id
	,ca.attr_value AS attr_value
FROM OPB_TASK T
JOIN opb_cfg_attr ca ON ca.version_number = t.version_number
JOIN rep_task_inst ti ON ti.task_id = t.task_id
JOIN rep_workflow w ON w.workflow_id = t.workflow_id
	AND t.version_number = (
		SELECT max(version_number)
		FROM opb_task t1
		WHERE t1.task_id = t.task_id
		)
	AND ca.attr_value = 4
	AND t.task_type = 68
	AND ca.attr_id = 204;