Weird behaviour of sender JDBC

[Former Member]

Hi Experts,

I have a scenario where JDBC ->Idoc--Asynchronous

It is a SQL server and the isolation level in the channel is set to 'Repeatable_Read'.

The select query I am using to select rows from the DB is

"set rowcount 2000 select * from P_ORDERS where STATUS = 'N'

and the update query I am using is

"update P_ORDERS set STATUS = 'C' where STATUS='N'

-

---

There were 7194 orders published; and the same number was sent to the receiver(checked in idx5).

Now the issue is,

-- The same order was picked up multiple times and there is no set pattern on how the data was picked.

( some orders were picked 4 times, some were 3 times)

-- some orders were not even picked.

I am trying to analyse what could have happened .

Your comments would be very helpful.

Regards,

Varun.

[Former Member · Accepted solution]

Hi Varun ,

Please avoid Set Rowcount . As it Causes Microsoft® SQL Serveru2122 to stop processing the query after the specified number of rows are returned.

Kindly go though : http://msdn.microsoft.com/en-us/library/aa259189%28SQL.80%29.aspx

Regards,

[agasthuri_doss]

Hey,

> "set rowcount 2000 select * from P_ORDERS where STATUS = 'N'

>and the update query I am using is

>"update P_ORDERS set STATUS = 'C' where STATUS='N'

After Reading the records change the status " Other than N "

Cheers

Agasthuri

[Former Member]

Hi Satish,

What I am trying to do is to pickup 2000 records per poll from the database, hence I have given set rowcount as 2000.

I have tried executing it individually in the database and I get the desired result. This solution was working fine for 4 months and now this is one case where it behaved this way(unfortunately at a very critical time in my project).. So i am trying to know what was the cause of this issue.

Hi Agasthuri,

Initially when the records are placed in the database they have the STATUS column set as N meaning new.

After selecting the data I am changing the status to C from N

Please let me know if anything is not correct here.

Regards,

Varun.

[Former Member]

remove "set rowcount 2000" from your query, let PI do it on its own,

change the isolation level in CC to "Serializable" , this will resolve your problem.

[Former Member]

Hi,

The problem if I do not pick 2000 records per batch is that the message size in XI is exceeding 10 MB (we have defined a seperate queue to handle messages with size more than 10MB as we do not want to disrupt the normal message flow to other interfaces) and is processing relatively slow. In our landscape these messages have to be processed as soon as they come in, that is why we have split up into batches of 2000 each. Hence I cannot remove that statement.

Regards,

Varun

[GabrielSagaya]

I think you have to write stored procedure and call the store procedure name in select query statement.

http://www.devx.com/getHelpOn/10MinuteSolution/20564

[Former Member]

okay , what is your polling time, and what is the time it takes to update status database and insert new record.

have you make isolation " serializable" , is there any change in behaviour after this?

[Former Member]

My polling interval is 5 mins.

and the update happens immediately. the data was put into the database in a single second(all 7194 records)

[Former Member]

Varun,

It seems there is an error in the sql query. Can you tell what exactly you are tyring to achieve in the query?

Also if possible can you run the query at the database level and see what it is doing exactly. Whatever the result you get at the database level you should be able to see the same in XI also.

Regards,

---Satish