Skip to Content

        In this article, I would like to give you the clear picture of how to optimize the JDBC Lookup effectively, when we need to insert the multiple records or single record from the legacy system to Oracle database table via SAP PI message mapping at the same time by using customized UDF of JDBC Lookup that uses Global Container object.

 

Business Requirement:

        In one of my interface ABAP Proxy to JDBC, there is a requirement to insert either multiple records or single records from the legacies to the Oracle database table via SAP PI, where the primary key value for the corresponding database target table should be inserted manually by using auto-incremented primary key values. If the table has no entries, we can use counter function to assign the sequential value. But the table has already thousands of entries and hence we need to go for alternate approach by using JDBC Lookup Concept. In this case, Each and Every time, we need to fetch the maximum value of primary key for the corresponding table in the Oracle database by calling JDBC Lookup, and increment the value and assign the same value in the primary key value of the corresponding database table for each and every record. This approach will not be a good solution by calling JDBC Lookup for each and every record, since it affects the mapping performance heavily.

 

Solution:

        For inserting multiple records in the Oracle data base table via SAP PI, we can call JDBC Lookup only one time and fetch the latest value stored in the primary key of the corresponding table and store the same in the Global Container Object. Whenever the first record is getting to be inserted, the value in the Global Container Object is incremented by one and the value is assigned to the table’s primary key field of the same row and again the same value is stored in the same Global container object for re-using purpose for next set of values to be inserted in the same table.

        In SAP PI 7.1, we can use Graphical JDBC Lookup in the corresponding mapping object in order to avoid UDF Code for simplified manner. But in our case, we are using customized JDBC Lookup to store the value in Global Container object for re-using purpose.

 

JDBC Lookup Mapping

 

        The JDBC Receiver Communication Channel for the corresponding Business System/Service should be created and the values should be assigned in the UDF Code.

 

Receiver JDBC Communication Channel

 

        Below is the corresponding User Defined Function Java Code for JDBCLookup object.

 

JDBC Lookup UDF Code

 

        GetCounter UDF is used to fetch the value from the Global Container Object Counter and increment the value and again assign it to the same Global Container Object Counter.

 

 getCounter Mapping

 

        Below is the corresponding User Defined Function Java Code for GetCounter function.

 

getCounter UDF Code

 

Result:

        In this case, I am trying to insert 3 records in the oracle table at the same time via SAP PI Graphical Mapping. This initially fetch the latest value of primary key as 1000003 and store the value in Global Container object as Counter and increment the value by 1 for each records and assign it to the target field as below. The Last value is stored as 1000006 for the primary key value since it has 3 records.

 

Final Result after JDBC Lookup

 

        JDBC Lookup is optimized by calling only once when the mapping is executed for inserting multiple records into the table and hence the mapping performance is increased.

 

Conclusion:

 

        I believe that this blog would provide better understanding of the optimization of the JDBC Lookup by calling only once for inserting multiple records in the oracle database table at the same time when the mapping is executed.

To report this post you need to login first.

3 Comments

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

  1. Srinivasa Kapu
    Nice blog. I have a scenario Proxy-File. For every plant and material combination we need to fetch around 5 fields from Legacy. Whenever we have more than 10,000 records from SAP for each plant and material combination we see the lookup for 10,000 times in the JDBC receiver CC and to process these many recors it is taking around 45 min. Does this method allow to retreive all the records in a single call? any help or advice would be appreciated.
    (0) 
    1. Gabriel Sagaya Selvam Panneer Selvam Post author
      Hi Srini, You can use my logic for RFC Lookup Optimization only if the fetching records from legacy should be in any sequential order!!. Here You need to have 5 different Global container variables like count1..count5 in RFCLookup(JDBCLookup) Method and the same variable should be used in GetCounter Method for the corresponding sequential order for retrieval.
      Thanks
      Gabriel
      (0) 

Leave a Reply