Sender JDBC adapter : data read from db but not in PI

[Former Member]

Hi Experts,

I am in the middle of a straneg situation. At random intervals I see a situation where data is read from the legacy database (oracle) and the change indicator is changed to read but the data is not visible in the communication channel monitoring or in the sxmb_moni...

I'm wonderign how and where to track this..

Please share your thoughts

Regards,

[Former Member · Accepted solution]

Did you check in message monitor of runtime workbench?

Do you have another sender channel running on another PI system (dev or test) reading the same DB?

[Former Member · Accepted solution]

Hi All,

There is only one channel accessign the legacy databse and I see the records are missign at random intervals.

PI is updating the tobe processed column in the oracle DB but I see no sign of the message in the Communication channel monitorign or the sxi_monitor.

Is there any way I can check the adapter logs at the OS level? May be there could be soem hint there.. is is possible to check?

[Former Member]

Hi All,

Is there a chance that the update query is accessign more records than select ?

I have put this as my select statement : select * from tablename where unread = "Y"

and in the update i have written update tablename where unread = 'N'

need your view experts .. pls

[ambrish_mishra]

Hi,

Should  be update tablename set unread=' N' where unread = 'Y'

Please check

Ambrish

[ambrish_mishra]

Although the above should work well under normal circumstances, but you need to make sure that the table is not updated at runtime when PI is doing a select. example; Select fetches 8 records and when it went to update, the table got updated with 2 more records and update happens on 10.

I am just trying to make you aware of the risk involved with such a select and update.

Hope it helps!

Ambrish

[Former Member]

I happen to read a thread where stefan mentioned two things.. first make the update and select query equivalent and then to make the isolation level to  serialzable ..

Doen both.. no errors reported so far.. finges crossed

[ambrish_mishra]

Hi,

I am aware of Isolation levels and serializable works but has a flip side... it can cause locking issues in high volume/frequency. Hope it is not the case with you.

BTW, did the Update statement correction work ? If yes, please mark the thread as answered.

Thanks

Ambrish

[ambrish_mishra]

Problem with your select statement :

select * from tablename where unread = "Y" 'Y'

[ambrish_mishra]

Curious! How was it resolved. Statements ?

Ambrish

[Former Member]

Hi Ambrish,

As I had mentioned, I saw a reply from Stefan who had suggested to set the isolation level to serializable.

And as you suggested I made the update and select query access the same numbe rof records.

I suspect what was happenign was that before the data is read the table was updated with new data and at that split second it was update to processes before it was read.

Regards,

Tauseef

[naveen_chichili]

Hi,

check if the JDBC channel trying to fetch the data and not able to process in PI.

This some times happens when JDBC Adapter service is locked in SAP PI.

Thanks,

Naveen.

[iaki_vila]

Hi Mohd,

Check the communication channel monitoring, may be the data is read and updated but some error occurs before the message come to the integration engine or fail in the adapter engine.

Also you could use the test tool Note 1085539 - XI JDBC/FTP connection Test and XML Validation Application to check well-formedness of XML file.

Regards.

[ambrish_mishra]

Hi,

Martin has given a good hint. however you can check with database team to trace when the record is read, the call made from which host so you would know if multiple PI systems are connected.

You can also simply check the monitors in your PI systems in the landscape.

Hope it helps!

Ambrish