Query XML Payload of a SOA Composite

Although SOA has a user-friendly enterprise manager console to view the instances/flows/payloads, sometimes we would end up wanting to programmatically fetch the payload from the backend Soa-infra tables.

The following code would be useful in such cases. 

Following query on the SOA INFRA tables will fetch the xml payload as BLOB.

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

We are querying against the following tables to fetch xml payload. We are querying against the following tables to fetch the xml payload

XML_DOCUMENT - Table in which the actual payload xml is stored as BLOB

INSTANCE_PAYLOAD - Table which provides the payload metadata of the instance

COMPOSITE_INSTANCE - Table which provides the composite instance details

Now, the next step is to convert the BLOB into actual xml payload.

Following Java class code can be used to convert and print the xml payload of the instance.

package client;

import java.io.IOException;

import java.sql.*;

import oracle.xml.parser.v2.XMLDOMImplementation;

import oracle.xml.binxml.BinXMLStream;

import oracle.xml.binxml.BinXMLDecoder;

import oracle.xml.binxml.BinXMLException;

import oracle.xml.binxml.BinXMLProcessor;

import oracle.xml.scalable.InfosetReader;

import oracle.xml.parser.v2.XMLDocument;

import oracle.xml.binxml.BinXMLProcessorFactory;

public class QueryPayload {

    public QueryPayload() {

        super();

    }

    

    public static void main(String args[]) throws BinXMLException, IOException {

        try{

            Class.forName("oracle.jdbc.OracleDriver");            

            Connection con=DriverManager.getConnection( 

            "jdbc:oracle:thin:@test/dbconnection","userid","password");

            Statement stmt=con.createStatement();

           

            String sqlstmt = " select a.document from xml_document a,

            "            instance_payload b,composite_instance c \n" +

            "            where a.document_id = b.payload_key  \n" +

            "            and b.instance_id = c.id  \n" +

            "            and b.instance_type='composite'  \n" +

            "            and a.DOCUMENT_TYPE = 2  \n" +

            "            and b.instance_id = 123456    ";

            ResultSet rs = stmt.executeQuery(sqlstmt);

            while(rs.next()) {          

               XMLDOMImplementation domimpl = new XMLDOMImplementation();

               BinXMLProcessor proc = BinXMLProcessorFactory.createProcessor();

               BinXMLStream inpbin;

            

                inpbin = proc.createBinXMLStream(rs.getBlob(1));

                BinXMLDecoder dec = inpbin.getDecoder();

                InfosetReader xmlreader = dec.getReader();

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

                doc.print(System.out);               

            }

            con.close();

        } catch (ClassNotFoundException e) {

        } catch (SQLException e) {

        }

    } }

Hope this blog was helpful.

SOA INFRA SQL Queries

All of us working in SOA are familiar with Enterprise Manager(EM) console where we can view the composites deployed, check the instances of execution and also flow of instances. This console helps us to analyze any issues that occur in SOA.

But sometimes, when there is huge load leading to multiple instances, it becomes very difficult to check in the EM console. This is when we can make use of the SOA Infra tables where the data shown in EM console is actually saved. You can also query these tables to find the performance of a flow like the counts, duration etc.

Here are few queries which were quite helpful for me and hope they would be helpful for you too.
Most of these queries are based on instance number, composite name, time and composite title as this is the handy information most of us would have.

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 CUBE_INSTANCE WHERE CREATION_DATE BETWEEN TO_DATE('09/20/2016 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('09/20/2016 23:00:00','MM/DD/YYYY HH24:MI:SS')  AND COMPOSITE_NAME IN (‘<<CompositeName1>>’, ‘<<CompositeName2>>’) GROUP BY COMPOSITE_NAME ) ORDER BY COUNT DESC

Above query will give the count of both success and failed instances. In order to filter successful vs failed, add STATE clause as follows.

SELECT * FROM (SELECT COUNT(COMPOSITE_NAME) COUNT,COMPOSITE_NAME FROM CUBE_INSTANCE WHERE CREATION_DATE BETWEEN TO_DATE('09/20/2016 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('09/20/2016 23:00:00','MM/DD/YYYY HH24:MI:SS') AND COMPOSITE_NAME IN (‘<<CompositeName1>>’, ‘<<CompositeName2>>’) AND STATE=5 GROUP BY COMPOSITE_NAME ) ORDER BY COUNT DESC

State = 5 is successful instance

https://blogs.oracle.com/ateamsoab2b/entry/list_of_all_states_from

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 CUBE_INSTANCE A WHERE STATE = 5 AND CREATION_DATE BETWEEN TO_DATE('09/20/2016 00:58:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('09/20/2016 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 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>>;  

Business Rules with Decision Tables in SOA 11g

In my previous blog, we have seen how to work with business rules using if/else rule sets. I will take the same example/scenario here to show how decision tables in business rules work.

You can find my previous blog about business rules here.

Before we get into the actual implementation, there are some business rules components, we need to get familiar with.

1. Facts – The data on which the rules will be applied. The input schema/message of the business rules service component usually acts as fact.
2. Functions – These can be compared to java functions. We can define a custom function which takes inputs, performs complex logic around the data (even looping around the data) and returns some output.
3. Rule Sets – Set of rules forms this component. Multiple rules can be combined together in an executable sequence. There are 2 types of rule sets – If/Then rule sets and Decision Table.
4. Globals – These are variables which can be defined as constants or modifiable fields and can be used across rules and rule sets.
5. Bucket Sets – Bucket Sets contain a set of values which can be used in decision tables. These set of values can be
1. List of Values derived from the enumeration of a schema
2. List of Value ranges Eg: 0 – 10, 11 – 60, 61 - 255, etc for ip address ranges
3. Random LOV types – String LOVs, Integer LOVs, etc

Scenario goes like this - An online order management system takes orders from customers. This business rules project checks if customer is eligible for any discounts or offers, based on various parameters and business rules. Here are the rules 

Repeating customer –

a.    Repeating Customer gets 20% off

b.    If purchase amount >= 50, free shipping

c.    If purchase amount < 50, no free shipping

New customer –

a.    If purchase amount >= 50 and < 100, free shipping

b.    If purchase amount >=100, free shipping + 15% off

c.    If purchase amount <50, not eligible for any offer

Steps to create the project:

1. Create a simple SOA project - OfferCheck.

2. Create an XSD which would hold the fields required for the input and output of this service. I am using the following xsd.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

            xmlns="http://www.offer.com/pg"

            targetNamespace="http://www.offer.com/pg"

            elementFormDefault="qualified">

  <xsd:element name="CustomerInformation" type="CustomerInformationType"/>

  <xsd:complexType name="CustomerInformationType">

    <xsd:sequence>

      <xsd:element name="RepeatingCustomer" type="xsd:string"/>

      <xsd:element name="PurchaseAmount" type="xsd:double"/>

    </xsd:sequence>

  </xsd:complexType>

  <xsd:element name="Offer" type="OfferType"/>

  <xsd:complexType name="OfferType">

    <xsd:sequence>

      <xsd:element name="OfferApplied" type="xsd:string"/>

      <xsd:element name="FreeShipping" type="xsd:string"/>

      <xsd:element name="PercentageDiscount" type="xsd:int"/>

    </xsd:sequence>

  </xsd:complexType>

</xsd:schema>

3. Drag and drop a business rule component and choose CustomerInformation as the input and Offer as output from the above xsd. Make sure that the Expose as Composite Service is checked and click ok.

4. In the Rules editor, you would find several sections. Click on facts. This screen will show you the information (from schemas - input/output) which can be used to create rules.

5. In the Bucketsets section, by default, you would see the enumeration fields from the schema. If the schema does not have enumeration, this section would be empty. In this scenario, as we need to check the amount spent between ranges, we will create a bucketset as follows.

6. Click on the + icon at the right and choose List of Ranges.

7. By default, you would see an infinity row. On clicking on the = icon further, it will allow you enter the lowest range you want to check. As this scenario, < 50 is the lowest. Hence, we would enter 50 in the end point column of the new row and click enter. The Range and Alias column get defaulted as below. You can add description.

8. As you further click on the + icon, it would add the ranges and you can modify as required. The final Bucketset as per our requirement would be as follows.

9. Now click on the RuleSet section, you would see two options there - If/Then Rule, Decision Table. Click on Create Decision Table.

10. In the decision table, you would find two sections, <insert condition> where the conditions to be checked are inserted, <insert action> where the result is captured.

11. Our first condition is to check if the Customer is repeating or not. Click on + icon on the right top and click on Condition to add a new condition.

12. A new row will get added. The drop down in the conditions row will show you all the value options from which you can frame the condition.

13. Expand CustomerInformationType and click on repeatingCustomer as follows. C1 condition gets added.

14. Click on the Row R1. As we need to check Repeating Customer value as Y, manually enter as "Y".

15. Now to add a new rule, click on the same + icon, and click on Rule. Under Row R2, click as otherwise.

16. Our next condition is on the purchaseAmount. Similar to C1, insert a new condition C2 and choose purchaseAmount from the drop down.

17. Above the conditions section, you will by default see "Local List of Values". Keep condition C2, selected and choose SpentAmount from the "Local List of Values" drop down. As purchaseAmount needs to be checked based on a range, we will use SpentAmount Bucketset here to set the rule for condition C2.

18. Right click on R1 corresponding to C2 and click Split Selected Cell.

As SpentAmount Bucketset has 3 ranges. The cell would be automatically split into 3 parts and defaulted with all the ranges in the bucketset as follows.

19. Perform the same step with the other rule under C2.

20. Finally you will see all the rules and conditions updated as follows.

21. Next step is to update the output.

22. Under Action, click on <insert action>, assert new.

23. In the pop window, choose OfferType in the Facts and choose all the 3 fields - freeShipping, offerApplied, percentageDiscount as parameterized and click ok.

24. You would now see all these fields in the actions. Now corresponding to each condition, set the constant output that should be returned.

25. The final screen would look as follows.

26. Deploy the project and test. You would find the results as per the scenario.

These business rules can be changed from SOA composer console without touching the code as shown in my previous blog.