Bulk insert via JDBC receiver

[thomas_jork]

Hello

I have a design question regarding the insert/update

of many rows(up to 100000 rows) into a MS SQL database via JDBC receiver adapter.

R/3(via RFC) -> XI -> DB(via JDBC)

The prefered way is to use a stored procedure, since any

changes to database design are encapsulated in the stored

procedure and no rebuild of the mapping is necessary.

But using this way the stored procedure has to be called

for each single row and this make the complete insertion

very slow.

The stored procedure itself checks if the row already

exists and updates or inserts accordingly afterwards.

Is there any other way to put this amount of data into

the database. Using a flatfile and the miscrosoft bcp

comand or DTS is not really an option.

May be I can put the logic of the stored procedure into

an database trigger and fill a temporary table with

the bulk data first. In this case I can use an Insert

statement instead. Is it possible to insert such an

amount of data within an single Insert statement?

Thanks in advance

Thomas

[moorthy · Accepted solution]

Hi Thomas,

You can insert multiple rows, with changing Reciever JDBC document structure with multiple occurence. in this case, you need to configure the reciever jdbc adapter

to view the structure-

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

But i think, you can insert multiple records into one call of Stored Procedure. Did you try inserting multiple records with stored procedure..?

Hope this helps

Regards,

Moorthy

[thomas_jork]

Hi

Thank you for your hints.

I have never used these java proxies, but i will make

some tests in the following days.

Regarding the timeouts. The RFC timeout is from the R/3

business sytem and therefore I can not raise the timeout

value. But it is ok when I use tRFC.

Kind regards

Thomas

[bhavesh_kantilal]

Hi thomas,

Do keep us informed on how you proceed with this issue.

Regards,

Bhavesh

[thomas_jork]

Hello Moorthy

Yes I have called this stored procedure with multiple

occurrences of the statement tag. It is working. But it

takes about 50 minutes for about 25000 rows. And in the

meantime the synchron RFC called runs into a timeout.

So Xi is calling the Interface and the adapter just one

time, but the SQL-XML statement leads to 25000 calls to

the stored procedure on the database side.

When I use a direct Insert from XI it takes about 20

minutes, which is a very long time as well and that leads

also to a timeout of the RFC call. Regarding the RFC call

I have to move to an tRFC. OK

But to generalize my question ...

What is the fastes way to get bulk data into a database?

Any experiences?

Thank you

Thomas

[moorthy]

Hi,

Just one hint, is it will be a better way with Java Proxy ? i.e calling a Java proxy and from Java Proxy you call Stored Procedure/sql statement.. ? I did not try.

But you can think of this..

And one morething, if you have RFC timeout problem, you can increase the timeout parameters. For Reference

/people/michal.krawczyk2/blog/2006/06/08/xi-timeouts-timeouts-timeouts

Also , ABAP proxy is also a better option instead of RFC.

Regards,

Moorthy