DATA Loss in JDBC to Proxy Scenario

[suman_saha]

Hi Experts,

We are facing data loss issue in JDBC to ECC Proxy scenario using SAP PI 7.4 single stack.

Test Scenario:

There were 15000 data in MS SQL database. We are using the following queries to fetch data and post into ECC

select top 2000 * from Table_ECC where Status=1

update top(2000) Table_ECC set Status=7 where Status=1

We have got 8 packets of data in ECC sxmb_moni but we can see only 7000 distinct data present in the table which is updated by that proxy.

Further investigating the issue, we found there are duplicate data in PI. This is impossible if the same data is updated with the flag after successful processing.

My question is whether PI ensures the same data is selected and updated when we use top 5000 limitation.

Regards,

Suman

[Former Member · Accepted solution]

Hi Suman

Maintain the transaction isolation level as serializable in communication channel.

Regards

Nagur

[suman_saha · Accepted solution]

Thanks Nagur.

Actually the job to update the table data will run for sometime.Meanwhile if PI pick the data with transaction isolation level set to serializable, will that hamper that job?

Suman

[Former Member · Accepted solution]

Hi Suman

The transaction isolation level serializable will not hameer any data.if u have check (no transaction handling) then uncheck the same

Regards

Nagur

[suman_saha · Accepted solution]

So in additional parameters should I change only transaction isolation level to serializable..nothing else??

[former_member184948]

I faced the same issue while dealing with one client.

I used many trails and error method.I knew the problem is somewhere in Update method, although it looks simple.

Mine was to access the attendance from one DB and mark the flag X so that next these records do not get picked.The below query finally worked.

Select : select TOP 500  F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.ConcID, F6Accounts.dbo.TOLRecord.eDate, F6Accounts.dbo.TOLRecord.eTime,  F6Accounts.dbo.TOLRecord.Extra6,F6Accounts.dbo.TOLRecord.Status from F6Accounts.dbo.TOLRecord WHERE F6Accounts.dbo.TOLRecord.eDate > '2014-07-14  00:00:00:000' AND F6Accounts.dbo.TOLRecord.Extra6 is NULL  ORDER BY F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.eTime

Update:

UPDATE F6Accounts.dbo.TOLRecord SET F6Accounts.dbo.TOLRecord.Extra6 = 'X' WHERE F6Accounts.dbo.TOLRecord.eDate >'2014-07-14 00:00:00:000' AND  F6Accounts.dbo.TOLRecord.EmpCode in ( select TOP 500  F6Accounts.dbo.TOLRecord.EmpCode from F6Accounts.dbo.TOLRecord WHERE F6Accounts.dbo.TOLRecord.eDate  >'2014-07-14 00:00:00:000' AND F6Accounts.dbo.TOLRecord.Extra6 is NULL ORDER BY F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.eTime) AND  F6Accounts.dbo.TOLRecord.eTime in (select TOP 500  F6Accounts.dbo.TOLRecord.eTime from F6Accounts.dbo.TOLRecord WHERE F6Accounts.dbo.TOLRecord.eDate > '2014 -07-14 00:00:00:000' AND F6Accounts.dbo.TOLRecord.Extra6 is NULL ORDER BY F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.eTime)