JDBC issue

[Former Member]

Hi,

We have done one JDBC to proxy scenario in our PI 7.3 . We have used JDBC adapter as sender.

we having issue while picking records from date Base.

there are 15000 records in the table. when i made all records flag as NULL . adapter started picking the records and updating as flag as 1

all records flag changed to 1. in these 15000 records 500 records not picked by PI (to source payload). even though flag changed as 1.

please suggest what might be the issue.

Query i used :-

processing parameters:-

quality of service is exactly once

select * from (select * from in_create where flag IS NULL ) where rownum < 200

update in_create set flag = 1, update_date = SYSDATE where flag IS NULL and rownum < 200

Advance Parameters

Transaction isolation level is serializable.

clusterSyncMode                lock

Regards

Raju

[baskar_gopalakrishnan2]

I suspect rownum does not go well with select update sequence operation sametime. Remove rownum and try to select all the records  and update all.  Your transaction setting is fine.

[former_member191435]

Hi Raju,

Can you please check with your DB admin for this....

Cheers,

Cnu

[ambrish_mishra]

Raju,

This is possibly because when the select was made, the count of records in the table was 14500 and when the update was done it was 15000. Check with DB admin if there was any other job running and updating the table when PI was reading the table.

One question though:

what is rownum ? is it some kind of a sequence number ?

Please look at the thread below for an alternate solution:

http://scn.sap.com/thread/3381809

Look at the bottom of the thread where I have proposed a solution.

Hope it helps!

Ambrish

Message was edited by: Ambrish Mishra

[Former Member]

Hi Ambrish,

rownum means we are not sending all messages once.

sending 200 records in one  message as serializable.

regards

Raju

i

[Former Member]

Hi Ambrish,

i checked with DB admin he said.There is no other job to update the table once flag is set to NULL, the PI is the only one to read from it.

Regards

Raju

[Former Member]

Hi ,

JDBC sender channel is giving the blow error:-

Cannot acquire lock for key fe431cd09f8d35f9adc937687885 for 10 polling intervals. The lock is in use by server…..7340151.for further analysis ,take thread dumps on the server 7340151 according to SAP Note 1095473.

Regards

raju

[ambrish_mishra]

Hi Raju,

What is the poll interval of your adapter. It should not be too short.

Check in NWA whether there are any locks on the adpater.

For the 7.1 versions, please note that the Lock Manager is under the /NWA/locks,or on the location "Availability and Performance Management -> Locks

For Visual Admin, LOCKING ADAPTER->Display locks-> Unlock the adapter and restart the channel.

Hope it helps!

Ambrish

[ambrish_mishra]

Hi Raju,

Are you still going with rownum design or alternate design which I suggested.

I don't think rownum logic will work because logically the update statement does not ensure you are updating the same records which you have read.

I suggest you take a look at the bottom of thread http://scn.sap.com/thread/3381809

Reading all records in one go might lead to performance issues.

Hope it helps!

Ambrish

[Former Member]

Hi Ambrish,

thank you ,

got the locks and removed . channel working fine now.

Regards

Raju

[ambrish_mishra]

Please close the other thread and this one too if your questions are answered.

Thanks

Ambrish