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