Skip to Content

Introduction

In SAP NetWeaver Process Integration (PI) 7.1, there is now graphical support for JDBC and RFC mapping lookups in message mappings, instead of having to create a user-defined function with the relevant lookup code as in previous releases (e.g. XI 3.0/PI 7.0). This new feature significantly facilitates the creation of such mapping lookups and allows the PI developer to allocate his/her time to other mapping requirements.

 

Defining JDBC Lookups Graphically

Prerequisites

  • The JDBC channel to be used for the lookup must be configured and activated in the Integration Directory.
  • The definition of the table used for the lookup must already be imported into the ES Repository as an external definition (see the SAP PI 7.1 Mapping Enhancements Series: Import SQL Table’s Metadata blog within this series by William Li for details).

Procedure

The first step in setting up a JDBC lookup graphically is to create a message mapping parameter within the Signature tab (for more information on parameters, see the related blog in this series SAP PI 7.1 Mapping Enhancements Series: Parameterized Message Mappings). The parameter should be an ‘Import’ parameter and have Category ‘Adapter’and Type ‘JDBC’.

image

Once the parameter is defined, you can proceed with defining the JDBC lookup in the target field mapping within the mapping definition. The JDBC Lookup standard function can be found among the functions in the Conversions category.

image

After the JDBC Lookup function is inserted into the data-flow editor of the mapping, bring up the properties for the function by selecting ‘Properties’ from the context menu or simply double-clicking on the function. This is where the SELECT statement can be defined graphically.

image

Within the properties:

  • Select the parameter to be used for the lookup. The parameter is maintained within the Signature tab as shown above. The name of the actual communication channel used for the lookup will be the value for this parameter.
  • Select the relevant external definition that corresponds to the table definition (see prerequisites). Note that, currently, the external definition must be located in the same SWCV as the mapping (of course, usage dependencies can be configured to allow for an external definition located within an external SWCV).
  • Move all fields to the left that should be used for reading a row in the database table (e.g. corresponds to the where clause in a SELECT statement. Source fields or results values from other functions must provide the input to these fields.
  • Move all fields to the right that should return a result from the SELECT statement. These results returned to these fields can then be processed further in the mapping.

And there you have it! The JDBC lookup setup is complete.

image

 

Defining RFC Lookups Graphically

Prerequisites

  • The RFC channel to be used for the lookup must be configured and activated in the Integration Directory.
  • The definition of the RFC structure used for the lookup must already be imported into the ES Repository as an imported archive.

Procedure

In procedure for setting up an RFC lookup graphically is very similar to the procedure for the JDBC lookup:

  • A message mapping parameter must be created within the Signature tab. It should be an ‘Import’ parameter and have Category ‘Adapter’and Type ‘RFC’.
  • image

  • Once the parameter is defined, you can proceed with defining the RFC lookup in the target field mapping within the mapping definition. The RFC Lookup standard function can be found among the functions in the Conversions category.
  • image

As one might suspect, the key differences reside in setting the properties for the RFC Lookup function itself.

    image

  • Select the parameter to be used for the lookup. The parameter is maintained within the Signature tab as shown above. The name of the actual communication channel used for the lookup will be the value for this parameter.
  • Select the relevant RFC function module that will be used for the lookup. Only RFCs already imported as an Imported Object will be listed.
  • The left column represents the RFC Request message. All fields to be used as input for the request should be selected (double-click). The fields selected for the request will be displayed in the bottom left box.
  • The right column represents the RFC Response message. All fields to be used capture the response and further applied in the mapping should be selected (double-click). The fields selected for the response will be displayed in the bottom right box.

Now the RFC lookup setup is complete.

image

Testing

Testing Locally

When testing a message mapping (or operation mappinp) that contains parameters locally, there is a new Parameters tab that must be completed in order to execute the test.

image

Testing at Runtime

Runtime execution of parameterized mappings requires values to be set at configuration time in the Integration Directory using an interface determination (see the related blog in this series SAP PI 7.1 Mapping Enhancements Series: Parameterized Message Mappings). For JDBC and RFC lookups, the actual channel to be used for the lookup must be configured in the interface determination.

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

  1. Prateek Raj Srivastava
    Would the earlier method be continued to be supported? I am asking this question to clarify whether there would be a performance difference in both the implementation procedures or not?

    Thanks for the info..

    Best Regards,
    Prateek

    (0) 
    1. Jin Shin Post author
      Hi Prateek –

      Yes, the ealier methods for doing the lookup continue to be supported.  Graphical support mainly simplifies the lookup setup at design time.  There should not be a runtime performance impact between the old and new lookup methods.

      Regards,
      Jin

      (0) 
  2. Geir Sunde
    Jin.
    Are there, or will there be, any facilities available for cashing the results of such a lookup.

    Giving you a sample: Say my mapping is processing 1000 (or 10000 or …) invoices containing a few number of different currencies. I need to do a lookup to the currency table for say a particular date not available in the invoice records to get a rate.

    What I would like to see then is not 1000 (or 10000 or …) executions of the lookup, but only a few (by cashing the already fetched values) or perhaps only 1 (by cashing the complete currency table (or a selected part of it)).

    Will there be any features available for this (like in some other tools), or do I have to take care of such performance issues manually my self?

    Geir Sunde

    (0) 
    1. Jin Shin Post author
      Hi Geir –

      There is nothing within the new graphical support features themselves for this, however, there are other means to accomplish this.

      If it’s a single result value you want to temporarily store or “cache”, perhaps you can use the graphical variable feature (see William Li’s blog within the series).  Otherwise, you’ll have the most flexibility in coding something for it within the “java areas” for the mapping – within the “Functions” tab.  There, perhaps you can use a java Map object or similar to store a number of values resulting from the lookup.  To make it more widely available (not just in your particular message mapping), you can do it in a function library.

      Regards,
      Jin

      (0) 
  3. Prateek Raj Srivastava
    Is it possible to use these graphical lookup while using AAE? In my opinion, there is no parameter option for mentioning the channel name while configuring “Receiver Interfaces” in Integrated Configurations.

    Thanks,
    Prateek

    (0) 
    1. Jin Shin Post author
      You are correct.  Ability to configure and use mapping parameters is one of the current limitations of using AAE (and Integrated Configuration object).  Target date for this functionality is currently unknown.  Definitely not Ehp1.

      Regards,
      Jin

      (0) 
  4. Leela Morampudi
    Hi…
          Thanks for ur blog. I ahve implemented File to RFC File scenario.In mapping, i am facing one problem. In mapping, i am not getting the two target feilds at a time. in RFC lookup, it is cuming. From RFC look up, i need to map the to two diffrent target feilds. But i can select only one target feild at a time. Could you please tell how you have done that? Please help me n this issue.
    (0) 
    1. Jin Shin Post author
      Hi –

      With the mapping for one target field open, you have to drag any other target fields you want to the data flow editor/mapping area.

      Regards,
      Jin

      (0) 
  5. Luis E. Ortiz
    HI,
    I have a question about JDBC Lookup , for each transaction (message) the channel call the DataBase ?. Wich is the better method? because I have a process a many message.

    thanks and regards

    (0) 
    1. Jin Shin Post author
      How many times the lookup gets triggered depends on how you design your mapping.  If the lookup is placed in a target field mapping where that target field mapping will be executed many times (e.g. within a 1 or 0..unbounded subtree), then many lookups will take place.  So you should try to design your mapping such that the fewest number of lookup calls are executed. For example, place the lookup call in the highest possible node of the target message and store retrieved data in the mapping GlobalContainer (for example) object using a user defined function so that the number of lookups made is greatly reduced. This will lead to a better performance.

      Regards,
      Jin

      (0) 
  6. Surender Chawla

    Hi Jin,<br/><br/>Thanks for the great blog, i have implemented this successfully after reading your blog. As it’s very much suitable to my project requirement. I am getting the below error: “<SAP:Category>Application</SAP:Category><SAP:Code area=”MAPPING”>RUNTIME_EXCEPTION</SAP:Code><SAP:P1>Thrown:<br/>java.lang.NullPointerException: while try</SAP:P1><SAP:P2>ing to invoke the method java.lang.String.length()</SAP:P2><SAP:P3> of an object loaded from local variable ‘guid'<br/>     </SAP:P3><SAP:P4>at com.sap.guid.GUID.parseHexGUID(GUID.java:1046)~</SAP:P4><SAP:AdditionalText/><SAP:Stack>Runtime exception occurred during mapping<br/></SAP:Stack><SAP:Retry>M</SAP:Retry></SAP:Error>” <br/>pls do the needful and suggest where I need to check. thanks in advance <br/><br/>-SS

    (0) 
    1. Jin Shin Post author
      The only thing related to a “GUID” for a mapping might be the Software Component Version (SWCV). Make sure the mapping works in local testing and also double check the mapping in relation to the SWCV. Maybe there are cache issues related to the mapping as well.  Maybe a mapping runtime cache refresh will help (PI Tools->Administration screen).

      Jin

      (0) 

Leave a Reply