OSB : DB Adapter-Poll - Delete Physical Records using Oracle Service Bus 12c

In 11g OSB, DB adapter can be used by creating it from JDeveloper , then pointing the folder in Eclipse. In 12c, the step is simplified . Similar to SOA BPEL, DB adapter can be created and used using JDeveloper at design time in OSB.

A variety of DB operations can be performed using DB Adapter.

1. DML operations like Insert,Merge(Insert/Update),Update,Delete
2. Select operation - Select, Select using primarykey , select using query by example
3. Using Procedure/Function
4. Poll operation for real time data synchronization from tables
5. Execute Pure SQL 

In this section we are about to see all the Poll related operations to pick the new or changed records from source table and how to MERGE(INSERT OR UPDATE) data using merge option with it to another table.

POLL TABLE FOR NEW OR CHANGED RECORDS IN A TABLE:

VERSION USED: 

SOA SUITE 12C - 12.2.1.3

DB  12C - 12.2.0.1.0

SOURCE SCHEMA - SCOTT 

SOURCE TABLE - EMP_UI

TARGET SCHEMA - TGT 

TARGET TABLE - EMP_UI_TGT

STEPS:

CREATING DATASOURCE AND CONNECTION FACTORY IN WEBLOGIC CONSOLE:

 In Oracle Middleware console, we have to create the datasource connection for the databases and assign a JNDI name to it (jdbc/<name>).

Then  create a connection factory (eis/DB/<name>) and assign the JNDI name to the connection factory.

The JNDI name provided during the design time adapter creation  should be same as the one being created in the console.

This would be used by the process/DB adapter during runtime as the code is deployed to the console.

Mapping :

Design time :  DBAdapter -> JNDI Name ( eis/DB/<name>) Eg. eis/DB/ScottLocal

Run time :Deployments->DBAdapter->Configuration->OutboundConnectionPools->javax.resource.cci.ConnectionFactory -> JNDI Name ( eis/DB/<name>) Eg. eis/DB/ScottLocal

The advantage of using an JNDI is as follows:

• Utilizing database connection pooling services provided by the container/weblogic
• Externalizing database connection and make it independent from the web application/weblogic itself
• Sharing database connections across applications deployed in the container/weblogic

Note:

Use the default Java Naming and Directory Interface (JNDI) name or specify a custom name. This connection enables you to configure the adapter during design-time and to connect to the database server during runtime. If a connection cannot be made, then you cannot proceed to the next dialog in this wizard, even if you are editing an existing partner link.This name must typically match with your connector-factory location setting in the weblogic-ra.xml file.If the names do not match, then Oracle weblogic attempts to create a runtime connection in a non-managed mode by using additional parameter settings as specified in the partner link Oracle BPEL Process Manager artifacts/OSB resources. These settings are created when you specify this design time connection. 

Ensure that you restart Oracle weblogic Server after changing the weblogic-ra.xml file

Steps:

Login to weblogic console (http(s)://<host>:<port>/console)

Home page or landing page in console

Create the datasource from Services->Data Sources

In Configuration tab -> New

Select "Generic Data Source"

Provide the Datasource name and JNDI name (jdbc/<name/datasourcename for easy identification>) 

Since we are going to use XA connection, select "Oracle Driver Thin(XA) for Service Connections ;Versions Any)

Difference between XA and non-XA : 

Reference: http://oraclesoawithweblogic.blogspot.com/2013/12/difference-between-xa-and-nonxa.html

Click Next

Provide the connection details
Database Name: <SID / ServiceName>
Hostname : <IP>/<Hostname> where db is hosted
Port : <Port where db is hosted>
Database username : <DB Schema/user name>
Password/Confirm Password : <DB password for above Schema/User>
Click Next

Click "Test Configuration"

Check connection test is succeeded.

Select the Targets (AdminServer/Managed Server(s)/Cluster)

Click Finish

Under Domain ->Deployments->Configuration->DBAdapter

Click Configuration->OutboundConnectionPools->New

Select "javax.resource.cci.ConnectionFactory"

Provide the JNDI name (eis/DB/<name>
)

Click and expand the javax.resource.cci.ConnectionFactory

Click on the connection factory

In XADatasourceName ,Property Value click and type the jndi name to be linked to the connection factory and press Enter key and click Save.

Goto Deployments under Domain -> Select DBAdapter->Click Update

Select Update option and click Next or Finish

Click Finish

Note:

The newly created Connection factory and Datasource will be attached to runtime .If not reflected properly , do complete restart of the server . 

Create the datasource and connection factory for both source and target. 

CREATING SERVICE BUS APPLICATION:

In JDeveloper create new OSB Application , click on New -> Application ->Service Bus Application . Type OSB Application Name in the pop up window and click on Finish . 

In JDeveloper create new OSB Project, File ->New->Project. Select "Service Bus Project" and Type Project Name . Our example it is "OSBDBPoll" . See the files created in Application Navigator.

Adding DB Adapter . Right click on Proxy Services Lane -> Insert Adapters-> Database. 

Provide DB Adapter name

In case the connection the DB is not created, create is by using  "GREEN +"  icon in the connection

Provide the DB details and test the connection , whether the JDeveloper is able to connect with appropriate DB.

Provide JNDI name and click Next

Select "Poll for New or Changed Records in a Table"

Import the tables required to be polled using the "Import Tables" button by clicking it.

Click on "Query" to query the list of tables in the mentioned Schema under "Schema" drop down.Now select the tables required and click on ">" button.

The selected tables will be moved to right pane 

In case if multiple tables are selected and if there is a parent child relationship to be set, clikc on "Create" button and choose the appropriate option for creating parent child relationships like which is the parent table, child table, what is the key to be linked, what is the relationship like 1 to 1 or 1:N or N:1 etc.

Select the columns required to be polled from the table.

Select "Delete the Row(s) that were Read" option.

Choose this operation to employ the physical delete polling strategy. This operation polls the database table for records and deletes them after processing. Use this strategy to capture events related to INSERT operations and cannot capture database events related to DELETE or UPDATE operations on the parent table. This strategy cannot be used to poll child table events. This strategy allows multiple adapter instances to go against the same source table. There is zero data replication.

Preconditions: You must have deletion privileges on the parent and associated child tables to use the delete polling strategy.

Please refer Oracle Documentation for adapters for further information. 

Select the polling options 

• Polling Frequency - The Polling Frequency section of the Database Adapter Polling Options Page enables you to specify how frequently to poll for new records or events
• Database Rows per XML Document -Use the Database Rows per XML Document to specify the number of rows per XML document when sending events to Oracle BPEL Process Manager or Oracle Mediator . This is the batch setting between the database adapter and its consumer: Oracle BPEL Process Manager or Oracle Mediator. For maximum throughput, it is ideal to pass a collection of rows as a single event. This reduces the per instance overhead and allows batching on the other end; that is, the database adapter merge or insert command can leverage batch database writing if it receives multiple rows as part of one invoke. Leave this set to 1 if your process performs unique processing on each row (if conceptually one row translates to one event).
• Database Rows per Transaction - Select Unlimited or enter a value in the Database Rows per Transaction section to indicate the number of table rows to process during a single transaction. For example, assume there are 10,000 rows at the start of a polling interval and this field is set to 100. In standalone mode, a cursor iteratively reads and processes 100 rows at a time until all 10,000 rows are processed, dividing the work into 10,000/100=100 sequential transactional units. This is the batch setting between the database and the database adapter. It cannot be set lower than database rows per XML document, which is the batch setting between the database adapter and its consumer: Oracle BPEL Process Manager or Oracle Mediator Component.
• Order By -Use the Order By section to order the returned rows by the selected column when polling the database for events. The best practice is to choose <No Ordering>, as message ordering regardless is not guaranteed without extra configuration
• Distributed Polling -Allows you to wait for increasing periods of time between retries. 9 attempts with a starting interval of 1 and a back off of 2 will lead to retries after 1, 2, 4, 8, 16, 32, 64, 128, and 256 (28) seconds.
• Delay Commit -Select this checkbox to delay the deletion of all rows until after all have been read.
• Use Batch Destroy - Select this checkbox to delete all rows at once after they have been read.
• Enable Streaming -Select this checkbox to enable streaming.
• Schema Validation - Select this checkbox to validate the schema
• Poll for Child Updates-  Specify the time interval between each retry. 

Note:Same Adapter options are used and they work for OSB as well apart from BPEL and Mediator. 

In case where condition and where parameters are to be used, click add and provide the parameter and click on edit and add the parameter and assign the where condition.

Note: Use the JDBC Query Timeout section of the Database Adapter Advanced Options Page to Specify the maximum number of seconds that the JDBC driver should wait for the database operation to execute. This section is displayed only if you selected the Call a Stored Procedure or Function option as the operation type on the Adapter Configuration Wizard - Operation Type page.Specify the maximum number of seconds that the JDBC driver should wait for the database operation to execute. If the specified value is zero, then the driver will wait indefinitely

Provide the Endpoint properties.

• Attempts - Specify the number of times to acquire a new SQL connection and retry the invoke. 
• Interval(s) - Specify the time interval between each retry. 
• Backoff Factor: x-  Allows you to wait for increasing periods of time between retries. 9 attempts with a starting interval of 1 and a back off of 2 will lead to retries after 1, 2, 4, 8, 16, 32, 64, 128, and 256 (28) seconds. 
• Max Interval (s)  -Specify the Maximum Interval for retries 

Click Finish.

We can see the adapter associated files created along with Proxy Service .

Refactoring or Moving an OSB resource from one path to another :

OSB provides an opportunity to move the resources from one path to another using the "Refactor" option.

Create the target folder where the resource should be moved .

Eg: In this example, we are moving an proxy from project folder to newly created "Proxy" folder.

So, first create the folder named "Proxy"

Right click on the project -> New -> From Gallery

Select "Folder"

Provider Folder name

Right click on the resource to be moved -> Refactor-> Move 

 Browse and Select the folder under which the resource should be moved and click OK.

Creating pipeline from proxy :

Click on the arrow icon from proxy , drag and drop it under Pipeline/Split Join section.

Provide Pipeline name 

Leave the default wsdl option and binding and click "Finish"

Pipeline will be created and wired to proxy

Use Refactor->Move and move the .pipeline file to Pipeline folder

Creating the Target DB with Business Service using DB Adapter:

In Jdeveloper composite, under "ExternalServices" lane, Right click ->InsertAdapters->Database

Provide DBAdapter name

If the DB connection is not there in JDeveloper already, create using the Green+ icon and provide the connection details. Test the connection . Click Ok

Provide the "JNDI name" and click Next

Select "Perform and Operation on a Table" option and select "Insert or Update(Merge)" option

Click on "Import Tables" button in the appearing popup
Click on "Query" => select the table needed =>use "->" to move the table to right side=> click OK

Click Next

Select the Primary Key from the column list . This popup will be displayed only when the imported table from the database doesn't have a primary key.

If the table has already a primary key , the popup will not appear.
Note: Primary key is mandatory

In case if parent-child relationships to be created, create then using Create button.

Select the columns needed to be inserted/updated

Choose the options and provide values appropriately


JDBC Query Timeout 

Use the JDBC Query Timeout section of the Database Adapter Advanced Options Page to Specify the maximum number of seconds that the JDBC driver should wait for the database operation to execute. This section is displayed only if you selected the Call a Stored Procedure or Function option as the operation type on the Adapter Configuration Wizard - Operation Type page.Specify the maximum number of seconds that the JDBC driver should wait for the database operation to execute. If the specified value is zero, then the driver will wait indefinitely.

JDBC Query Timeout :Specify the maximum number of seconds that the JDBC driver should wait for the database operation to execute.

JDBC Options 

Use the JDBC Options section of the Database Adapter Advanced Options Page enables you to specify JDBC options. Set low-level JDBC options on calls to the database. The operation you selected determines which options may appear here.

Query Timeout  :

Specify the timeout value for the query. This is applicable to most queries. But for stored procedures it may appear a little differently than it does in the regular case.

Max Rows : Specify the value for the maximum rows to be queried.

Interaction Options: 

Use the Interaction Options section to specify interaction options. The Interaction Options Page is displayed only if you selected the Perform an Operation on a Table option or the Execute Pure SQL option as the operation type on the Adapter Configuration Wizard - Operation Type page.

Get Active UnitOfWork:  Is an advanced setting that forces all invokes in the same global transaction to use the same SQL connection if going to the same database. This makes it easier to guarantee that later invokes can see the changes of earlier invokes, but you may not need to set this at all (if using emulated two phase commit, it should automatically be the same connection). Another difference is that for MERGE and INSERT, all changes are not written until the global transaction commits, so this setting also changes the timing of when WRITE operations occur.

Detect Omissions:  Allows the MERGE and INSERT to ignore empty or missing XML elements in the input payload. For a MERGE this will prevent valid but unspecified values from being overwritten with NULL. For INSERT operations, they will be omitted from the INSERT statement, allowing default values to take effect.

Optimize Merge:  Must always be set to true, as it is a general enhancement to MERGE performance (using an in query for the primary key existence check).

Provide the properties (Please refer the Poll DBAdapter creation for the properties and their usage)

Click Finish

In the composite, the reference DB for merge operation is created along with Business service.

Wire the External reference from Pipeline

Save it .

Note: While creating the process /resources, try to save it as soon as possible.

When an external service is linked or wired from pipeline, by default the wired service will be assigned to the routing.

Change the routing option from "Use Inbound operation for Outbound" to the "Merge" operation

CREATING AN XQUERY FOR TRANSFORMING DATA FROM SOURCE TABLE TO TARGET TABLE:

In JDeveloper, under Resource folder, Right click->New->XQuery File ver 1.0

Provider Xquery file name

Select the source schema and target schema
Click on the Green+ icon under "Sources" [refer 1 from snapshot below]
Provide the parameter name [refer 2 from snapshot below]
  This name will appear in the mapping page for the input parameter
Select the Sequence type [refer 3 from snapshot below]
   Type (Simple Datatype like String,Int,Datetime etc or Schema complex type like elements ) from which the source content should be picked

Click on Browse button under Schema Object Reference

Select the Schema and element

Similar way select the type/element of target

Mapping design view will open and right click on the target element and click Insert

Now map the collection to collection parameter for "forloop"

Then map individual elements

Create a log activity for data tracking

Drag and Drop Replace activity
Parameters:
Location : body and "." using expression builder
Replace option : Replace node contents
Value : Click on the expression builder icon drop down

Select XqueryResources

Select the Xquery created

In the XqueryVariables, provide the binding value (ie. from which context variable the input payload is passed to the xquery for mapping)
==========================================================
[Context Variable in OSB:

Oracle Service Bus message context is a set of properties that hold message content as well as information about messages as they are routed through Oracle Service Bus. These properties are referred to as context variables—for example, service endpoints are represented by predefined context variables. Oracle Service Bus also supports user-defined context variables.

The message context is defined by an XML schema. You typically use XQuery expressions to manipulate the context variables in the message flow that defines a proxy service.

Predefined Context Variables:

header :

·        For SOAP messages, header contains the SOAP header. (If the proxy service is SOAP 1.2, header contains a SOAP 1.2 Header element.)

·        For message types other than SOAP, header contains an empty SOAP header element.

body:

For the following cases:

·        SOAP messages—contains the <SOAP:Body> part extracted from the SOAP envelope. (If the proxy service is SOAP 1.2, the body variable contains a SOAP 1.2 Body element.)

·        Non-SOAP, non-binary messages—contains the entire message content wrapped in a <SOAP:Body> element.

·        Binary messages—contains a <SOAP:Body> wrapped reference to an in-memory copy of the binary message.

·        Java objects—contains a <SOAP:Body> wrapped reference to an in-memory copy of the Java object.

attachments :

Contains the MIME attachments for a given message

inbound:

Contains

·        Information about the proxy service that received a message

·        The inbound transport headers

outbound:

Contains

·        Information about the target service to which a message is to be sent

·        The outbound transport headers

operation:

Identifies the operation that is being invoked on a proxy service

fault:

Contains information about errors that have occurred during the processing of a message

messageId:

The transport provider-specific message identifier. This ID should uniquely identify the message among other messages going through the Service Bus runtime, but it is not required that this value be unique]

Reference from Oracle Documentation https://docs.oracle.com/middleware/1221/osb/develop/GUID-71E8CE2C-660C-4D26-A308-FB46F0AAE820.htm#OSBDV307

==========================================================================

Make sure the element passed from context variable is same as the input parameter element in the Xquery source.

Add one more log for getting the value after mapping .

Save and deploy the OSB project. Deployment steps are provided below in Deployment section.

In source database table data is inserted, which is polled by Proxy. After polling, the data will be physically deleted from the table.

In pipeline the polled data is logged , before and after mapping

Mapped data is available at the target.

DEPLOYING AN OSB PROJECT FROM JDEVELOPER

Right click on the OSB Project -> Deploy -> ProjectName

In DeploymentAction -> Deploy to Service Bus Server -> Next

Select the created Application Server to deploy the code . Choose "Overwrite modules of the same name" .Click Next.

Click Finish

If the application server is not created already click on the Green+ icon and provide the application server details

Provide Name

Provide Application server username and password ( user who have deployment access)

Provide hostname, port , domain name of the application server

Click Test connection and when all the tests are successful, click Finish.

Deployment Screen/Log Window:
Check the code has been successfully deployed to the server.

If the log window is not visible, select "Window" tab in JDeveloper and select log