JDBC sender adapter issue

[Former Member]

Hi SDNers,

I have a scenario which picks data from AS400 table using JDBC adapter.

My sample SQL query and update query are as follows

SQL query : Select * from <table> where <keyfield> = min(keyfield) and flag ="";

Update query: Update <table> set flag = "true" where <keyfield> = min(keyfield) and flag ="";

This is to ensure records are processed one by one.

All goes well. Suddenly I have a issue where out of "n" records in the table one record failed to reach the target system. My cause of concern is there is no error in RWB and SXMB_MONI.

I have identified the missing record and could see the flag has been set to "true" in the table. I dont have TREX installed in XI, so could not track the message in XI.

Has any one faced this issue? Any ideas are highly appreciated.

Regards,

Jai Shankar

[Former Member · Accepted solution]

I'm wondering if this has nothing to do with the JDBC part of your interface, and maybe something to do with your receiver or a mapping issue that wouldn't output an error.

[Former Member · Accepted solution]

Hi All,

Thanks. I guess we tracked the issue. It has to do with transaction isolation level. Though I had set it to repeatable read, a record flag was updated incorrectly due to the insert happened between JDBC adapters select and update.

I could find 2 msgs in XI for a single record and no msg for the record missing in target.

Now, I have changed the transaction isolation level to "Serilaziable" and hope the issue will not pop up again.

If, any one have any other thoughts on this pls share it here.

Regards,

Jai Shankar

[former_member189387 · Accepted solution]

Hi Jai ,

Just i am giving my thoughts . If u have changed the transaction isolation level to "Serilaziable" . It won't let u to insert record while it is inthe process of updating the respective rows.

Wont't it affect ur AS400 transactions ?

Regards.,

V.Rangarajan.

[Former Member]

Hi All,

It seems, still the issue is not solved. We have the same issue again inspite of setting the transaction isolation level to serializable.

SAP Note says

<i>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</i>

We have ensured

a. Same where clause.

b. Serializable transaction isolation level

Am not much sure how SELECT FOR UPDATE works. We are using AS400 db.

Any experience with SELECT FOR UPDATE.

Regards,

Jai Shankar

[Former Member]

Hi Jai,

Few of my colleagues faced same issue.

The problem traced out was:

in source system one of the columns was not set as primary key, so duplicate records were existing; and in the target system that column was set as primary key due to which it was not accepting duplicate records.

Check whether you have same type of issue.

Ravi.

[Former Member]

Hi Ravi,

Thanks for sharing the info.

In your case, you would have got a error msg in RWB. Rite?

But in my case, I dont have any errors..

P.S: The transaction isolation level is set to Repeatable read.

Regards,

Jai Shankar

Message was edited by:

Jai Shankar