Important Queries related to SOA-INFRA

Query to fetch and tabulate the number of instances in various states


SELECT (CASE
WHEN STATE=1 THEN 'OPEN AND RUNNING'
WHEN STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN STATE=3 THEN 'OPEN AND FAULTED'
WHEN STATE=4 THEN 'CLOSED AND PENDING'
WHEN STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN STATE=6 THEN 'CLOSED AND FAUTED'
WHEN STATE=7 THEN 'CLOSED AND CANCELLED'
WHEN STATE=8 THEN 'CLOSED AND ABORTED'
WHEN STATE=9 THEN 'CLOSED AND STALE'
WHEN STATE=10 THEN 'NON-RECOVERABLE'
ELSE STATE || '' END) AS STATE,COMPOSITE_NAME, COUNT(*) AS NUM_OF_CUBE_INST FROM SOA_SOAINFRA.CUBE_INSTANCE GROUP BY STATE,COMPOSITE_NAME
ORDER BY 3 DESC;


Query to find the count of the instances of particular Composites created on particular time   

SELECT
    *
FROM
    (
        SELECT
            COUNT(composite_name) count,
            composite_name
        FROM
            soa_soainfra.cube_instance
        WHERE
            creation_date BETWEEN TO_DATE('09/20/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND TO_DATE('09/20/2021 23:00:00', 'MM/DD/YYYY HH24:MI:SS')
            AND composite_name IN ( '<<CompositeName1>>', '<<CompositeName2>>' )
        GROUP BY
            composite_name
    )
ORDER BY
    count DESC;


Query to find the execution time of BPEL instances based on composite name   

SELECT * FROM (SELECT COMPOSITE_NAME COMPOSITENAME,A.CMPST_ID COMPOSITE_INSTANCE_ID, CREATION_DATE BEGIN_TIME,MODIFY_DATE END_TIME , (EXTRACT(DAY FROM MODIFY_DATE - CREATION_DATE)*86400+ EXTRACT(HOUR FROM MODIFY_DATE - CREATION_DATE)*3600+EXTRACT(MINUTE FROM MODIFY_DATE - CREATION_DATE)*60+ EXTRACT(SECOND FROM MODIFY_DATE - CREATION_DATE)) DURATION_IN_SECOND,A.* FROM SOA_SOAINFRA.CUBE_INSTANCE  A WHERE STATE = 5 AND CREATION_DATE BETWEEN TO_DATE('09/20/2021 00:58:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('09/20/2021 23:59:59','MM/DD/YYYY HH24:MI:SS') AND COMPOSITE_NAME IN ('<<CompositeName>>')) ORDER BY COMPOSITE_NAME, DURATION_IN_SECOND DESC

Query to find mediator instances based on instance number or title 

SELECT * FROM MEDIATOR_INSTANCE A, COMPOSITE_INSTANCE B WHERE A.COMPOSITE_INSTANCE_ID = B.ID AND (A.COMPOSITE_INSTANCE_ID = '<<InstanceId>>' OR B.TITLE LIKE '<<Title>>')

Query to find/track receive or callback activities on instance number 

SELECT * FROM DLV_MESSAGE WHERE CIKEY IN (SELECT CIKEY FROM SOA_SOAINFRA.CUBE_INSTANCE  WHERE CMPST_ID='<<InstanceNumber>>')

Query to find payload of a composite instance based on instance number

SELECT A.DOCUMENT  FROM XML_DOCUMENT A,INSTANCE_PAYLOAD B,COMPOSITE_INSTANCE C WHERE A.DOCUMENT_ID = B.PAYLOAD_KEY AND B.INSTANCE_ID = C.ID AND B.INSTANCE_TYPE='COMPOSITE' AND A.DOCUMENT_TYPE = 2 AND B.INSTANCE_ID = <<InstanceNumber>>;  




If you like please follow and comment