PI/XI Sender JDBC Select query and Update Query to limit our records to be picked up by XI from Database to avoid Huge Message processing failed.

[former_member185881]

In this article I’d like to show you how to avoid Huge Message Processing Failed into XI, if XI picks up large number of records from your SQL Server Database. To achieve this we can always limit our records to be picked up by XI/PI but how?  

Here goes the solution--- 

Step 1:

As you know in your Sender Communication Channel you have to give SELECT Query and UPDATE Query as a mandatory parameter. This SELECT Query will pick up all the records from your source SQL SERVER Database and UPDATE Query will update the flag of those particular records which XI has picked up.

Now if in your source table there are around 50 lakhs records present unprocessed then XI will try to pick up all the 50 lakhs records and the message will be failed at Adapter Level because of huge size.

How to solve this problem ---

We have to use TOP command of SQL.

Using TOP command we can restrict the number of records to be picked up by XI.

Now how we can write TOP command for both SELECT Query and UPDATE Query. 

SELECT Query:

Select TOP 10000 fieldName1, fieldName2 from TableName where clause...

UPDATE Query:

Update TOP(10000) TableName set fieldName1='abc' where clause...

Now every time depending upon the Poll Interval only 10000 records will be picked up by XI and remaining 40000 records will be picked up by XI in subsequent next polls. With each poll 10000 records will be picked up.

So by using TOP command Huge file problem will never come. Enjoy…. 

STEP 2: 

Note:

Please don’t forget to make it Transaction Level Isolation = serializable

(under Advanced --> Additional Parameters in your sender Communication channel)

"