Query On Repository of Informatica

Informatica Repository management

select * from V_PC8X_CONNECTION where CONNECTION_NAME IN (select DISTINCT CONNECTION_NAME from REP_SESSION_CNXS where SUBJECT_AREA='FOLDER_NAME'); 

 If you want to get connection details used in particular folder then you need to pass the FOLDER_NAME as input to the above query. Above query will give details like Connection name, Host name, database type, user name, Connection string and more. Informatica Repository Queries to get connection names, command tasks and event wait file names 

Query to get folder name, workflow name, session names and all the connections used in sessions.


 SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME, 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, 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 AND WF.SUBJECT_AREA = 


Note: If SESS_EXTN_OBJECT_TYPE=78 then its reader, If 79 then its writer Query2: Query to get commands used in command tasks including the commands used as pre/post session commands. 


 SELECT DISTINCT T.SUBJECT_ID, F.SUBJECT_AREA AS FOLDER_NAME, W.TASK_NAME AS WORKFLOW_NAME, T.TASK_NAME AS CMD_TASK_NAME, CMD.PM_VALUE AS CMD_NAME, CMD.EXEC_ORDER, CMD.VAL_NAME AS CMD_NUMBER, T.TASK_ID, T.TASK_TYPE, T.RU_PARENT_ID FROM OPB_TASK_VAL_LIST CMD, OPB_TASK T, OPB_TASK W, REP_SUBJECT F WHERE T.TASK_ID = CMD.TASK_ID AND T.SUBJECT_ID = F.SUBJECT_ID AND T.TASK_TYPE = 58 AND T.RU_PARENT_ID = W.TASK_ID AND F.SUBJECT_AREA = ORDER BY F.SUBJECT_AREA, W.TASK_NAME, T.TASK_NAME, CMD.EXEC_ORDER 

Query to get the event wait file names used in event wait tasks.  


SELECT DISTINCT F.SUBJECT_AREA AS FOLDER_NAME, W.TASK_NAME AS WORKFLOW_NAME, T.TASK_ID, T.TASK_TYPE, T.TASK_NAME AS EVENTWAIT_NAME, T.RU_PARENT_ID, W.TASK_ID FROM OPB_TASK T, OPB_TASK W, REP_SUBJECT F WHERE T.RU_PARENT_ID = W.TASK_ID AND T.TASK_TYPE = 60 AND T.SUBJECT_ID = F.SUBJECT_ID AND F.SUBJECT_AREA =

Comments

Post a Comment

Popular posts from this blog

Data Center Technician / Engineer certifications

Cisco Certified Network Associate Data Center (CCNA Data Center) Cisco Certified Network Professional Data Center ( CCNP Data Center ) Juniper Networks Certified Professional Data Center (JNCIP-DC) VCE Converged Infrastructure Administration Engineer (VCE-CIAE) VMware Certified Professional 6 – Data Center Virtualization (VCP6-DCV)
Read more

different OPB tables

OPB_SUBJECT - PowerCenter folders table OPB_MAPPING - Mappings table OPB_TASK - Tasks table like sessions, workflow etc TASK_TYPE for session is 68 and that of the workflow is 71. OPB_SESSION - Session & Mapping linkage table OPB_TASK_ATT R - Task attributes tables OPB_WIDGET - Transformations table Usage: Use WIDGET_ID from this table to that of the WIDGET_ID of any of the tables to know the transformation name and the folder details. Use this table in conjunction with OPB_WIDGET_ATTR or OPB_WIDGET_EXPR to know more about each transformation etc. OPB_WIDGET_FIEL D - Transformation ports table Usage: Take the FIELD_ID from this table and match it against the FIELD_ID of any of the tables like OPB_WIDGET_DEP and you can get the corresponding information. OPB_WIDGET_ATTR - Transformation properties table Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this transformation means. OPB_EXPRESSION - Expressions table Usa...
Read more

Industry 4.0 IOT

The Industrial Internet of Things (iIoT), which is also known as  Industry 4.0  when applied to the manufacturing industry, is a concept of integrating smart manufacturing machinery, AI-powered automation, and advanced analytics to help make every worker and every factory more efficient. The iIoT will revolutionize manufacturing by enabling the acquisition and accessibility of far greater amounts of data both faster and more efficiently than ever before. Many manufacturers have already started to implement the IIoT devices and processes by leveraging intelligent, connected devices inside their factories, warehouses, and workshops. In most cases, companies are deploying iIoT devices and machinery that are connected by communication technologies that assist industries in collecting, monitoring, analyzing, and delivering valuable insights like never before. The exact mechanics of how this technology is deployed varies from company to company, but the goal is always the same; to i...
Read more