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

1 Comments:

At 4/01/2019 , Blogger sheela rajesh said...

Amazing experience on reading your article. It is really nice and informative.
Python Training in Chennai
Python Classes in Chennai
JAVA Training in Chennai
Hadoop Training in Chennai
Selenium Training in Chennai
Python Training in Chennai
Python Course in Chennai

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home