jdbc adapter and database

[Former Member]

hi all,

i am working with JDBC adapter.

We have configured a Sender JDBC Adapter to poll records from an Oracle table based on a flag field and then update the flag for the selected records. When tested in DEV and QA environments (where test data comes in intermittently and not in huge volumes), itu2019s working fine.

Both SELECT and UPDATE queries written in the Sender JDBC adapter are getting properly executed and are changing the status of the flag for the selected records from Y to N once read from the database.

select * from <table> where flag = 'N'.

update <table> set flag = 'Y' where flag = 'N'.

But in the PROD environment (with records getting updated in the database every second), after XI executes the SELECT query and just before the UPDATE query is executed, new records come into the Oracle table with status flag 'N". So when the UPDATE query runs just after the SELECT query, then these unselected records also get updated to 'Y'. Thus these records never get into the resultset and hence XI and thus remain unprocessed.

So when XI does a SELECT and UPDATE on the Oracle DB table and concurrently there is an INSERT happening into the table from the other end, the JDBC sender adapter is picking up a certain number of records but updating the status of more records than it picked up.

how do we come about the solution to this?

[Former Member]

Hi,

What is the current isolation level set for your sender adapter ? Normally, it is set to database default so you may have to ask your dba for its current value, as it is very important for your issue ... Look at this page for more details about these levels : http://www.precisejava.com/javaperf/j2ee/JDBC.htm#JDBC102

Setting this param to the suitable value should avoid your pb

Rgds

Chris