Sender JDBC adapter

[Former Member]

Hi All,

I have the  issue where we need to fetch 6 million rerecords from SQL DB 

and insert into ECC via Proxy. The issues is SQL DBA does not want to provide the flag for

updating the records ( through which we can split the data) and want read only access.

So fetching so many records in one go is one issue.

Other issues is how many messages will be generated in SAP PI( going to to ECC) ..

Will there be 6 millions? I guessing using multi map, we can restrict it but overall design looks shaky..

Can you advice the best way forward.

Thanks!

[vadimklimov]

Hi,

There is no consistent way to limit amount of records polled and processed by JDBC sender adapter unless SELECT with TOP records is used together with subsequent UPDATE so that polled records are flagged in UPDATE statement and are not polled during next polling executions.

There are mechanisms within Messaging System to handle large messages processing (see SAP Note 1727870 for details of message permit concept and configuration), but such large amount of polled records may lead to memory being exhausted even during JDBC sender channel execution, before the message reaches Messaging System.

In JDBC sender channel, it is possible to prevent processing of large messages by using additional parameterization (see SAP Notes 1296819 and 1253826 as well as Configuring the Sender JDBC Adapter - Advanced Adapter Engine - SAP Library), but this will really prevent processing in case JDBC adapter attempts to poll too large set of records - which, I assume, is not something you want here. This configuration helps to protect PI system from exhausting memory in case it comes across large amount of data polled from a remote database. But there is no way in JDBC sender adapter to poll records in chunks just with SELECT statement.

Having written this, I would suggest trying to convince database administrators to let you to introduce some flag / indicator that could be updated by JDBC sender channel for records that were polled by it. It doesn't necessarily have to be the same table where polled application data is stored - it can be some other technical table, which is then used in a view to join it with a table holding application data (and then JDBC sender channel shall poll using view and update that flag field correspondingly). Otherwise, there is no good way to security and consistently ensure JDBC sender polls records in chunks.

Regards,

Vadim