Sender JDBC Adapter : Urgent Question

[Former Member]

Hi,

I am working on a Oracle - XI - R/3 scenario. This scenario is working fine currently. The question that I have is what if I more than 500,000 records existing in the oracle table, then how many records will be picked up by the adapter when it polls the Oracle table . Will it try to pick up all the records, or does it pick up records in small packages..Can I set up the package size somewhere ??

I havent found an option in the adapter as such, and do not have high volumes of data in the Oracle table yet, so havent been able to run tests myself.

Thanks a lot

Any help will be appreciated

Mayank

[Former Member · Accepted solution]

With Oracle you have a specific syntax for the query, for instance:

select name from user where rownum < 10

you will get the first 10 entries only, thanks to the "rownum" embedded field.

Refere to here:

http://www.akadia.com/services/ora_important_part_3.html

Regads,

Sandro

[Former Member · Accepted solution]

Thanks for your response Sandro. But ultimately I have to transfer all the records from the Oracle table to R/3. So if I hard code " select name from user where rownum < 10 " in the JDBC adapter, then what about records with rownum > 10.. I need to transfer those also ..

What is the solution for that ...

Please advise

Thanks

Mayank

[Former Member · Accepted solution]

You must to track which records you trasfered alredy with the standard function of JDBC sender adapter "Update SQL Statement".

Please refere to the official docu for this:

http://help.sap.com/saphelp_nw2004s/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm

Sandro

[Former Member · Accepted solution]

Hi,

ASFIK there is no property parameter provided by the adapter.

One option will be if you can restrict them at the ORACLE level.

Regards

Vijaya

[Former Member]

Bhavesh, Tim, Sandro ..

Thanks for giving me ideas in getting my issue resolved. I think I have the solution finally. I controlled the data being selected using the

SQL Query Statement " select * from zbwxi where rownum < 5000 and readflag = 'U' " and in the

update sql statement I put " update zbwxi set READFLAG = 'P' where rownum < 5000 and readflag = 'U' .

ZBWXI was by Oracle table which has a field called readflag.

I have run a few tests and the results look good to me ..

Let me know if you need explanation on why this worked ..

thanks for all your help

Mayank

[Former Member]

Hello Bhavesh,

Do you know approx how many records get pulled at one time by the sender JDBC adapter when the number of records in the Source table is very large. I am just hoping that the the adapter does not try to get the entire table ( 200,000 to 300,000 ) and the message fails in AFW due to memory issues ..

How can I design this ? Any suggestions ...

thanks in advance

[Former Member]

Mayank,

if you do not adopt the proposed solutions, the adapter will send ALL the records, of course, it's the role of it to do so.

You got a lot of good ideas and suggestions from SDN users.

Regards,

Sandro

[Former Member]

Hi Tim,

Using a sender JDBC adapter, you cannot execute a stored procedure if I remember correctly

thanks

Mayank

[bhavesh_kantilal]

> Hi Tim,

>

> Using a sender JDBC adapter, you cannot execute a

> stored procedure if I remember correctly

>

> thanks

> Mayank

You are correct partly! You cannot execute an oracle Stored procedure as Oracle Stored Procedures return a Cursor, but, if you are accessing some other DB like a SQL server , then you can use an Stored Procedure in sender JDBC adapter.

Like mentioned, you should limit the number of records selected on the basis on the selected query and then use the Update Statement to update only those records that are selected. Apart from using an SQL expertise, the JDBC adapter does not provide a standard way to achieve this!

Regards,

Bhavesh

[former_member431549]

I believe it requires SP15 or 16, but you can use stored procedures in oracle with sender channel. Other than oracle, the possibility existed before these SP levels.

[bhavesh_kantilal]

Tim,

Thanks for that clarification. Will surely try it now!

Mayank -- Like pointed by Sandro, all records will be pulled in accrodance with the Select query. I dont have exact numbers as such but I have seen a few instances when the load was high the Adapter Framework could not pass the messages to the Integration Engine.

So you would need to filter the same in the select query itself. Catch hold of an SQL expert who can help you in this and then do a trial and error to get the optimum data!

Regards,

Bhavesh

[former_member431549]

You could also use a stored procedure to control how many rows are retrieved on each poll of the database. This would be my suggested route.