Advertisements

Archive for March, 2013

Database Polling in OSB – Part I

Most of us have used DB Adapter in BPEL to perform operation in database. One of the features that is widely used is database polling when working with database. OSB also supports JCA DB adapter since it’s initial release in 11g. In a series of posts, i will show you different polling strategies in ACTION from OSB perspective.

I had used a use case of migrating data (smaller volume) from SI_EMPLOYEE table (source) to SI_EMPLOYEE_COPY (target) using DB adapter in OSB for this demo. And JDeveloper 11.1.1.6 and OSB 11.1.1.6 are used for this demo.

This post assumes that reader has basic understanding of working with DB Adapter in BPEL, OSB and using JDeveloper and Eclipse IDEs.

Create Business Service:

This business service merges the rows into target table using DB adapter. So create DB adapter files in JDeveloper by selecting the merge operation.

InsertDBOperation

In the next step of the wizard, select target table and finish the DB wizard to create all necessary files (.JCA, mappings, properties, WSDL and XSD). Import all of these files into Eclipse IDE and create business service from JCA file.

BusinessService

The JCA properties from configuration file can be verified and modified from the JCA transport tab of the business service.

JCA Transport

Delete Polling Strategy:

Create DB adapter files in JDeveloper by selecting Poll operation, source table and the polling strategy (After Read) as ‘Delete the Row(s) that were Read’.

Poll

table

AfterReadDelete

Import all of these files (JCA, mappings file, properties file, WSDL and XSD) into Eclipse IDE and create proxy service from .JCA file. Also the JCA properties can be verified in ‘JCA Transport’ tab including the polling strategy and the polling interval (60 sec in this scenario).

eclipsepoll

Complete the message flow in proxy service by adding Routing and Replace activity to transform $body as required by business service.

xquery

Deploy OSB project. Now insert a few rows in source table. Verify that polling happens and target table is populated with records and also the records got deleted from source table.

Before Polling:

b4poll

After Polling:

aftrpoll

Logical Delete Polling Strategy:

In this polling strategy, a column in the table is marked with particular value to designate the logical delete thus avoids DB adapter to pick it up the same record again and again. Currently our table does not have any column for this purpose, so add a column ISACTIVE VARCHAR2(1) in source table.

Create DB adapter files in JDeveloper by selecting Poll operation, source table and the polling strategy (After Read) as ‘Logical Delete’.

Poll

table

logdelete

Enter ‘Read Value’ as ‘N’ and ‘Unread Value’ as ‘Y’ so that DB adapter would only poll active records.

logdeleteac

In the next step of DB wizard, one can verify SQL statement used for polling and finish the wizard.

pollsql

Import all of these files (JCA, mappings file, properties file, WSDL and XSD) into Eclipse IDE and create proxy service from .JCA file. Also the JCA properties can be verified in ‘JCA Transport’ tab including the polling strategy and the polling interval (60 sec in this scenario).

logdelete1

Complete the message flow in proxy service by adding Routing and Replace activity to transform the payload as required by business service.

logdelete2

Deploy OSB project and disable the proxy DeleteRecordPS so that only our new proxy service will poll DB table.

disable

Insert a few rows in source table with value ‘Y’ for ‘ISACTIVE’ column.Verify that polling happens and target table is populated with records and also the records in source table are marked with value ‘N’.

Before Polling:

activewithY

withzero

After Polling:

withN

with107

The configuration jar of this demo OSB project can be downloaded from here.

Advertisements

Extending the Weblogic Domain

In this post, I quickly show you how to extend an existing weblogic domain which is quite simple and straight forward. Initially i have OSB domain and extended the same domain to include SOA.

Open the configuration wizard (config.cmd) from $MW_HOME/wlserver_10.3\common\bin and select the option as shown below and click on Next.

image

Select the domain directory from the selection browser.

image

Choose the products from the displayed list.

image

Give database schema details for new components that get added because of the selection in the previous screen. For example, in the current scenario the component schemas ‘SOA Infrastructure’ , ‘User Messaging Service’ and ‘SOA MDS Schema’ have to modified with correct database information as shown below and click on Next.

image

Make sure that connection is successful by observing the logs shown and click on Next twice.

image

Verify the configuration summary and click on Extend button to finish.

image

Once the domain is created, click on Done button and bring up the server to verify the installation of SOA server by opening the EM console and look for soa_infra.

image

Sending JSON payload requests from BPEL

In this blog post, I am sharing sample code that demonstrates the usage of JSON in BPEL context. The code is refactored into 2 projects containing the java utility code and  a sample BPEL process. The code can be downloaded from here.

Demonstrates:

Java Utility Code:

  • Using cookies to pass authentication info (username and password).
  • Sending XML payload as URL parameters using intermediate conversion to JSON for GET request.
  • Sending HTTP request through HTTP proxy.
  • Sending JSON or XML payload in the POST request.

BPEL Process:

  • Using BPEL preferences to store any configurable information.
  • Using java embedding activity to call the external java code. The jar file has to be kept in SCA-INF/lib to refer to java code.
  • Using Replay activity. The fault policy mechanism can also be used for retry functionality, if we throw BPEL related faults from java code.
  • Converting JSON to XML and vice-versa using java utility routines in BPEL.
  • An example on creating XSD similar to JSON data format representing the service response so that XPath expressions can be used in BPEL.

Does not cover:

  • HTTP Basic Authentication
  • Using XML APIs (like JAXP) to convert input XML to URL parameters
  • URL encoding while calling HTTP GET method
  • Using JAX-RS to invoke REST based services

FYI:

  • XMLToJSONUtil.java in the code zip file is taken from Biemond’s blog.
  • The libraries used for JSON processing can be downloaded from here .
  • JSR – 353 Java API for JSON Processing is closed for public review.

The code is uploaded for reference and  this may not be the only way to achieve this.

Query to find BPEL execution time

I had a scenario where business event is captured by a mediator in one of the composites and routes it to several BPEL processes in a sequence to complete particular business flow. I had to know the time taken by each of these BPELs in my end-to-end flow. Instead of going through each of the BPEL instance in EM console, I came up with the following query to find out the time taken. I used the ECID to correlate all the BPEL instances generated during my process flow. Check if it’s of help to any.

SELECT INSTANCE_KEY, COMPOSITE_NAME, COMPONENT_NAME,
       CREATION_DATE, MODIFY_DATE, EVAL_TIME
FROM (SELECT INSTANCE_KEY, COMPOSITE_NAME, COMPONENT_NAME,
               CREATION_DATE, MODIFY_DATE, EVAL_TIME
       FROM BPEL_PROCESS_INSTANCES
        WHERE instance_key IN (SELECT cikey FROM CUBE_INSTANCE
                              WHERE ecid = <<ecid>>)
       UNION ALL
       SELECT 1 INSTANCE_KEY, ‘Comp’ COMPOSITE_NAME, COMPONENT_NAME,
             CREATED_TIME CREATION_DATE, UPDATED_TIME MODIFY_DATE,
             CASE
          WHEN UPDATED_TIME IS NOT NULL THEN
            (extract(DAY FROM (UPDATED_TIME – CREATED_TIME)) * 24 * 60 * 60 +
             extract(hour FROM (UPDATED_TIME – CREATED_TIME)) * 60 * 60 +
             extract(minute FROM (UPDATED_TIME – CREATED_TIME)) * 60 +
             extract(second FROM (UPDATED_TIME – CREATED_TIME))) * 1000
         ELSE NULL
             END AS eval_time
        FROM MEDIATOR_INSTANCE
    WHERE ecid = <<ecid>>
       )
ORDER BY INSTANCE_KEY;

In case of the multiple events, we can rewrite the query if the composites are using sensors. The following query has been used in our case to monitor and to get execution times, minimum and maximum time taken for each of the component in process flow.

/*SELECT ECID, INSTANCE_KEY, CID, COMPOSITE_NAME, COMPONENT_NAME, CREATION_DATE,  MODIFY_DATE, EVAL_TIME */
–SELECT COMPONENT_NAME,  min(EVAL_TIME), max(eval_time)
SELECT ECID, count(component_name) cnt
FROM (SELECT ECID, CIKEY INSTANCE_KEY, CIKEY CID, COMPOSITE_NAME, COMPONENT_NAME, CREATION_DATE, MODIFY_DATE,
        CASE
        WHEN MODIFY_DATE IS NOT NULL
        THEN (extract(DAY FROM (MODIFY_DATE – CREATION_DATE)) * 24 * 60 * 60 +
              extract(hour FROM (MODIFY_DATE – CREATION_DATE)) * 60 * 60 +
              extract(minute FROM (MODIFY_DATE – CREATION_DATE)) * 60 +
              extract(second FROM (MODIFY_DATE – CREATION_DATE))) * 1000
          ELSE NULL
              END AS EVAL_TIME
      FROM CUBE_INSTANCE
      WHERE ecid IN (SELECT b.ecid FROM COMPOSITE_SENSOR_VALUE a, MEDIATOR_INSTANCE b
                                               WHERE sensor_name = <<sensor name>>
                            AND  a.composite_instance_id = b.composite_instance_id
                                        AND string_value IN (<<values>>)
                        )                           
    UNION ALL
    SELECT a.ECID, 1 INSTANCE_KEY, a.composite_instance_id CID,
               ‘Comp’ COMPOSITE_NAME, a.COMPONENT_NAME,
              a.CREATED_TIME CREATION_DATE, a.UPDATED_TIME MODIFY_DATE,
              CASE
        WHEN UPDATED_TIME IS NOT NULL
        THEN (extract(DAY FROM (UPDATED_TIME – CREATED_TIME)) * 24 * 60 * 60 +
              extract(hour FROM (UPDATED_TIME – CREATED_TIME)) * 60 * 60 +
              extract(minute FROM (UPDATED_TIME – CREATED_TIME)) * 60 +
              extract(second FROM (UPDATED_TIME – CREATED_TIME))) * 1000
          ELSE NULL
              END AS eval_time
      FROM MEDIATOR_INSTANCE a, COMPOSITE_SENSOR_VALUE b
      WHERE a.composite_instance_id = b.composite_instance_id
        AND b.sensor_name = <<sensor name>>
        AND b.string_value IN (<<values>>)
  )
–GROUP BY COMPONENT_NAME
GROUP BY ECID order by cnt

–ORDER BY ecid, INSTANCE_KEY, CID;

Note: Based on the requirement, the above select and group by statements have to be commented/uncommented.


Advertisements

Pages

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 331 other followers

Enter your email address to follow this blog and receive notifications of new posts by email.