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>>;