High Volume JDBC Sender channel problem?

[Senthilprakash1]

Dear All,

I have developed a scenario in which i need to fetch entries from 3 tables in a single go. (Its a Asynchronous Scenario without BPM).

I see that what my sender adapter is fetching the entries i see the size of the message is too high.

The size is around 78,942,162 KB. and its not getting processed in moni.

The 3 tables have following field in common :

TABLE1 TABLE2 TABLE3

GUIID GUIDID GUIID

CHTOT CHTOT

Now my logic is to fetch one GUID from TABLE1 and corresopnding entries frm table2 &3.

But i see that TABLE3 has more than 7000 rows corresponding to the single GUID and hence when i try to fectch the entries is see a high volume message.

query: SELECT * from TABLE1 U1,TABLE2 U2,TABLE3 U3 WHERE U2.GUIID=U1.GUIID AND U3.GUIID=U1.GUIID AND U2.CHTOT=U3.CHTOT AND U1.GUIID IN (SELECT MIN(GUIID) FROM U1.TABLE1 WHERE CTECMA = 'WMSSC' AND CTSTS = 'N')

Is there anyother possible design by which i can restrict the amount of message comming into the sender channel?

Please Advice.

Thanks and Regards,

Senthilprakash.

[former_member206760 · Accepted solution]

Tricky one....

you can use a BPM for this...

http://www.flickr.com/photos/36855503@N03/3394552752/

[Senthilprakash1 · Accepted solution]

Dear Tarag,

Yes we have achieved it with BPM. I wanted to test the same without BPM asynchronous scenario.

Is it possible to redesign this in any other Asynchronous way to accommodate this huge amount of data in PI?

Regards,

Senthilprakash.

[former_member206760 · Accepted solution]

no i do not think there is any other way apart from BPM...

[Former Member]

Hi, how many records are you processing? which is the total of the union af tabe 1 table 2 and table 3, do you really need all this records?, have you indexed the tables?, maybe you will have to arrange the select query, it's not performing to simple view, every time you have to pool only the records that you need.

Regards.

Marco

[Senthilprakash1]

Dear Marco,

Max a single table will contain 8000 Rows so it means that the other two tables will also contain 8000 Rows i suppose.

I don't know weather the tables are indexed or not.

Dear All,

I wanted to know is XI JDBC Sender Adapter capable of handling Multiple select query output coming from a stored procedure?

STRUCTURE LIKE:

row1

<fields>

row2

<fields>

row3

<fields>

Please advice,

Senthilprakash.

[Former Member]

Hi... Senthil,

XI JDBC Adpater is capable og handling multiple rows..

You can use Pacakge and Cursors..

Thanks & Regards,

Leela