How to use Native SQL in receiver JDBC adapter

[Former Member]

Hi All,

Could someone pls advice me on how to use Native SQL in receiver JDBC adapter. We have a SAp to Oracle DB scenario.

Curenttly we are using Message protocol as XML SQL Format. and it's taking 3 hrs to insert 1.5 million records to the db. we want to try using Native SQL to see if it enchances performance.

Any suggestions are welcome...

Regards,

XIer

[agasthuri_doss · Accepted solution]

Hi,

Message protocol as XML SQL Format is the best way and 3 hours is good for 1.5 million records,If you want to increase the performance still go ahead for App server which will increase the Complete performance of SAP XI itself.

Regards

Sangeetha

[Former Member]

Hi XIer

selecting native sql as msg protocol means u have to pass the sql statement as message content. this statement will be transferred to the database for processing without unalteration.

for msg protocol xml sql format the sql statement is formed from the structure unlike native sql format.

The output of your mapping has to be the SQL Statement to be invoked on the Database. The output of the mapping hence has to be non XML and you might have to use a Java mapping.

follow the link and as pointed out the declaration needs to be like in xml format. this ensure the execution of the native sql commands.

http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

The most useful Thread

http://help.sap.com/saphelp_nw70/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

In your receiver JDBC adapter you need to have an option called LogSQL and there you can see the Query in the Audit Log on the Adapter in RWB.

This is discusses in the note : 801367

/people/michal.krawczyk2/blog/2005/06/28/xipi-faq-frequently-asked-questions

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

http://help.sap.com/saphelp_nw2004s/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

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

JDBC ADAPTER

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

/people/saravanakumar.kuppusamy2/blog/2005/01/19/rdbms-system-integration-using-xi-30-jdbc-senderreceiver-adapter

To install oracle drive in XI server, just check these links,

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/3867a582-0401-0010-6cbf-964...

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

http://msdn2.microsoft.com/en-us/library/ms177523.aspx

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

/people/alessandro.berta/blog/2005/10/04/save-time-with-generalized-jdbc-datatypes

https://www.sdn.sap.com/irj/sdn/advancedsearch?query=jdbc%20with%20multiple%20tables&cat=sdn_all

/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30 - File to JDBC

JDBC - http://help.sap.com/saphelp_nw2004s/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

multiple tables with JDBC - http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

JDBC Response /people/swaroopa.vishwanath/blog/2006/12/28/send-rfc-to-sap-xi-150-asynchronous

JDBC RECEIVER ADAPTER

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

regards

Sandeep Sharma

PS if helpful kindly reward points

[Former Member]

So if I have understood it correctly, for my scenario, i.e SAP (Proxy) to JDBC, I will first have a mappinf that would create the exact SQl statement as expected by DB, then I will have to use Java Mapping to put in a single string, i.e without XML.

But this java mapping would be in Adapter Module or second step of mapping in Interface mapping?

Even if there is a single field in the target structure, still there be a root xml tag around the sql statement that I generate...

Regards,

XIer

[Former Member]

Hi XIer,

Do you feel that if you write your own Java Code for executing JDBC query is faster. If so then you can create a JAVA proxy on receiver side which will use to update the database.

Try this and check for the performance whether it helps for you or not?

Regards,

Gaurav Jain

[Former Member]

Gaurav,

Could you please put some light on how to go about implementing Java proxy and how that would help..

Regards,

XIer

[Former Member]

Hi,

Native SQL String

An arbitrary SQL statement is expected as the message content. This statement is transferred unaltered to the database for processing

Please see the below link

http://help.sap.com/saphelp_nw2004s/helpdata/en/cf/406642ea59c753e10000000a1550b0/content.htm

http://help.sap.com/saphelp_nw2004s/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

Thanks

Swarup