JDBC Sender Channel: Picking only few records, where as all records are successfully getting updated by Update Query

[siddhardha_dnk]

Dear All,

We are using on PI 7.3.

Here i have an issue with JDBC Sender channel.

Update statement is working and is updating "Flag" and "TimeStamp" fields in table as mentioned in Update Query.

Whereas all the records which are updated are not getting picked by channel.

Lets say, 7 records are updated at same time with same "TimeStamp" and "Flag", as per Update Statement. But only 1 or 2 record(s) are being picked and is/are processing successfully.

And this happens only sometimes.

Please help me on this.

Warm Regards,

DNK Siddhardha.

[ambrish_mishra]

Hi,

Did you check if the table is not updated while PI adapter is polling. What I mean is when the select happened, the table had 2 records and when the update happened, the table had 7 records.

The select won't pick up records which have been updated based on flag.

This is the most common problem with select and update....

Cheers,

Ambrish

[siddhardha_dnk]

Hi Ambrish,

Thanks for your reply.

But in our case, it is creating lot of issues as clients business is running on sequencing.

Please let me know, how to solve this issue.

Warm Regards,

DNK Siddhardha.

[ambrish_mishra]

Hi,

First of all, is my assumption right ?

If yes, there are 4 ways to resolve the issue:

Option1 : Check with your DB team and make sure the updates to the table happen periodically and not real time. JDBC adapter has to be scheduled accordingly to make sure there is a time gap between the table update by the DB team and PI adapter polling the DB.

If the above is not posible....

Option 2: In sender JDBC, make the isolation level to serialzable and try.

If the above does not work

Option 3:

In case you have a sequence number or counter field in the table like 1, 2, 3 ........ for the records in DB, you can look at Rene's solution in thread http://scn.sap.com/thread/3372631

SELECT      *

      FROM  tablename

      WHERE NO in (

            SELECT DISTINCT TOP 10 NO

                  FROM  tablename

                  WHERE Status = 0

                  ORDER BY NO)

      ORDER BY NO

UPDATE tablename SET Status = 1 where NO in (select Distinct top 10 NO from tablename where Status = 0 order by NO)

Option 4: Work with a stored procedure. this is a sure shot way to avoid conflicts. I have the solution but this can come later. you should be able to resolve the issue from above.

Hope it answers your question

Ambrish/scn.sap.com/thread/3361885<br/>

Other threads for your reference.

Please also go through the threads.

http://scn.sap.com/message/14163380
http://scn.sap.com/thread/3361885

[ambrish_mishra]

Hi,

Please close the thread if your question is answered.

thanks

Ambrish