Saturday, May 29, 2021

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 infromation


2) Repository Level :  These are reposiotry level. 


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


ISP_PCD : 

-----------

ISP_RUN_LOG : 


PO_IDOBJ: Provide the individual login details


PO_OSPROFILE: Os profile details


PO_USERINFO : gets user infromation






ISP_PCR Schema Objects :

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


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


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

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




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 NAPPING_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
























No comments: