DB lookups

[Former Member]

Hi all,

We have to make use of DB lookups in XI, can I maintain that DB table in the data base of XI?

Can any one tell me the advantages as well as disadvantages of maintaining like this?

Regards

Hasini

[Former Member · Accepted solution]

Hi Hasini,

We have to make use of DB lookups in XI, can I maintain that DB table in the data base of XI?

You can maintain but performance wise it is not suggestable for maintaing the DB in XI Application server.

and even if you are doing so other user who want to access the DB are able to access the XI data base which is not good practise I think.

If DB lookup is made from the Native system is much faster than the other system(this is the only advantage I think so)

Regards

Harsha

[Former Member]

Lookup in mapping is the feature provided by SAP to lookup the data in the target R/3 or DB systems with the API provided.

You need to write UDF in order to implement the API's provided by SAP.Consider the below example

VendorNumber-UDF--CURR

The scenario is legacy to SAP. The legacy system doesn't provide the currency details. But the target field need's to be populated with currency value.

"The business rules says there are values maintained in SAP Table where if you pass VendorNumber it will return thr currency to you"

So what you can do? You can write UDF implementing SAP Provided API's and do a lookup in the SAP System and get back the currency value and populate them in CURR field.

I hope it clears a bit.

Please find the below blogs

DB Lookup: /people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

RFC Lookup:https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/a03e7b02-eea4-2910-089f-8214c6d1b439

There are three types of look ups u can do

RFC look up

SOAP look up

JDBC look up

What is Lookup and why we need:

Within an XI mapping it is a common requirement to be able to perform data lookups on-the-fly. In particular, there may be a need to look up some data that is maintained in an R/3 application.

In the error handling topic we have seen the different validations which need to be performed on file. This can be done through Lookup.

Some use cases:

• Look up material number from table MARA.

• Look up cost center budget.

• Look up employee information.

• Look up unit-of-measure (UOM) information from table t006a.

• Lookup for raising an alert.

The purpose of the lookup may be:

• To perform application-level validation of the data, before sending it to the backend.

• To populate fields of the XML document with some additional data found in the backend application.

This is a form of value transformation.

The "value mappings" offered by XI are not adequate in this case, since the data would have to be manually entered in the Integration Directory.

There are two ways in which we can do lookup:

• Call lookup method from GUI mapping.

• Call lookup method from XSLT mapping.

Lookup method from GUI mapping can be called using any of the following ways.

• RFC lookup using JCO (without communication channel)

/people/sravya.talanki2/blog/2005/12/21/use-this-crazy-piece-for-any-rfc-mapping-lookups

• RFC lookup with communication channel.

/people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

• Lookup using JDBC adapter.

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

/people/sap.user72/blog/2005/12/06/optimizing-lookups-in-xi

• CSV file lookup.

/people/sundararamaprasad.subbaraman/blog/2005/12/09/making-csv-file-lookup-possible-in-sap-xi

Lookups with XSLT - https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/8e7daa90-0201-0010-9499-cd347ffb...

/people/sravya.talanki2/blog

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/05a3d62e-0a01-0010-14bc-adc8efd4...

DB lookup - /people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

SOAP Lookup - /people/bhavesh.kantilal/blog/2006/11/20/webservice-calls-from-a-user-defined-function

You can refer to these links.

/people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer Absolute stealer.

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

For Java APIs and also here you can map that how many types of lookups are possible in XI.

http://help.sap.com/javadocs/NW04/current/pi/com/sap/aii/mapping/lookup/package-summary.html

[former_member556603]

Hi,

Hope this information is useful to you..

Look up tables

.

In the error handling topic we have seen the different validations which need to be performed on file.

This can be done through Lookup.

Some use cases:

• Look up employee information.

• Lookup for raising an alert.

The purpose of the lookup may be:

• To perform application-level validation of the data, before sending it to the backend.

• To populate fields of the XML document with some additional data found in the backend application.

There are two ways in which we can do lookup:

• Call lookup method from GUI mapping.

• Call lookup method from XSLT mapping.

Some useful blogs...

/people/shabarish.vijayakumar/blog/2006/02/13/unable-to-open-iresrid-xipipi-71-updated-for-pi-71-support

Lookup - /people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

DB lookup - /people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

SOAP Lookup - /people/bhavesh.kantilal/blog/2006/11/20/webservice-calls-from-a-user-defined-function

http://help.sap.com/saphelp_nw04/helpdata/en/cf/406642ea59c753e10000000a1550b0

Lookup’s in XI made simpler - /people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

How to check JDBC SQL Query Syntax and verify the query results inside a User Defined Function of the Lookup API -

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

/people/prasad.illapani/blog/2006/10/25/how-to-check-jdbc-sql-query-syntax-and-verify-the-query-results-inside-a-user-defined-function-of-the-lookup-api

Lookups - /people/morten.wittrock/blog/2006/03/30/wrapping-your-mapping-lookup-api-code-in-easy-to-use-java-classes

Lookups - /people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

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

/people/sap.user72/blog/2005/12/06/optimizing-lookups-in-xi

Lookups with XSLT - https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/8e7daa90-0201-0010-9499-cd347ffb...

/people/sravya.talanki2/blog

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

/people/sravya.talanki2/blog/2005/12/21/use-this-crazy-piece-for-any-rfc-mapping-lookups

/people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

/people/sap.user72/blog/2005/12/06/optimizing-lookups-in-xi

/people/morten.wittrock/blog/2006/03/30/wrapping-your-mapping-lookup-api-code-in-easy-to-use-java-classes

Thanks,

Satya..

Reward points if it is useful..

[Former Member]

Dear Hasini,

Performing db lookups can be useful to complete or enhance data when performing transformation in XI ... I use it in some of our XI scenarios, using helper/wrapper classes that enhance native db lookup APIs shipped with XI (it provides cache feature to avoid performing sql statements for the same values, error management, etc).

The drawback of lookups is the "runtime" costs, as it could decrease performances of your mappings (db performing slowly, badly designed statements or coding etc).

But I'd not recommend maintaining data tables within XI (I compare XI to a consummer of data, like the web dynpro concept, and its role is to manipulate data, not as storage area) - though this is a "personal" rule.

Rgds

Chris