Friday, January 20, 2017

Informatica Metadata Queries - 16 INTEGRATION SERVICES




16.1         How to delete unwanted Integration Services (seen in wf Monitor)


1 Take back up of the following tables

Create table OPB_SESS_TASK_LOG_20151020 as select * from OPB_SESS_TASK_LOG ;
Create table OPB_SWIDGINST_LOG_20151020 as select * from OPB_SWIDGINST_LOG ;
Create table OPB_DTL_SWIDG_LOG_20151020 as select * from OPB_DTL_SWIDG_LOG ;
Create table OPB_TASK_INST_RUN_20151020 as select * from OPB_TASK_INST_RUN ;
Create table OPB_WFLOW_VAR_RUN_20151020 as select * from OPB_WFLOW_VAR_RUN ;
Create table OPB_WFLOW_DEP_RUN_20151020 as select * from OPB_WFLOW_DEP_RUN ;
Create table OPB_WFLOW_CACHE_20151020 as select * from OPB_WFLOW_CACHE ;
Create table OPB_WFLOW_RUN_20151020 as select * from OPB_WFLOW_RUN ;


2. Perform the below delete statements to delete log tables.


DELETE FROM OPB_SESS_TASK_LOG;
DELETE FROM OPB_SWIDGINST_LOG;
DELETE FROM OPB_DTL_SWIDG_LOG;
DELETE FROM OPB_TASK_INST_RUN;
DELETE FROM OPB_WFLOW_VAR_RUN;
DELETE FROM OPB_WFLOW_DEP_RUN;
DELETE FROM OPB_WFLOW_CACHE;
DELETE FROM OPB_WFLOW_RUN;
COMMIT;

3. Execute the below query for the list of integration services list under the repository

SELECT * FROM OPB_SERVER_INFO;

4. Delete the duplicate/unwanted integration service based on the least server id  

(ex: 2 – Integration_ABC, 5 - Integration_ABC, perform delete on 2)

Delete from OPB_SERVER_INFO where server_id in ( 2,5) -- least server id as it is outdated



16.2         Total Count of workflows assigned to each Integration Services



SELECT SERVER_NAME,
  COUNT(1) AS WF_COUNT
FROM
  (SELECT SUBJECT_ID,
    WORKFLOW_NAME,
    SERVER_NAME,
    TRUNC(START_TIME) AS START_TIME,
    RANK() OVER (PARTITION BY SUBJECT_ID, WORKFLOW_NAME ORDER BY START_TIME DESC ) RNK
  FROM OPB_WFLOW_RUN
  )
WHERE RNK=1
GROUP BY SERVER_NAME
  --ORDER BY 1,2,3



  SELECT (SELECT REPOSITORY_NAME FROM OPB_REPOSIT_INFO) AS REPO,
  TRUNC (START_TIME) AS START_TIME,
         A.SERVER_NAME AS INTEGRATION_SERVICE,
         COUNT (*) WF_COUNT
    FROM OPB_WFLOW_RUN A
   WHERE TRUNC (START_TIME) = (  SELECT MAX (TRUNC (AA.START_TIME))
                                   FROM OPB_WFLOW_RUN AA
                                  WHERE AA.WORKFLOW_NAME = A.WORKFLOW_NAME
                               GROUP BY A.WORKFLOW_NAME)
GROUP BY SERVER_NAME,TRUNC (START_TIME)
--ORDER BY 1, 2, 3

16.3         List of workflows running on each Integration Service



select * from (
select
(SELECT repository_name FROM OPB_REPOSIT_INFO) AS REPO,
subject_id, workflow_name,server_name, trunc(start_time) as start_time,
rank() over (partition by subject_id, workflow_name order by start_time desc ) rnk
from  OPB_WFLOW_RUN
) where rnk=1

order by 1,2,3