Oracle database integration with SAP PI for high volume & Complex Structure

[Former Member]

Hi

We have requirement for integrating oracle database to SAP PI 7.0 for sending data which is eventually transferred to multiple receivers. The involved data structure is hugely complex (around 18 child tables) with high volume processing requirement (100K+ objects need to be processed in 6-7 hours). We need to implement logic for prioritizing the object i.e. high priority objects must be processed first and then objects with normal priority.

We could think of implementing this kind of logic in database procedures (at least it provides flexibility for implementing data selection logic as well as processed data can be marked as success in the same SP) but since PI sender adapter doesn't support calling Oracle stored procedures currently so this option is rules out. we can try implementing complex data selection using oracle table function but table function doesn't allow any SQL query which changes data (UPDATE, INSERT, DELETE etc) so it is impossible to mark selected objects in table function from PI communication channel "Update Query" option.

Also, we need to make sure that we are not processing all the objects at once as message size for 20 objects can vary from 100 KB to 15 MB which could really lead to serious performance issues for bigger messages.

Please share any implementation experience for handling issues:

1 - Database Integration involving Oracle at sender side

2 - Complex Data structures

3 - High Volume Processing

4 - Controlled data selection from database to contro the message size in PI

Thanks,

Panchdev

[Former Member]

Hi,

regarding:

2 - Complex Data structures

I did some integration scenarios with JMS and complex XML structures (XSLT / message mappings, JMS receiver and sender adapter property funtions, ...). Data size varied from some kb upto 10 MB. Don't know whether it's much comparable but we never had isses with it.

3 - High Volume Processing

Same scenario was one of high frequent and volume scenarios. Managed processing times (inkl. message split and reply to sender) of < 30s (average of 90 % running < 5s). Overall volume of upto 160k messages a day on a clustered system (2 nodes) without any problems.

Regards,

Kai

[former_member206111]

Hi,

We can call the stored procedure using receiver adapter using ccBPM, we can follow different approaches for reading the data in this case.

a) In this a ccBPM instance needs to be triggered using some dummy message, after receiving this message the ccBPM can make a sync call to the Oracle database the store procedure(this can be done using the specific receiver data type strucure), on getting the response message the ccBPM can then proceed with the further steps.The stored procedure needs to be optimized for improving the performance as the mapping complexity will largely get affected by the structure in which the stored procedure returns the message.Prioritization of the objects can be handled in the stored procedure.

b) In this a ccBPM instance can first read data from the header level table, then it can make subsequent sync calls to Oracle tables for reading data from the child tables.This approach is less suitable for this interface as the number child tables is big.

Pravesh.

[Former Member]

Hello Pravesh

Thanks a lot for the update but it doesn't help to resolve the problem.

We have implemented this solution for oracle version <10.g but this is not a feasible approach especially in case of high volume processing as we need to poll data more frequently which will generated large number of integration process instances which will adversaly impact the overall system performance. We have experienced other issues as well with timeout of the JDBC call initiated from BPE. If synch timeout for BPE and AE is not in synch then it led to data loss. This may even lead to database performnace issue as well if procedure execution takes more time then polling trigger interface. So overall we dont prefer this option espcially for high volume processing.

Panchdev