Skip to Content

PI, how to handle high volume data per jdbc adapter

December 23, 2009 | 649 Views |
 peng shen2
Follow
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 {code:html}http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/2016a0b1-1780-2b10-97bd-be3ac62214c7{code}) set sapflag = ‘1’  where sapflag = ‘0’ and ROWNUM <10001*

For SQL Server, You could try this:

*UPDATE where sapflag =’0′)*

 In my case, It’s Informix.’select first n’ can not used in SQL clause.I may be able T*o 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? 

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member

    Nice idea, but it seems a bit over-engineered to me. I’m not sure about Informix, but the equivalent to rownum in SQL Server is TOP: where sapflag = ‘0’

    (0) 
    1. Shen Peng Post author
      Thanks for your kindly comment. ‘Select top n’ should work for SQL Server, but not able to ‘update’.
      I have update my blog.
      Regards
      Shen Peng
      (0) 
  3. Former Member
    This can be acheived without BPM, we have implemented a similar scenario without BPM. In the JDBC adapter you have to use the “Query SQL Statement” to retrieve the records and “Update SQL Statement” to update the records. Important aspect is that the Update SQL statement would be a complex SQL with some sub queries in the SQL statement. Might need to take SQL developers help to design the query.
    (0) 

Leave a Reply