Oracle database integration using Advance Queuing (AQ)

AQ is a proprietary messaging technology from Oracle that can be handy for integrating Oracle databases with applications (e.g. OSB, BPEL, Java, even VisualBasic) or other Oracle databases.

The advantages of using AQ (messaging integration style) compared to the traditional jdbc connection (database integration style):

• reliability (AQ supports message persistence, retry)
• avoid sharing/locking problem of database integration style
• messaging integration style is more scalable than point to point integration using jdbc connection

Architecture example

A typical use case is a data push scenario: a change in the database (e.g. employee data) needs to be propagate to different applications/databases.

• a queue message is generated in a Oracle database (e.g. using plsql / trigger when an update is occurred)
• a jca adapter is made using JDeveloper/SOA Suite (AQ adapter)
• using this jca adapter the queue can be consumed by OSB, Soa Suite BPEL or Java code.
• the queue can be also consumed by other Oracle databases

The AQ also supports more scenarios such as publish-subscribe or sending messages in opposite directions than in the usecase above (the message is produced by applications and then consumed by Oracle database).

How

Create the payload object using CREATE TYPE payload_name AS OBJECT
Create the queue table using DBMS_AQADM.CREATE_QUEUE_TABLE 
Create the queue using  DBMS_AQADM.CREATE_QUEUE 

To use:

Start the queue using DBMS_AQADM.START_QUEUE
Create a payload
Send the queue payload using DBMS_AQ.ENQUEUE

You can see the numbers of messages in the queue using: SELECT COUNT(*) FROM queue_table_name;
You can view the messages payload using: SELECT user_data FROM queue_table_name;

For examples, please refer to http://www.oracle-developer.net/display.php?id=411

For generating jca adapter using JDeveloper and the example of consuming the queue using OSB, please refer to the book" Oracle Service Bus 11g Development Cookbook" chapter 7.

Notes
You need these priveleges:  AQ_ADMINISTRATOR_ROLE to create the queue and EXECUTE ON DBMS_AQ to execute the plsql to send the queue.

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)

---

References:

Distributed Transactions

The goal

The goal of transactions is to maintain data integrity & consistency  in case of concurrent accesses to data (e.g. multiple web sessions update the same database) and/or distributed resources (e.g. sending data to multiple databases/JMS resources). Typically a transaction has the ACID (atomic, consistent, isolated, and durable) properties in order to guarantee data integrity. 

X/Open Distributed Transaction Processing (DTP) standard

This is a popular distributed transaction standard. The architecture diagram (from Kosaraju, javaworld.com):

The Transaction Manager manages the transaction: manage transaction context, maintain associations with participating resources, conduct 2PC and recovery protocol. You can use open source transaction manager such as Atomikos. Transaction manager is also included in enterprise platforms such as Weblogic.

The Resource manager is a wrapper over a resource (e.g. database, JMS message broker) that implements XA interface for participating in transactions with a transaction manager. The transaction manager keeps track which resources that are participating in the transaction using resource enlistment process. For example in the case of Oracle database you get the resource manager by using an XA capable Oracle JDBC database driver.

TX interface is the interface between your application and the transaction manager. The important methods in this interface are such as for transaction demarcation  (e.g. begin, rollback, commit).

XA interface is the interface between resource managers and the transaction manager. The important methods in this interface are such as start, end, prepare (the first phase of 2PC), and commit  (the second phase of 2PC),.

For more details please read the article by Allamaraju (see the references below.)

Java Transaction API (JTA) and Java Transaction Service (JTS)

The architecture diagram (by Allamaraju):

In this transaction management from Sun, the API (JTA) follows the X/Open DTP interfaces while the transaction manager (JTS) implements the OMG OTS specification. OMG Object Transaction Service (OTS) standard is basically an X/Open DTP with additional features such as CORBA interfaces, nested transactions/finer granularity, synchronization protocol.

2PC (two-phase commit) protocol

This is a type of distributed transaction protocol to achieve data integrity. First the transaction manager issues prepare requests to all resource managers. If all the resource managers ready to commit (thus a kind of voting), the transaction manager will issue the commit request otherwise the transaction manager will issue the rollback.

Java examples:

Oracle JDBC XA driver

In the application, programmatically you can implement 2PC voting by using the statuses returned by resource managers after you call prepare() to all the resource manager via XA interface. Based on this voting you can perform either commit() or rollback() for all resource managers. An example of code (Oracle JDBC): http://docs.oracle.com/cd/E11882_01/java.112/e16548/xadistra.htm#autoId18

Spring with open sources JTA

Spring offer an extra abstraction layer for loose coupling between transaction management implementations & resources (JTA implementations (such as Atomikos, JBossTS), JPA/EJB3, Hibernate, single JDBC, JMS) and the application you write.

Kosaraju wrote a good article (with sample codes) about XA-2PC transaction examples in Spring using open sources JBossTS (usecase: 2 databases) and Atomikos (usecase: 1 database + 1 JMS/ActiveMQ): http://www.javaworld.com/javaworld/jw-04-2007/jw-04-xa.html

This is the sequence diagram (by Kosaraju) of the Atomikos case:

Rather than programmatic, you can better implement transaction using declarative way (either with annotation or AOP) since transaction management is a cross cutting concern across your applications and can be separated from your core business logic. It's interesting how Spring's declarative transaction,  transaction template and the transaction manager hide the complexities of programmatic transaction. In the sequence diagram above, the proxy (which is created by declarative transaction) invokes the transaction manager TX interface (doBegin, do Commit). We see also how the XA interface methods (start, end, prepare, commit) are used in the XA resources (database, jms).

While 2PC provides data integrity, its performance is expensive. You may trade off the data integrity for performance using relaxations of XA-2PC (e.g. XA-1PC, XA-Last resource) or non XA (Shared resource, Best effort 1PC). Please read (with sample codes): http://www.javaworld.com/javaworld/jw-01-2009/jw-01-spring-transactions.html

If you need to learn the basic of Spring transaction (with IoC, annotations, AOP, templates), please read the chapter 16 of the "Spring Recipes" book (with example codes).

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)

---

Reference:

Nuts and Bolts of Transaction Processing by Allamaraju
http://www.subbu.org/articles/transactions/NutsAndBoltsOfTP.html
http://www.subbu.org/articles/jts/JTS.html


Spring Recipes by Mak



Java Transaction Processing





Java Transaction Design Patterns

XA transactions using Spring by Kosaraju
http://www.javaworld.com/javaworld/jw-04-2007/jw-04-xa.html

Distributed transactions in Spring, with and without XA by Syer
http://www.javaworld.com/javaworld/jw-01-2009/jw-01-spring-transactions.html

Oracle Database JDBC Developer's Guide
http://docs.oracle.com/cd/E11882_01/java.112/e16548/xadistra.htm#autoId18

Asynchronous messaging for SOA integration

The advantages of asynchronous messaging:
• asynchronous communication: the client doesn't have to wait for the reply
• more reliable than RPI style http web service (e.g. you can use guarantee delivery / durable subscriber patterns)
• loose coupling, thus more resistant to change
• more immediate than integration via file transfer
• avoid schema sharing problem and locking problem of database integration style

Here are some of my blogs about asynchronous messaging for integration:

Messaging patterns implementation using Oracle OSB:
* Asynchronous web service using Oracle OSB
http://soa-java.blogspot.com/2012/01/asynchronous-web-service-using-oracle.html
* Reliable SOAP over http in Oracle OSB: Point-to-Point Channel
http://soa-java.blogspot.com/2012/01/reliable-soap-over-http-in-oracle-osb.html
* Event-driven Publish-Subscribe SOAP over http messaging in Oracle OSB
http://soa-java.blogspot.com/2012/01/soap-over-http-in-oracle-osb-publish.html
* Request-Reply messaging using Oracle OSB
http://soa-java.blogspot.com/2012/01/request-reply-messaging-using-oracle.html

Messaging patterns for scalability problems:
* Claim check messaging pattern with Oracle OSB
http://soa-java.blogspot.com/2012/01/claim-check-messaging-pattern-with.html
* Message sequence pattern
http://soa-java.blogspot.com/2012/01/message-sequence-pattern.html

Others
* How to setup JMS resources in Weblogic via console and WLST Python script
http://soa-java.blogspot.com/2012/01/how-to-setup-jms-resources-in-weblogic.html
* Asynchronous web service using Java (JAX-WS)
http://soa-java.blogspot.com/2012/01/asynchronous-web-service-using-java-jax.html

---

Please share comment.

Source: Steve's blog http://soa-java.blogspot.com

References

A compilation of messaging patterns, a definitive reference, useful also for example to understand the patterns in Spring Integration and other message oriented middleware products:
Hohpe's Enterprise Integration Patterns
http://eaipatterns.com/



A very good book, very practical if you need to use OSB for your work:
Oracle Service Bus 11g Development Cookbook

This book teach you not only about how to use OSB but also about SOA architecting, governance, versioning and many other "life" lessons dealing with SOA projects:
The Definitive Guide to SOA: Oracle Service Bus

Asynchronous web service using Java (JAX-WS)

In his blog, Edwin Biemond described how to build a asynchronous http web service in Java (Java code first then generate wsdl using JAX-WS).

Summary of the tricks:
• using annotation @OneWay() for asynchronous operation when you define your web service operation using @WebMethod().
• in de http request headers include ws-addressing wsa:MessageID (so that the recepient of the callback can correlate the callback reply message with the request message) and wsa:ReplyTo/wsa:Address (so that the asyn service knows to which url it has to send the callback reply)
• in de callback reply headers include ws-addressing wsa:RelatesTo, which has the same value with the request's MessageID (so that the recepient of the callback can correlate the callback reply message with the request message)

---

See also blogs compilation about messaging for integration: http://soa-java.blogspot.nl/2012/01/asynchronous-messaging-for-integration.html


Please share comment.

Source: Steve's blog http://soa-java.blogspot.com

References

A good overview for ws-addressing, ws-policy, ws-reliable messaging, SOA transactions, security


http://eaipatterns.com/
Hohpe's Enterprise Integration Patterns

Request-Reply messaging using Oracle OSB

In his blog, Eric Elzinga showed a simple way to implement Request-Reply messaging pattern with Oracle OSB




You can implement the business logic in the proxy's message flow, perhaps with publish / service callout to forward the message to another services while sending the ack using the jms reply.
---------------------------------------------------

---

See also blogs compilation about messaging for integration: http://soa-java.blogspot.nl/2012/01/asynchronous-messaging-for-integration.html

Please share comment.

Source: Steve's blog http://soa-java.blogspot.com

References
http://eaipatterns.com/
Hohpe's Enterprise Integration Patterns


The Definitive Guide to SOA: Oracle Service Bus

Asynchronous web service using Oracle OSB

In his blog, Edwin Biemond described how to build a Oracle OSB adapter, basically it's a web service  (http) that wraps jms-based asynchronous services (you can implement the async jms service for example using Message Driven Beans MDB, jms-based Java code, Oracle SOA suite or with OSB jms proxy).


Summary:
• in de web service (http) request headers include the ws-addressing wsa:MessageID (so that the recepient of the callback can correlate the callback reply message with the request message) and wsa:ReplyTo/wsa:Address (so that the asyn service knows to which url it has to send the callback reply)
• In the jms message, include de CorrelationID to convey the value from wsa:MessageID, and replyURL from wsa:ReplyTo/wsa:Address.
• in the web service (http) callback reply headers include ws-addressing was:RelatesTo, which has the same value with the request's MessageID (so that the recepient of the callback can correlate the

---

See also blogs compilation about messaging for integration: http://soa-java.blogspot.nl/2012/01/asynchronous-messaging-for-integration.html

Please share comment.

Source: Steve's blog http://soa-java.blogspot.com

References
http://eaipatterns.com/
Hohpe's Enterprise Integration Patterns


The Definitive Guide to SOA: Oracle Service Bus

Claim check messaging pattern with Oracle OSB

Goal:
• reduce the message size to ease a performance problem
• make sure the message size conforms the size limitation of the messaging system (e.g. 4 mb limit in the MQMQ)



When the messages are too big to be processed efficiently by web services / transformation, it's better to save some parts of the message in database before the processing and then enrich the result of the processing with the saved data from the database.

---

See also blogs compilation about messaging for integration: http://soa-java.blogspot.nl/2012/01/asynchronous-messaging-for-integration.html


Please share comment.

Source: Steve's blog http://soa-java.blogspot.com

References

http://eaipatterns.com/
Hohpe's Enterprise Integration Patterns


The Definitive Guide to SOA: Oracle Service Bus

Message sequence pattern

Goal:
• to reduce the message size to ease a performance problem (divide & conqueror)
• to make sure the message size conforms the size limitation of the messaging system (e.g. 4 mb limit in the MQMQ)


• The message is split into smaller chunks. Each of these chunks are processed independently by a service process. Finally the results are aggregated back to a single result (for further processing or for reply.)
• The sequence information (message-id, sequence-id, total-sequence, reply address etc) are added in the headers (to avoid parsing the body).
• The construction is generic, de change needed in the business processes are kept to minimum for example by using wrappers/handlers to manage the headers.
• After a timeout period, the incomplete chunk results will go to an error-queue.


Its parallel version is the split-aggregate pattern:


This open an interesting possibility with cloud computing: the processes can be computed for example using map-reduce algorithm from Hardoop. This solution is scalable but we need to consider the latency cost due to the network traffic.

---

See also blogs compilation about messaging for integration: http://soa-java.blogspot.nl/2012/01/asynchronous-messaging-for-integration.html


Please share comment.

Source: Steve's blog http://soa-java.blogspot.com

References

Hohpe's Enterprise Integration Patterns... one of the most popular SOA books in the market

How to setup JMS resources in weblogic : console and WLST Phython script

Manually via console
1. create a persistent store (for transaction-enable jms server)
console: service>persistent store
2. create a jms server
console: service > messaging > jms server
3. create a jms module
console: service > messaging > jms module
4. create a subdeployment
click jms module > subdeployment tab
5. create a connection factory and jms queue/topic
click jms module > configuration tab

Automation using WLST python scripts
1. create a persistent store (for transaction-enable jms server)
myfileStore = create('myFileStore','FileStore')
2. create a jms server
myjmsserver = create(' ...name...','JMSServer')
myjmsserver.addTarget(myserver)
myjmsserver.setPersistentStore(myfileStore)
3. create a jms module
jmsMySystemResource = create(" ...modulename...","JMSSystemResource")
jmsMySystemResource.addTarget(myserver)
4. create a subdeployment
subdeploy = jmsMySystemResource.createSubDeployment('...Subdeploy name....')
subdeploy.addTarget(myjmsserver)
5. create a connection factory and jms queue/topic
theJMSResource = jmsMySystemResource.getJMSResource()
connf = theJMSResource.createConnectionFactory('...name...')
connf.setJNDIName('...jndi...')
connf.setLocalJNDIName('...jndi....')
connf.setSubDeploymentName('...subdeploy name....')

myqueue = theJMSResource.createQueue('...name...')
myqueue.setJNDIName('...jndi....')
myqueue.setSubDeploymentName('...subdeploy name...')

mytopic = theJMSResource.createTopic('...name...')
mytopic .setJNDIName('...jndi....')
mytopic .setSubDeploymentName('...subdeploy name...')



Run this script using weblogic.WLST java class:
command line: java weblogic.WLST myscript.py
or using Ant:

<java classname="weblogic.WLST" fork="yes" classpathref="wlst.class.path">
<arg line="myscript.py" />
</java>

Please download an example of the python code here.

---

See also blogs compilation about messaging for integration: http://soa-java.blogspot.nl/2012/01/asynchronous-messaging-for-integration.html

Please share comment.

Source: Steve's blog http://soa-java.blogspot.com

References:
Professional Oracle WebLogic Server


The Definitive Guide to SOA: Oracle Service Bus

Event-driven Publish-Subscribe SOAP over http messaging in Oracle OSB

This blog discuss how to implement Publish-Subscribe (one producer, multi subscribers) channel in Oracle OSB. This system embodies Event-driven architecture: the consumers listen to event-messages, published to a topic, by the producer. This channel is reliable, i.e. it implements:
• durable subscriber: to avoid missing messages when the consumer is not listening (e.g. disconnect for maintenance)
• guarantee delivery: to make sure that all messages are delivered even when the consumer fail
• idempotent receiver: dealing with duplicate messages (receive no duplicate messages)
We use SOAP with WSDL since it's a good practice to define your interfaces with WSDL. We use http since in our existing system we use a lot of RPI-style web services over http.

How
1. build a jms producer business service (BS): SOAP based on the jmssimple.wsdl (downloadable below)
transport tab: jms protocol and the queue address (please read my other blog about how to setup jms resources in Weblogic)
jms transport tab: topic type, message type: text... handy for test since it's readable,no response
2. build one or more jms consumer proxy: SOAP also based on the jmssimple.wsdl,
transport tab: jms protocol and the queue address (same as the address in the BS),
jms transport tab: topic type and message type: text, durable susbcription, XA enable
message handling tab: enable transaction & same transaction response. The transaction will rollback (i.e. put the message back to the queue) in case if a fail consumer, thus this enables guarantee delivery.
3. wrap the producer BS with a proxy: SOAP with a wsdl, http transport.
Add a routing options in the request routing in the proxy message flow, enable the Quality of Service in this option.
4. make a consumer BS point to other webservice (e.g. a FileWriter in my example.)


Test:
1. Send a http message (e.g. using SOAPUI) to the producer proxy, the FileWriter service will write a file containing the message from the consumer BS.
2. Disable the FileWriter service
3. Send a http message to the producer proxy again.
4. After awhile turn on the FileWriter service service again, this service will catch up with the messages he missed and write these in the files.

Logging
You can watch the jms message in the jms log files: /servers / /logs/ jmsServers/ . By default the jms logging is off, so you may need to turn on the jms logging (console: JMS resource > Configuration > Logging tab.)

Retrial
You can define also how many times OSB will retry to deliver the messages and define a retrial time out. You may define also an error-channel queue to contain your undelivered messages (after fail retrial attempts.)

Please download the example here (osb 11.1.3 config export).

Using Oracle OSB is an easy way to learn SOA (click here there and voila... your SOA solution is ready.) In the future I will discuss how to implement this using open source ActiveMQ & Spring framework.

Note about terminologies:
I tried to use consistent terminologies (e.g. producer, consumer) while discussing point-to-point queue and publish-subscribe topic in my blogs. In practice, while discussing publish-subscribe topic people use publisher instead of producer and
subscriber instead of consumer.

---

See also blogs compilation about messaging for integration: http://soa-java.blogspot.nl/2012/01/asynchronous-messaging-for-integration.html


Please share comment.

Source: Steve's blog http://soa-java.blogspot.com

References
http://eaipatterns.com/
Hohpe's Enterprise Integration Patterns... one of the most popular SOA books in the market


The Definitive Guide to SOA: Oracle Service Bus

Reliable SOAP over http in Oracle OSB: Point-to-Point Channel

This blog discuss how to implement Point-to-Point (one producer, one consumer) channel in Oracle OSB. This channel is reliable, i.e. it implements:
• durable subscriber: to avoid missing messages when the consumer is not listening (e.g. disconnect for maintenance)
• guarantee delivery: to make sure that all messages are delivered even when the consumer fail
• idempotent receiver: dealing with duplicate messages (receive no duplicate messages)
We use SOAP with WSDL since it's a good practice to define your interfaces with WSDL. We use http since in our existing system we use a lot of RPI-style web services over http.



How:
1. build a jms producer business service (BS): SOAP based on the jmssimple.wsdl (downloadable below)
transport tab: jms protocol and the queue address (please read my other blog about how to setup jms resources in Weblogic)
jms transport tab: queue type, message type: text... handy for test since it's readable,no response
2. build a jms consumer proxy: SOAP also based on the jmssimple.wsdl,
transport tab: jms protocol and the queue address (same as the address in the BS),
jms transport tab: queue type and message type: text, durable susbcription, XA enable
message handling tab: enable transaction & same transaction response. The transaction will rollback (i.e. put the message back to the queue) in case if a fail consumer, thus this enables guarantee delivery.
3. wrap the producer BS with a proxy: SOAP with a wsdl, http transport.
Add a routing options in the request routing in the proxy message flow, enable the Quality of Service in this option.
4. make a consumer BS point to other webservice (e.g. a FileWriter in my example.)



Test:
1. Send a http message (e.g. using SOAPUI) to the producer proxy, the FileWriter service will write a file containing the message from the consumer BS.
2. Disable the FileWriter service
3. Send a http message to the producer proxy again.
4. After awhile turn on the FileWriter service service again, this service will catch up with the messages he missed and write these in the files.

Logging
You can watch the jms message in the jms log files: /servers / /logs/ jmsServers/ . By default the jms logging is off, so you may need to turn on the jms logging (console: JMS resource > Configuration > Logging tab.)

Retrial
You can define also how many times OSB will retry to deliver the messages and define a retrial time out. You may define also an error-channel queue to contain your undelivered messages (after fail retrial attempts.)

Please download the example here(osb 11.1.3 config export).

Using Oracle OSB is an easy way to learn SOA (click here there and voila... your SOA solution is ready.) In the future I will discuss how to implement this using open source ActiveMQ & Spring framework.

---

See also blogs compilation about messaging for integration: http://soa-java.blogspot.nl/2012/01/asynchronous-messaging-for-integration.html


Please share comment.

Source: Steve's blog http://soa-java.blogspot.com

References
http://eaipatterns.com/
Hohpe's Enterprise Integration Patterns... one of the most popular SOA books in the market


The Definitive Guide to SOA: Oracle Service Bus