SOA 11g - Useful SOA Infra SQL queries for Oracle SOA Production Support - Part III ( Get SOA Payload from Back end tables ( SOA Infra) )

This post will be helpful when there is a need to reprocess high number of instance due to any server outage or data source outage.

Query to retrieve the payload from back end tables:

select Document from (select Document From Xml_Document Where 1 = 1 and Document_Id in (select document_id from document_ci_ref where cikey in (Select B.Cikey From Composite_Instance A, Cube_Instance B Where A.Ecid = B.Ecid And Nvl(A.Conversation_Id,'A')  = Nvl(B.Conversation_Id,'A') And Id = #Composite Instance ID)) order by doc_partition_date) a where rownum <=1

Write a Java program that automates the reprocessing job :- 

• Connect to SOA infra database
• Retrive the payload using above query
• Convert the blob to string
• Add SOA envelope to the retrived payload
• Generate the end point url using the values from cube instance table for this particular composite
• use HTTP post to invoke the composite

Above steps is just a way to automate the reprocessing, you can use this query and automate in any other possible way as well. 

Code snippet to convert the blob to string...

                                    Blob blob = payloadDetail.getBLOB("DOCUMENT");

                                    BinXMLStream inpbin = proc.createBinXMLStream(blob);

                                    BinXMLDecoder dec = inpbin.getDecoder();

                                    InfosetReader xmlreader = dec.getReader();

                                    XMLDocument doc = (XMLDocument)domimpl.createDocument(xmlreader);

                                    //doc.print(System.out);

                                    TransformerFactory tf = TransformerFactory.newInstance();

                                    Transformer transformer;

                                    transformer = tf.newTransformer();

                                    

                                    //Convert Xml to String                       

                                    StringWriter writer = new StringWriter();

                                    transformer.transform(new DOMSource(doc), new StreamResult(writer));

                                    String output = writer.getBuffer().toString();

Use this and write your process accordingly...

SOA 11g - Useful SOA Infra SQL queries for Oracle SOA Production Support - Part II ( Queries and its usage)

Query to find the count of instances for individual composites :

SELECT count(*),SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1)
FROM COMPOSITE_INSTANCE WHERE
CREATED_TIME > TRUNC(SYSDATE)-7 AND CREATED_TIME < TRUNC(SYSDATE)
GROUP BY SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1);

Normally composite_dn will be like XX_Test/HelloWorld!1.0*soa_36243828-42da-4952-9579-f58c0e913705, so to overcome that, we can use below code to get the composite name alone.

"SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1)"

This query is used to get the instance count of all composites, all states between timestamps. 
Filters can be added and modified to get the results as required. 

How to find the instance ID if we have composite sensors in our composites:

SELECT Ci.*,
  number_value Delivery_ID
FROM composite_sensor_value csv,
  (SELECT *
  FROM COMPOSITE_INSTANCE
  WHERE CREATED_TIME > TRUNC(SYSDATE)-7
  AND CREATED_TIME  < TRUNC(SYSDATE)
  AND SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1) IN ('%Composite_Name%')
    --AND STATE <> '1'
  )CI
WHERE CSV.COMPOSITE_INSTANCE_ID = CI.ID
AND SENSOR_NAME                 = '%Sensor_Name%'
AND NUMBER_VALUE                = '%Sensor_Values%'
ORDER BY CREATED_TIME;
Add or modify filters to get the results as expected. 

If you are not sure about the type of sensor, use this substr(clob_value, instr(clob_value,'>',1,1)+1, instr(clob_value,'</',1,1)-1) to filter or select the values. 

if you want to get the details for particular state, then read this post to understand the state and description.

Query to get the count of instance on hourly basis:

SELECT TO_CHAR(TRUNC(ci.CREATED_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS'), COUNT (*) FROM COMPOSITE_INSTANCE CI
WHERE CREATED_TIME > TO_DATE('19-JUN-2014 08:35:10', 'DD-MON-YYYY HH24:MI:SS') AND
CREATED_TIME < sysdate
GROUP BY TO_CHAR (TRUNC (CI.CREATED_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS')
ORDER BY TO_CHAR (TRUNC (CI.CREATED_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS');

Modify the filter created_time accordingly to get the expected results. 
You can add composite_dn filter to get the hourly count of instances for one particular composite. 

Query to get the count of instances on different state:

SELECT COUNT(*),
  DECODE(cube_instance.STATE, 0, 'Initiated', 1, 'Running', 2, 'Suspended', 3, 'Faulted', 4, 'Closed Pending', 5, 'Closed Completed', 6, 'Closed Faulted', 7, 'Closed Cancelled', 8, 'Closed Aborted', 9, 'Closed Stale', 10,'Closed Rolled Back','unknown') state
FROM CUBE_INSTANCE
WHERE creation_date > TRUNC(sysdate)-1
AND creation_date   < sysdate
GROUP BY STATE;

If you want to get the same details on composite wise,

SELECT COUNT(*),
  DECODE(CUBE_INSTANCE.STATE, 0, 'Initiated', 1, 'Running', 2, 'Suspended', 3, 'Faulted', 4, 'Closed Pending', 5, 'Closed Completed', 6, 'Closed Faulted', 7, 'Closed Cancelled', 8, 'Closed Aborted', 9, 'Closed Stale', 10,'Closed Rolled Back','unknown') STATE ,
  COMPOSITE_NAME
FROM CUBE_INSTANCE
WHERE CREATION_DATE > TRUNC(sysdate)-1
AND CREATION_DATE   < SYSDATE
GROUP BY STATE,
  COMPOSITE_NAME
ORDER BY COMPOSITE_NAME;

Above are the major tables and possible joins, so we can rewrite as per our requirement and get the expected results quickly. 

Caution - From my experience, while running queries in Production SOA infra schema, make sure you restrict the number of records to search by adding date filter else it may thorow temp table space error.