Skip to Content
Author's profile photo Dheeraj Kumar

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 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)

 

image

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      i guess this does not work with Oracle.
      Author's profile photo Dheeraj Kumar
      Dheeraj Kumar
      Blog Post Author
      I have done with SQL SERVER not tested with Oracle so cant comment on this.

      Thanks
      Dheeraj Kumar

      Author's profile photo Former Member
      Former Member
      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.

      Author's profile photo Artem Solohin
      Artem Solohin

      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 )

      Author's profile photo Former Member
      Former Member
      I think, this solution would not work with DB2. We need some intelligent coding in there.
      Author's profile photo Dheeraj Kumar
      Dheeraj Kumar
      Blog Post Author
      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.

      Regards
      Dheeraj kumar

      Author's profile photo Former Member
      Former Member
      It gives me a lot of help for my scenario
      Author's profile photo Former Member
      Former Member

      Hi,

      Can you brief me what is the use of giving

      Transaction Level Isolation = serializable in jdbc adapter.