If you are reading this blog topic, then like so many, you are trying to use the Oracle SOA 11g Database Adapter to fit your database polling needs and are in need of a little assistance.
A common occurrence that I keep coming back to is that life using the SOA database adapter usually becomes easier when the adapter is simply viewed and treated as a gateway between the Oracle SOA composite world and the database world. When viewing the adapter in this light one should come to understand that the adapter is not the ultimate all-in-one solution for database access and database logic needs.
In this post I will discuss using the Oracle SOA Database Adapter and a handy pattern that nicely handles some seemingly simple polling requirements.
NOTE:
I will not be discussing the various issues or situations encountered with the many different polling configurations that can be setup. To read up on a number of configuration settings and polling behaviors the following blog link is useful:
http://javaoraclesoa.blogspot.com/2012/04/polling-with-dbadapter-in-clustered.html
The Use Case
Process a number of orders by picking up order information from the database, transforming the order information, and importing the newly transformed order information into an external system. The solution would involve order information moving from a database table, then into a BPEL process, and finally being imported into an external system via a web service interface.
This blog topic will only cover the database and database polling portion of the use case.
The Requirement – Only Process 1 Record at any Given Time
Only 1 order/record can be processed from start to finish at any given time. Order processing can be defined as the order/record being polled, transformed, imported into the external system, and an update to the order/record status being made in the database table.
The requirement is simple and straight forward; however in a clustered SOA environment enforcing the requirement is much more complex than one might think.
Problems Encountered:
- Clustered environment problems:
- Each managed server has an Oracle SOA DBAdapter application running and polling. Each of the database adapters on the different managed servers can and will get out of sync from one another. The out of sync behavior causes the Distributed Polling/Skip Locking polling configurations to add little value and the settings now seem to not work as expected. I faced the problem that the Distributed Polling/Skip Locking features were being counteracted because multiple database adapters in the cluster would get out of sync with their polling times. For example, one database adapter would poll exactly on the minute while the other in the cluster would poll on the 13th second of every minute. I would end up with the adapters polling 2 records in a 13 second interval, and the second record polled would enter BPEL processing before the first record could finish (13+ seconds for processing is slow I know, but the slow performance was being caused by the external service invoked by the BPEL process). Our requirement would then fail to be met because 2 records would be in process at the same time.
- Also within the cluster, multiple adapters polling will lead to each adapter polling different records at the same time, breaking the requirement for only processing 1 record at any given time.
- Distributed Polling/Skip Locking could not be relied upon to meet the requirement for a number of reasons, see here for more discussion of the Distributed Polling/Skip Locking issues http://javaoraclesoa.blogspot.com/2012/04/polling-with-dbadapter-in-clu…;
- Limiting the number of records to be polled/processed within a specific time interval cannot be easily achieved using the database adapter settings. Between multiple adapters polling, record locking, skip locking, and adapters polling on different frequencies, unexpected behavior can easily arise when trying to use the adapter to poll and process a limited number of records.
Solution
The solution was not to try and make the database adapter do more but to make it do less and to utilize the database technology itself in order to resolve with the problems being faced.
The solutions to the problems were:
- To create a database view over the database table.
- To poll against a database view instead of the database table.
- To build the database view so that:
- It contains only 1 record to be polled at any given time.
- It contains only the oldest record ready to be worked, which will be in a “READY” status.
- It will not contain any records if the database table contains any record in an “IN_PROCESSING” state within the past 30 minutes. The 30 minute time limit was chosen in the case that a stuck thread or some other system issue is encountered that prevents the status of the record from changing from “IN_PROCESSING” to some other state.
- All of the columns in the database table are available in the database view and that no virtual columns exist in the view. Doing so allows insert, update, and delete operations made on the database view to propagate to the database table. The database adapter must be able to commit an update to the view for a logical delete or else polling will not work
4. Use the Reserved Value configuration of “R${weblogic.Name-2}-${IP-2}” when configuring the database poller within the composite. Doing so configures the adapters in the cluster to update the record with a value that ties the record to one specific database adapter in the cluster, preventing any other database adapters from also polling that particular record.
The view creation SQL looks like the following:
CREATE OR REPLACE VIEW PHO.ORDER_IMPORT_STAGING_V AS
/* all of the columns listed in the select are all of the columns available on the staging table, no virtual columns are created*/
SELECT ot.staging_id, ot.order_id, ot.order_number, ot.order_name, ot.order_hash, ot.order_status, ot.order_timestamp FROM PHO.ORDER_IMPORT_STAGING ot
WHERE ot.order_status LIKE ‘%READY%’
AND /* and the number of orders in process within the last thirty minutes equals 0 */
(select count(*) from PHO.ORDER_IMPORT_STAGING k where k.order_status like ‘%IN_PROCESS%’ and (k.order_timestamp + interval ’30’ minute) > CURRENT_TIMESTAMP) = 0
AND /* and were the order record with the oldest staging id that is ready to be processed is chosen */
(select min(x.staging_id) from PHO.ORDER_IMPORT_STAGING x where x.order_status like ‘%READY%’) = ot.staging_id
/
The Pattern
With the 4 steps listed above and the sample SQL statement for building a view to meet the requirement, a very useful pattern is born. Also be sure to recognize that the pattern concepts work together even if we use a technology other than the Oracle SOA Database Adapter for our polling needs.
Abraham Maslow once said, “If you only have a hammer, you tend to see every problem as a nail.” The database adapter is a great tool to have and use. Just keep in mind that database adapter may be part of the solution and not the entire solution.
I hope this pattern helps my fellow consultants, software engineers, developers, and code monkeys with their database polling needs.
Product Versions
- Oracle SOA Suite 11.1.1.5.0
- Oracle RDBMS 11.1.1.2.0 non RAC