Sender JDBC Adapter Select/Update Issue

[former_member10771]

Dear All,

We have configured a Sender JDBC Adapter to Poll data from the DB2 tables. It is working fine and both the select and the update queries written are also getting properly executed and are changing the status of the flag from Y to N once read from database.

In the communication channel ->

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

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

But I have one doubt after executing the select query some new data comes into the table of status flag 'N"., then will this unselected data will also be updated to 'Y' .

The question is while we do a select and update from XI on the DB table and at the same time there is an insert happening into the table from the other end how will the adpater behave in this case.Will it result in missing of some records during next select/update transaction from XI..

Your inputs will be appreciated.

Regards

Amit

[Former Member]

Amit

Did you ever get a solution to your question ?

Sender JDBC Adapter Select/Update Issue

Posted: Apr 24, 2008 2:29 PM Reply

Dear All,

We have configured a Sender JDBC Adapter to Poll data from the DB2 tables. It is working fine and both the select and the update queries written are also getting properly executed and are changing the status of the flag from Y to N once read from database.

In the communication channel ->

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

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

But I have one doubt after executing the select query some new data comes into the table of status flag 'N"., then will this unselected data will also be updated to 'Y' .

The question is while we do a select and update from XI on the DB table and at the same time there is an insert happening into the table from the other end how will the adpater behave in this case.Will it result in missing of some records during next select/update transaction from XI..

Your inputs will be appreciated.

Regards

Amit

[Former Member]

Hi this is related to databse.

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

after select statement executed, later update will executes in the middle any changes are done then

that record is also updated means flag is set to Y, because there is no change in the flag field after that record is updated(initial is 'N').

if suppose that is changed(to A or some other letter) then we will write one more statement.

[Former Member]

Amit,

The Isolation Level settings should be changed according to your requirements,so have a look at this page if you want to get explainations and then it will help you choose the most suitable value : http://db.apache.org/derby/docs/10.0/manuals/develop/develop71.html

Or you can try to find DB2 JDBC documentation to get more precise info

Rgds

Chris

PS : according to SAP Help :

"You must add an indicator that specifies the processing status of each data record in the adapter (data record processed/data record not processed) to the database table.

· The UPDATE statement must alter exactly those data records that have been selected by the SELECT statement. You can ensure this is the case by using an identical WHERE clause. (See Processing Parameters, SQL Statement for Query, and SQL Statement for Update below).

· Processing can only be performed correctly when the isolation level for transaction is set to repeatable_read or serializable."

[Former Member]

Amit,

good thinking......

actualy as far as i know...when ever JDBC adapter is executing any query ..it will set a lock. i mean to say... it won't consider the new entries coming.after that time...

it will be taken care by next next query execution.

so it wont be a problem.. JDBC adapter will take care of this synchronisation.

i hope u understood...

Regards

biplab