Skip to Content

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.

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 TOP 10000 fieldName1, fieldName2 from TableName where clause…



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: 


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

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



You must be Logged on to comment or reply to a post.
    • With Oracle you can use rownum - BUT you have to be very careful with your update.

      Rownum is evaluated AFTER the data is fetched, and before any ORDER BY is applied. So the following could return different results depending on what the optimizer does...

      SELECT *
      FROM table_of_1_to_100
      WHERE rownum <= 10
      ORDER BY number_column;

      To get around this you have one of two options:
      1. Write the query so that you can be sure of the records you will receive/update...

      SELECT *
      FROM (
      SELECT *
      FROM table_of_1_100
      ORDER BY number_column)
      WHERE rownum <= 10;

      2. Have a two phase process (my preference) to ensure you only update records that XI receives. To do this turn on the "TEST" flag in your communication channel, so that the channel only reads and does not update. Have two mappings - one to map the data to your target system, the second mapping to create the updates to write back to the source system. The second update should also set a semaphore so that your communication channel won't keep reading data. If you call a stored procedure instead of a query then you don't need the semaphore or the second mapping as the stored procedure can take care of the logic for you.

      • And UPDATE query like this

        UPDATE mytable set STATUS = 2 WHERE ID in  (SELECT ID FROM (select * from mytable WHERE STATUS = 1 ORDER BY ID) WHERE rownum <= 10 )

    • Hi

      In the above blog i have clearly mentioned, this is for SQL Server for other Databases i cant comment coz i have not tried.

      Dheeraj kumar