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';
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:
Post a Comment