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.
The JDBC Receiver Communication Channel for the corresponding Business System/Service should be created and the values should be assigned in the UDF Code.
Below is the corresponding User Defined Function Java Code for JDBCLookup object.
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.
Below is the corresponding User Defined Function Java Code for GetCounter function.
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.
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.
4 Comments
