SAP PI JDBC issue - PI updates row which it did not pick

[former_member434498]

Hi Team,

We have an interface in Production which download records from a SQL database staging table and sends it to SAP.

Our polling interval is currently set to pick records every 2 minutes. Below is the configuration of our sender JDBC channel in PO system (version is 7.5).

Select Statement:

Select * from dbo.mytable where flag IS NULL

Update Statement:

Update dbo.mytable set flag-CURRENT_TIMESTAMP where flag IS NULL

Advanced Settings:

Transaction Isolation Level: Serializable

Database Auto-Commit-Enabled: Yes

Disconnect from Database After Processing Each Message: Yes

Problem is this:

There are instances when our PO system updates the flag of the record which it did not actually pick.

Example. PO system had updated the flag of rows # 1-10 but the XML file in PO workbench only contains rows # 1-9. Hence, it is missing the last row (row # 10) in the XML file.

Anyone had this similar issue? Any suggestions on how to avoid this?

Thanks,

Carlo

[binod8]

Hi Carlo,

As per my understanding we may be going wrong here.

1> You are reading the data from DB where flag is NULL. Suppose you were reading the data at 10AM 000 ms. SAP PO found 9 data where flag was NULL at 10 AM 000ms.

2> Now you are updating all the records having flag as NULL at 10 AM 001ms (after 1 ms). Since we are updating the flag in DB after sometime say just after 1 MS but still there is chance that during this time there was one more record for which flag was set as NULL. Since this data appeared in DB after our select query, it was not in our select data list. But when we run the update command, since this new record is also having flag as NULL it is getting updated with the timestamp.

So in the select query we have the data that was in DB with flag NULL at 10AM 000ms (may be 9 records). Then when the update was run say after 1ms then 10 record is getting changed for NULL since this new record might have come in DB after we ran the select query.

If this is true, then you should run the update only for the record which you have picked and the flag is NULL but not for all where flag is NULL.

Thanks& Regards,

Binod