JDBC sender Adapter consistency and Transaction level - URGENT

[dhagigeorgiou]

Hi,

We are using the JDBC adapter to read data from an Oracle view. The view joins two tables on their keys where flag = 0. The JDBC adapter carries out the select and then updates the selected rows with flag = 1. The transaction level used is the default, which according to XI help is TRANSACTION_SERIALIZABLE.

Our question is what happens if the oracle application that feeds the underlying tables inserts a new record in the time interval between the select statement and the update statement with flag = 0.

WILL THE UPDATE STATEMENT UPDATE EVERYTHING, EVEN THE NEWLY INSERTED RECORD??

As we are in a live environment and we have reason to believe that some records updated to FLAG = 1 are NOT sent to XI we would appreciate the expert's help on this issue!

[Former Member · Accepted solution]

My suggestion is to have the update statement on the actual keys on the table instead of the flag = 0.

[dhagigeorgiou]

It turns out that the default is not serializable. One needs to explicitly state this.

Dimitris

P.S Sorry for the URGENT in the subject! Wont happen again.

[Former Member]

Hi Dimitris,

If you use transaction level SERIALIZABLE, the update statement WILL NOT update the newly inserted records. When you use this transaction level, it takes a snapshot of the database at the current point in time.

We confirmed this with the SAP XI Dev team on our last project and have a number of interface in production using this method. It works great.

Thanks,

Jesse

[dhagigeorgiou]

Hello everyone,

Thank you all for your prompt responses.

Having tested many scenarios, we think that we have found the root of the problem. The Oracle database to which we connect has by default a transaction level for all transactions of read_committed. It seems that the value 'default' of the sender JDBC transaction level in configuration is actually the default of the database and not SERIALIZABLE as XI help documentation states. Of course we acknowledge the fact that this may be specific to the JDBC driver (v 10.1) we have chosen.

Any thoughts on that?

[Former Member]

Dimitris,

I was looking at the FAQ :JDBC Driver Snote and found this information which seems to answer ur question.

Q: If I have the following configured in a JDBC Sender:

Select Query:

SELECT column FROM TABLENAME WHERE FLAG = "TRUE"

Update Query:

UPDATE TABLENAME SET FLAG = "FALSE" WHERE FLAG = "TRUE"

How do I know that the JDBC adapter will not update newly added rows (rows that were added between the time that the SELECT and UPDATE queries were executed) that were not read in the initial SELECT query?

A: The SELECT and the UPDATE are run in the same DB transaction, i.e. both statements have the same view on the database.

Make sure that both statements use the same WHERE clause. An additional requirement for the correct operation of this scenario is the configuration of an appropriate transaction isolation level on the database (i.e., repeatable_read or serializable). You might also consider using a "SELECT FOR UPDATE" statement instead of a plain SELECT statement to ensure proper locking on the database.

[Former Member]

Hi Dimitris,

Please have a look at this blog. Guess this should solve your problem

/people/saravanakumar.kuppusamy2/blog/2005/01/19/rdbms-system-integration-using-xi-30-jdbc-senderreceiver-adapter

cheers ,

Prashanth

[Former Member]

Hi,

>> The transaction level used is the default, which >>according to XI help is TRANSACTION_SERIALIZABLE.

this is the right transaction level to use.

>>WILL THE UPDATE STATEMENT UPDATE EVERYTHING, EVEN THE >>WLY INSERTED RECORD??

no it will not modify newly inserted record. There is no way to do it. You can modify these records only during next run.

cheers,

Naveen