I am doing DB Adapter polling in OSB.I have a db adapter which polls order table whenever there is a record with status='N'.The OSB component is on single node of Weblogic Server.
1.The db adapter picks the order data with status='N' in the proxy service
2.It transforms to webservice xsd
3.Invokes a webservice.
4.The webservice returns acknowledgement number.
5.The order table should be updated with status 'Z' and attribute1=acknowledgement number
OSB Service Design
1.Proxy service calls business service to poll the order table
2.Converts the payload to the xsd format required by target
3.Invokes the webservice on target
4.Receives acknowledgement number
5.calls a plsql procedure which updates all the records within this batch with the acknowledgement number and status='Z'
My configurations in db adapter
Logical delete option in db adapter.
Unread Value :'N'
Read Value : 'A'
Polling frequency : 300 seconds
no of databse rows per XML document : 10
Database rows per transaction : 10
1.My process after getting the acknowledgement number it updates the status with Z and attribute1=acknowledgement number.At the of this transaction I am seeing that the status is being updated by
the adapter to 'A' based on read value.I want the read value only to be updated initially but as soon as my OSB process picks it for further processing it should not update again.How can i prevent
the status from getting updated to 'A'.
2.I have 40 records in order table.Per the database rows per transaction it should process 10 records at a time and for each batch of 10 i should get one acknowledgement number.Instead i see that
all the 40 records are updated with the same acknowledgement number.Kindly help as to how I can resolve this situation.
The thing is, when you use Xa connection, the commit is global, means, you are polling data(connection 1, same datasource), invoke for DB action (connection 2, same datasource) and the commit happens after the invoke (if that's end your thread).
In your DB Adapter(Poll) check the "Get Active UnitOfWork". This is one way to ensure that the current transaction will be committed.
Thank you for your inputs.In my case I am using (connection1) for polling data with status 'N'.Invoking webservice and then changing the status to 'Z' using connection2 on the EBS Database.But in the db adapter since I gave read value as 'A' at the end of the transaction it commits the record to 'A' and overriding my status change to 'Z'.Kindly let me know how i can avoid this.
As I mentiod above: In your DB Adapter(that Poll the data) check the "Get Active UnitOfWork" option. This is one way to ensure that the current transaction will be committed (meaning value will change to 'A', commit and then continue to you procedure that update to 'Z').
Another option is to use for the polling connection Non-XA transaction.
Thanks for the update.I am able to find the option of getActive unit of work in db adapter with select but not when i am polling.Should i set this property in toplink myself outside jdev?Will that work.
....I don't think it will work. It should be checked for invoke only.
Two options: 1. Set the property "idempotent" to false - this should enforce the process to dehydrate and commit.
2. Use a Non-Xa connection. This will work for sure
Invalid JCA transport endpoint configuration, exception: javax.resource.ResourceException: BINDING.JCA-12532 Cannot set JCA WSDL Property. Error while setting JCA WSDL Property. Property setIdempotent is not defined for oracle.tip.adapter.db.DBActivationSpec Please verify the spelling of the property.
I followed your instructtion and set the property idempotent in the db adapter property tab.But this property was not visible in the db polling adapter wsdl that I have imported into OSB.Kindly help.
Thank you for the inputs.I worked around the problem by setting the read value to 'Z' and the reserved value to 'A'.So at the end of the transaction my status is 'Z'.I used XA data source.Another option of working around this problem could be is to use sequencing polling strategy.I agree that non-XA data source will work for sure.