JDBC adapter: multiple sequential SELECTs

[Former Member]

Hi all!

We have the following scenario:

From a ORACLE third-party-application's database we want to retrieve data from different tables (=different return structures) in a defined sequence (because the tables depend on each other). Only changed records should be read each time, selected by an flag present in each table.

Today we have an integration process that uses the JDBC receiver adapter in a synchronous call with multiple statements. The returned data is then split/mapped an send to the receiver system.

Because the synchronisation time should be reduced to 1-2 minutes we fear a serious overhead by the many integration process instances.

Is there any other way to achieve this aim?

Thanks in advance!

Hans

[Former Member]

Hans,

I think the reason why a BPM is used and multiple selects are handled by a Receiver JDBC adapter and not a Sender, is because Sender requires EXACTLY one select statement even in a stored procedure.

I have worked on a similar scenario and BPM was the straight forward answer to me. Using Java mapping is a good idea if you don't want to use BPM.

Cheers

Raju

Edited by: Raju Jampana on Sep 16, 2010 4:39 PM

The other thing you can do is write a stored procedure to do all your multiple selects, joins and whatever you want to do and stage this data in a temporary table before your Sender JDBC adapter polls with exactly one select statement.

[stefan_grube]

Could you use a stored procedure for this?

As a stored procedure runs in one LUW, you can access and update several tables the same time.

You need no BPM any longer and save much processing time.

[Former Member]

Hello Stefan

I have access to the database and could create a SP.

I have 8 different SELECTS (with each up to 8 JOINS incl. subqueries) but I am not advanced enaugh in ORACLE SQL to know how to collect the different resultsets into one result structure.

If you could help me a bit with this, I would much appreciate this, as this solution would have the best performance.

Thanks

Hans

[stefan_grube]

I am sorry, I work wit MS SQL and the stored procedure language is depending on database.

But you find tons of examples in Internet.

[Former Member]

Hi Hans,

The other way is that you can write a java mapping program which handles this sequence select statement.

First poll one table data by using Snder Jdbc adapter and ater polling this data you can connect to database once again using java mapping program for remaining select statements and that too in sequence.

You will be having full control for sequece execution of select statements in java mapping program itself.

Regards

Prasanna Kumar

[Former Member]

Hi Prasanna

Thanks for your quick answer!

Do you mean inside the mapping I should use the JDBC lookup API to select the additional data records?

Regards

Hans

[Former Member]

Hi Hans,

Yes you have to use Lookup API in a Java Mapping Program.

Here is the link on Lookups

http://help.sap.com/saphelp_nwpi711/helpdata/en/44/f593d8d6ab2461e10000000a1553f7/frameset.htm

Regards

Prasanna Kumar