PI, how to handle high volume data per jdbc adapter

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?

6 Comments

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

Greg Cantlon December 23, 2009 at 12:19 pm

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’

like (0)
1. Shen Peng Post authorDecember 23, 2009 at 4:37 pm
  
  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
  
  like (0)
Jawed Macknojia December 23, 2009 at 9:01 pm

What about restricting the number of fetched records with a LIMIT clause and LIMIT clause is supported by most databases.

like (0)
1. Shen Peng Post authorDecember 25, 2009 at 2:11 pm
  
  Thank you for your input. But ‘limit’ only works on MYSQL.
  
  like (0)
Sridhar Upadhyayula December 24, 2009 at 2:46 am

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.

like (0)
1. Shen Peng Post authorDecember 25, 2009 at 2:45 pm
  
  Thank you. But in my case, Informix, ‘select first n’ can not be used in clause .
  
  like (0)