Database Adapter
Database adapter is one of the technology adapter, we use this adapter to interact with database.It is used to communicate with oracle database or 3rd party database by leveraging underlying jdbc connector/drivers.
Has the input/ output messages that are easily converted into xml.
Support synchronous calls from BPEL/mediator.
Operations in DB Adapter:
->call a Stored Procedure or Function.
->Perform Operation in table
-> Insert or Update
-> Insert Only
->Update Only
->Delete
->Select
->Select by Primary Key
-> Query by Example
->Poll for new or changed records in a table
->Execute Pure SQL
There are number of operations available that you can perform with DB adapter namely :
Call a Stored Procedure or Function
Select this option if you want the service to execute a stored procedure or function.
Perform an Operation on a Table
Select this option for outbound operations.
You can select Insert or Update, Insert Only, Update Only, Delete, Select, or any combination of the six.
Poll for New or Changed Records in a Table
Select this option for an inbound operation (that is, an operation that is associated with a Receive activity).
This operation type polls a specified table and returns for processing any new rows that are added.
You can also specify the polling frequency.
Execute Pure SQL
Useful when dealing with arbitrarily complex statements, aggregate queries (result is not row-based),
and XMLType columns.
Different types of files that are being created while configuring DB adapter
At run time, the location is used to look up the adapter instance which executes the service.
Based on the properties in the db.jca file and the linked or-mappings.xml file,
<seviceName>.properties file generates the correct SQL to execute, parses the input XML, and builds an output XML file matching the XSD file.
To execute the SQL, it obtains a pooled SQL connection from the underlying data source.
Following file will be created while invoking DB Adapter in SOA:
db.jca
mapping.xml
properties.xml
.xsd
.wsdl
<serviceName>.wsdl
This is an abstract WSDL, which defines the service end point in terms of the name of the operations and the input and output XML elements
<serviceName>_table.xsd
This contains the XML file schema for these input and output XML elements. Both these files form the interface to the rest of the SOA project.
<serviceName>_or-mappings.xml
This is an internal file.
It is a TopLink specific file, which is used to describe the mapping between a relational schema and the XML schema.
It is used at run time.
<serviceName>_db.jca
This contains the internal implementation details of the abstract WSDL.
It has two main sections, location and operations.
Location is the JNDI name of an adapter instance, that is, eis/DB/SOADemo.
Operations describe the action to take against that end point, such as INSERT, UPDATE, SELECT, and POLL.
contents of the db.jca file are wholly determined by choices made while running the Adapter Configuration Wizard.
<serviceName>.properties
This is also an internal file. It is created when tables are imported, and information about them is saved. It is used only at design time.
Database adapter falls under Transactional or non-transactional adapters
Database adapter is a transnational adapter.what is logical delete ?
-> This will read the data from the table with status value is ready.
-> logical delete thus avoids DB adapter to pick it up the same record again and again.
-> When we poll for new or changed records using database adapter, we have multiple options after reading the data. Either we can delete the row from table (physical delete) which is not recommended or we can update the column in source table with new value (logical delete).
-> E.g. Source table has one column with the name flag, we read the data from source table when flag column has value “N” and after we read the data then we update the flag value to “Y”.
LogicalDeletePollingStrategy
This is a non-intrusive polling mechanism where we update a status column to mark records as read rather than deleting them.
This uses 2 SQLs -- one for polling/reading the records and another after-read SQL to update/mark the records as read.
For Logical delete the ui configuration can be used to fill the property values
<property name="MarkReadColumn" value="MARKCOLUMN"/>
<property name="MarkReadValue" value="READ"/>
<property name="MarkReservedValue" value="R${weblogic.Name-2}-${IP-2}"/>
<property name="MarkUnreadValue" value="UNREAD"/>
MarkReadColumn
The column to be used for updating the below given three properties
MarkReservedValue
This is the value which is used to pick the records on a specified node.
The value can be specified with special variables .
Let us look at the above property value and study it.
In the above sample weblogic.
Name is the name of the server node and IP is the ip address fo the node. -2 specifies the last two characters
R${weblogic.Name-2}-${IP-2}
MarkUnreadValue
This property specifies the value of the record which are to be picked . For simplicty you can consider this as where condition
MarkReadValue
This property is useful only in the case of logical delete.
This property updates the value of MarkReadcolumn with the specified property after sucessful processing for the record
what is the difference between, select and select by primary key?
select of primary key provide unique record .
select : whole record
Q)What is Data Source?
We specify database details to connect to database, if we hard code these details in code then we need to change code to reflect new details when we move to next environment so to solve this problem, data source concept came where we specify database details so that we need not to make changes in code. We just update the data source configuration in different environment.
When we use database adapter to connect to database, we need to specify outbound connection pool. That outbound connection pool further refer to data source.
What Is A Xa Data Source? How It Differs From A Non-xa Data Source?
XA driver- 4 XA driver
NoN-XA driver - 4 NON-XA driver
Xa Data Source
-> An XA transaction involves a coordinating transaction manager, with one or more databases (or other resources, like JMS) all involved in a single global transaction.
-> It uses two-phase commit to ensure that all resources either all commit or rollback any particular transaction. When you have scenario like you need to connect to two different databases, JMS Queue and application server, in this case you will use XA transaction that means all resource participate in one transaction only.
-> Global Transaction
-> multiple transaction takes place.(multiple resources)(distributed)
-> More than one resource involves
-> Ex: transfer money and paying tax to other DB.
Non-xa Data Source
-> Non-XA transactions have no transaction coordinator, and a single resource is doing all its transaction work itself (this is sometimes called local transactions).
-> you can rollback or commit transaction for only one resource.
-> Local transaction
-> single transaction takes place.(one to one)
-> Involve only one resource
-> Ex: Transaction btn two DB(source-target), like transfer money
What is Polling? How many ways we can do polling with DB Adapter?
A very useful feature of Oracle data base adapter is polling.
It tell us about any changes in particular table on which we want to poll.
Using this feature we can do lot of things according to our logic and our requirement.
Delete the Row(s) that were Read: physical delete polling strategy.
The commonly used polling strategies with Oracle DB adapter are:
1. DeletePollingStrategy : This is simplest where we read all possible rows from table and delete them afterwards to ensure that they are only read once.
2. LogicalDeletePollingStrategy : This is a non-intrusive polling mechanism where we update a status column to mark records as read rather than deleting them. This uses 2 SQLs -- one for polling/reading the records and another after-read SQL to update/mark the records as read.
3. LastReadId or SequencingPollingStrategy : This takes help of an external sequencing/helper table. This assumes that all new rows are inserted with an increasing key so that when selected only records with key greater than the highest key previously processed are fetched. This last highest key is stored in the helper table.
Poll operation, source table and the polling strategy (After Read) as ‘Delete the Row(s) that were Read’.
Polling Operation:
->Delete the row that were read.
->Update a field in the table(Logical Delete)
->Update a Sequencing table.
->Update an External Sequencing table on a different database.
->Update a Sequencing File.
Distributed Polling
-> Distributed Polling” will be used during the clustered environments.
-> This option has to be enabled when this DBAdapter is deployed in multiple soa managed server.
-> This will avoid the creation of multiple instances of BPEL.
-> Means it will avoid the possibility of picking the same record multiple times in a clustered environment.
-> Often in production environments,servers runs in clustered mode i.e more than one managed server running under one cluster. Generally if we don't implement distributed functionality then say for e.g we have 5 severs in clustered environment. Then in clustered environment in case of polling DB adapter it is quite possible that all 5 nodes try to poll the same record at same time. which will result in 5 concurrent instances with same data.Clearly,we do not want that at all.
When we select Distributed polling while configuring DB adapter. , it automatically uses the syntax SELECT FOR UPDATE SKIP LOCKED that means the same row cannot be processed multiple times.
-> when you have a clustered environment where multiple nodes are polling for the same data it is likely that the same record will be processed more than once.
To avoid this problem, the DB Adapter has a Distributed Polling technique that utilizes an Oracle Database feature:
SELECT FOR UPDATE SKIP LOCKED.
-> Distributed Polling means that when a record is read, it is locked by the reading instance.
-> If distributed polling is not set, then the adapter tries to process all unprocessed rows in a single polling interval.
Ex:
If we enable the distributed polling checkbox and set the MaxTransactionSize the behaviour changes.
Here the entire work is divided into 100 transaction units but each unit is processed in a single polling interval
i.e 1st polling interval 10 records are processed, rest 990 will be processed in subsequent intervals.
There maybe cases where you would like to control the number of DB records which are polled
at a time.
Polling frequency
Polling frequency is the interval at which the DB adapter activation agent polls the new records.
Database Rows per Transaction
Database Rows per Transaction (default value of 10) controls the number of records which are read at a time.
"For eg. if there are 1000 records to be read and we set the Database Rows per Transaction=10 , at the start of the polling
interval the entire work is divided into 1000/10=100 transaction units and completes sequentially till all are processed."
This property resolves to MaxTransactionSize in the jca file.
Q) How we can limit the number of rows fetched using database adapter ?
We can limit the number of rows fetched using ” Database Rows per Transaction” property. 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
Q) How we can limit the number of rows per XML message while sending the message from database adapter to consumer?
By using “Database Rows per XML Document” property
Singleton property in DB Adapter
Use of Singleton property in DB adapter.
"In clustered environments when we deploy a composite which is using a DB
adapter pooling. "
DB Adapter starts polling Staging table(EMP) in parallel with the interval. ...
For resolving this issue we need to set the singleton property in composite.
Many a times I have noticed that when you use DB Adapters for integrating with applications like the E Business Suite in a clustered high availability environment, at times there will be multiple instances for the same request.
There’s a property of Inbound endpoint life-cycle support within Adapters called Singleton.
To enable this feature for high availability environment for a given inbound adapter endpoint, one must add the singleton JCA service binding property in the composite.xml within the <binding.jca> element and set it to a value of true as shows.
<property name="singleton">true</property>
</binding.jca>
-> BPEL Persistence properties are used to control, when a process need to dehydrate.
Below are the properties which we can use to control it for BPEL Component in a Composite.
InMemoryOptimization
-> This property indicates to Oracle BPEL Server that this process is a transient process and dehydration of the instance is not required. When set to true
-> Oracle BPEL Server keeps the instances of this process in memory only during the course of execution.
-> This property can only be set to true for transient processes
(process type does not incur any intermediate dehydration points during execution).
store database for a asynchronous BPEL process.
CompletionPersistPolicy
-> This property controls if and when to persist instances.
-> If an instance is not saved, it does not appear in Oracle BPEL Console.
-> This property is applicable to transient BPEL processes
(process type does not incur any intermediate dehydration points during execution).
-> This property is only used when inMemoryOptimization is set to true.
-> This parameter strongly impacts the amount of data stored in the database
(in particular, the cube_instance, cube_scope, and work_item tables).
-> It can also impact throughput.
· on (default): The completed instance is saved normally.
· deferred: The completed instance is saved, but with a different thread and in
another transaction, If a server fails, some instances may not be saved.
· faulted: Only the faulted instances are saved.
· off: No instances of this process are saved.
<component name="mybpelproc">
...
<property name="bpel.config.completionPersistPolicy">faulted</property>
<property name="bpel.config.inMemoryOptimization">true</property>
...
</component>
OneWayDeliveryPolicy
-> This property controls database persistence of messages entering Oracle BPEL Server.
-> Its used when we need to have a sync-type call based on a one way operation.
-> This is mainly used when we need to make an adapter synchronous to the BPEL Process.
-> By default, incoming requests are saved in the following delivery service database
tables:dlv_message
· async.persist: Messages are persisted in the database.
· sync.cache: Messages are stored in memory.
· sync: Direct invocation occurs on the same thread.
<component name="UnitOfOrderConsumerBPELProcess">
...
<property name="bpel.config.transaction" >required</property>
<property name="bpel.config.oneWayDeliveryPolicy">sync</property>
...
</component>
General Recommendations:
1. If your Synchronous process exceed, say 1000 instances per hour, then its better to set inMemoryOptimization to true and completionPersistPolicy to faulted, So that we can get better throughput, only faulted instances gets dehydrated in the database, its goes easy on the purge (purging historical instance data from database)
2. Do not include any settings to persist your process such as (Dehydrate, mid process receive, wait or On message)
3. Have good logging on your BPEL Process, so that you can see log messages in the diagnostic log files for troubleshooting.
No comments:
Post a Comment