Find and Audit Active Service Operations

It is a good idea to periodically monitor what service operations are active in your production database.

Here is a base query that should return active Service operations that are routed to an active local node.

• Make sure the values returned here are only service operations that you are actually using.
• You should inactivate routings and service operations that you are not using.
• This will ensure you do not have data being posted to the integration broker and updating or extracting data.
• This should be checked after applying bundles and patches

-- Find All Active Service Operations and routings 
select O.IB_OPERATIONNAME, 
RD.SENDERNODENAME,RD.RECEIVERNODENAME, o.descr
from PSOPERATION o, PSOPRVERDFN OV, PSIBRTNGDEFN RD, PSMSGNODEDEFN MN
where O.IB_OPERATIONNAME = OV.IB_OPERATIONNAME
AND OV.ACTIVE_FLAG = 'A'
AND RD.IB_OPERATIONNAME = OV.IB_OPERATIONNAME
AND RD.VERSIONNAME = OV.VERSIONNAME
AND RD.EFF_STATUS = 'A' -- ROUTING IS ACTIVE
AND RD.RECEIVERNODENAME = mn.msgnodename  
AND mn.active_node = 1 
AND mn.localnode = 1
ORder by O.ib_operationname, RD.SENDERNODENAME;