The methods looked at;
1 using an external webservice (without OSB)
2 using an external webservice proxied by the OSB
3 using the fn-bea:execute-sql function from the OSB
4 using the JCA DbAdapter from the OSB provided by Oracle as part of Oracle SOA Suite
Setup
Database call
In order to test the database call, I've created a function which returns systimestamp (the current time). This function is called with the previously described methods. A PL/SQL function was chosen as database call because the execute-sql function requires a select-statement to be performed. A function allows usage in SQL statements.
The PL/SQL code used was the following;
CREATE OR REPLACE
PACKAGE TEST_PACKAGE AS
FUNCTION GET_DATE RETURN TIMESTAMP;
END TEST_PACKAGE;
CREATE OR REPLACE
PACKAGE BODY TEST_PACKAGE AS
FUNCTION GET_DATE RETURN TIMESTAMP AS
BEGIN
RETURN SYSTIMESTAMP;
END;
END TEST_PACKAGE;
Oracle Service Bus configuration
For the OSB configuration, the following is a nice jumpstart; http://www.oracle.com/technetwork/articles/jumpstart-for-osb-development-page--097357.html
Because the OSB configuration is relatively straightforward, I will not explain it in detail here. In all methods, only one XQuery transformation was required. I could have chosen for XSLT transformations but the available examples for the usage of fn-bea:execute-sql where in XQuery so I decided to use that.
JDBC methods
External webservice
The Java code used was the following;
package getdatejava;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
@WebService
public class SimpleJDBCService {
@WebMethod
public String GetDate() throws Exception {
String query = "select TEST_PACKAGE.GET_DATE from dual";
Context context = new InitialContext();
DataSource dataSource =
(javax.sql.DataSource)context.lookup("jdbc/testuser");
Connection con = dataSource.getConnection();
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
Timestamp result = null;
while (rs.next()) {
result = rs.getTimestamp(1);
}
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
String res = dateFormat.format(result.getTime());
//con.close();
return res;
}
}
weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool testuserDS to allocate to applications, please increase the size of the pool and retry.
Because I'm manually programming out the Java JDBC code, the code is specific to a query/call. The Java code requires changes if the query changes. Also for every new query, a new service needs to be created.
fn-bea:execute-sql
In order to use the XPath function and generate the resultmessage, I used the following XQuery code (see for example; http://docs.oracle.com/cd/E23943_01/admin.1111/e15867/xquery.htm#i1104589);
(:: pragma bea:global-element-return element="ns0:processResponse" location="../xsd/GetDate.xsd" ::)
declare namespace ns0 = "http://xmlns.oracle.com/HelloWorld/GetDate/GetDate";
declare namespace xf = "http://tempuri.org/TestProject1/xquery/query_XQ/";
declare function xf:query_XQ()
as element(ns0:processResponse) {
<ns0:processResponse>
<ns0:result>
{fn-bea:dateTime-to-string-with-format("dd MMM yyyy hh:mm a G", (
fn-bea:execute-sql(
'jdbc/testuser',
xs:QName('ns0:result'),
'SELECT test_package.get_date from dual')/GET_DATE/text()))
}
</ns0:result>
</ns0:processResponse>
};
xf:query_XQ()
As can be seen, a datasource is used directly in the call to execute-sql. Since I'm using a select statement, there are several limitations. Every action has to be wrapped in an SQL statement. More complex logic can (as workaround) be wrapped by an SQL function which in an autonomous transaction performs actions. Such a construction is not a nice way of doing things like this. Hardcoding the datasource and the query in an XQuery transformation might not be desirable for maintainability. It is however relatively fast. In the methods tested, this is about just as fast as directly using a Webservice which does the JDBC.
DbAdapter
In order to call the DbAdapter from the OSB, JDeveloper was used to generate the required files; JCA file and schema's. In the OSB, an assign (for the call to the adapter) and a transformation for the response message was required.
The DbAdapter supports transactions and provides several options for handling them. Also it allows various Db actions to be performed such as inserts and procedure calls. PL/SQL objects are also supported. It is even possible to use a custom SQL statement which can be changed at runtime in the Enterprise Manager.
Performance measures
For completeness I've also added performance measures of a BPEL process using the DbAdapter. The performance test was executed in SOAP UI with the settings as specified below.
The below list summarizes the results. Fastest is on top, slowest at the bottom.
- JAX-WS web-service with JDBC (method 1)
- OSB with XQuery (method 3)
- OSB with DbAdapter (method 4)
- JAX-WS web-service with JDBC proxied by OSB (method 2)
- BPEL no audit logging
- BPEL audit logging production
Discussion
There are several options available for doing database calls from the Oracle Service Bus. These differ in performance, maintainability, transaction handling and error handling. Choose wisely when picking one for your specific use-case!
Performance and maintainability
Performance and maintainability seem to be adversely proportional. The best performing methods are difficult to maintain (less flexible). The methods which allow easy maintenance, do not have the best performance.
Also when more intermediaries are used, the performance drops. The slowest methods involving the OSB are when the DbAdapter is used (JCA is used to access the DbAdapter) and when a simple webservice is proxied (a SOAP call is executed). When the action is performed inside the OSB by means of XQuery or in a standalone webservice, it is faster.
Since the performance measures were done on a single simple database query, I cannot be sure that the time required to perform more complex actions increases by a factor or that the overhead of a method is a fixed duration. In case the performance is increased by a factor, it becomes more important to also take it into account, when choosing a method.
Manually coding JDBC calls in a web-service is very fast (fastest measured) but the code is difficult to maintain (although as flexible as the coder can code it) and requires some time to develop. Using XQuery from the OSB is about just as fast. The code is also difficult to maintain and the implementation provides some limitations. Only SQL statements can be used (hence the name execute-sql...). It requires less coding however then using custom web-services for every query. This method also allows a certain amount of re-use if implemented with this thought in mind. The DbAdapter is very flexible (supports most Oracle Db calls). In 11g however, the generation of files to configure the call to the DbAdapter, still has to be done in JDeveloper while OSB development is done in Eclipse. 12c will of course fix this. The JDeveloper / Eclipse project structure can be integrated (see: http://guidoschmutz.wordpress.com/2010/08/08/oracle-service-bus-11g-and-db-adapter-a-different-more-integrated-approach/).
Transactions
The DbAdapter provides (distributed) transaction support. A plain external web-service uses it's own transaction and cannot (without additional effort) participate in the OSB transaction. I'm not sure if the XQuery appoach uses the OSB transaction or creates its own. I suspect the latter. A BPEL implementation is slower then an OSB implementation, calling the DbAdapter.
Error handling
Also in the different implementations, the way error handling will have to be implemented differs. BPEL offers several out of the box functionalities which can be used to implement error handling such as the fault management framework and the error hospital. The OSB offers retry mechanisms and error handlers. This is more limited then what is possible in BPEL. A custom web-service needs custom error handling. SOAP errors however when thrown from a JAX-WS web-service can be handled in the OSB.
No comments:
Post a Comment