Couples days ago, I used PI JDBC Sender Adapter to retrieve data from A database system. When PI fetch about 900,000 rows records at one time, J2ee Stack got OOM(out of memory) error, restarted over and over again.

After applied SP 20, PI could throw an exception, and the communication channel  stop. Administrator had to something to make the records into smaller amount. This is final solution.(Note 1253826 - Configuring Maximum Message Size Limits)

But this can not be  the final solution to our customer.  

Best performance message size should be in the range of 1MB to 5MB.(PI Best Practices: Sizing & Performance Tuning http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/2016a0b1-1780-2b10-97bd-be3ac62214c7)

For Oracle database, function  ROWNUM will be very useful. Then PI fetch a certain rows but not ALL. For example:
select * from <tablename> where sapflag = '0' and ROWNUM <10001
update <tablename> set sapflag = '1'  where sapflag = '0' and ROWNUM <10001

For SQL Server, You could try this:

UPDATE <tablename> SET sapflag = '1' where <primay key>  in (select top <n> <primay key> from  <tablename> where sapflag ='0')

 In my case, It's Informix.'select first n' can not used in SQL clause.I may be able To update the flag in JDBC Sender Adapter with a procedure or SQL program. But it's not easy to find an Informix expert.

 Here lots of warmhearted friends gave me lots helpful suggestions. That's very nice. I will try harder to aoid using a complex BPM. What I have done is to design BPM:

1, ERP system trigger the process;

2, send SQL 'select count(*)' to DB;

3, if count<>0, retrieve first 10000 rows;

4,send SQL to update flag identified by primay key;

5,send XI message to ABAP proxy which write data into SAP;

6,loop step 2;

7, if count=0, sned 'end' message, end of the process.

There is a simple test, total  7318 rows, retrieve 1000 rows each time. What happened in SXMB_MONI after trigger once? 

At the SAP side, write a program to call the ABAP proxy 'trigger'. To avoid another trigger at the same time,  the program write log information, and check whether another process is running.

Even more, we can add a field like 'company code' in the trigger message, append a 'where' condition to the SQL clause.  And the 'end' message, add a field 'total' which stands for how many rows retrieved totally. Then we can write a report to check when,  how many rows and who about the process.

Therefore, SAP system uses can run the process better. With JDBC Receiver adapter and BPM, we can achieve the requirement better.

OK.Thanks for your time. If you have any better idea please leave a comment.