If a mapping needs to use real time data from a database table, then this approach may offer a nice solution. It uses a combination of the standard RFClookup function in combination with a UDF for storing a hash table. The trick is to use a generic ABAP function module that reads data using the input in PI:

RFClookup and Java hashlist

 

To use database tables in a mapping without going through the hassle of getting database access or adding a new function module to the SAP system, one could use value mapping replication. However, this requires logic to insert and delete entries in the cache in order to make sure that the latest information is available from the database to the mapping.

This approach uses the standard RFClookup to fetch values from the database:

  • it is (very) fast in in terms of speed.
  • it requires one generic function module to be available on the ABAP stack.
  • it offers label value lookup using a java hash table.

The ABAP module has generates a generic query using the input parameters from PI. These are the table name, a list of key fields to be concatenated and result field. Optionally a filter (used as where clause) can be added as well. The ABAP function produces a list of labe-value pairs that are passed back to the PI system.

Typically the gsStoreLookup is conducted at the beginning of the mapping, hence, it is linked between the two headers of the source and taget message structure. Once the header mapping is done and the real message mapping begins, the values can be looked up from the hash list in the global container.

Retrieve lookup values

The user defined function to store lookups uses the global container to store the hashlist. It uses the whole queue as input for labe-value pairs and skips the context-change entries if there are any. Because you might have multiple lookups in the mapping, each lookup requires an unique ID (name).

public void gcStoreLookup(String[] label, String[] value, String[] id, String[]var1, ResultList result, Container container) throws StreamTransformationException{
  int i, j, k;
  String myID;
  Hashtable<String,String> ht = new Hashtable<String,String>(); 
    
  GlobalContainer gc=container.getGlobalContainer();
  AbstractTrace myTrace = gc.getTrace();

  myID = "lookup"  + id[0].trim();

  // check the length of input queus, k becomes minimum length
    i = label.length;
    j = value.length;
    if (i == j) {
      k = i;
    } // end of if
    else {
      if (i < j) {
        k = i;
      } // end of if
      else {
        k = j;
      } // end of if-else
    myTrace.addWarning("Lookuplist " + myID + "input length differ, taking length = " + Integer.toString(k) );
    } // end of if-else
  // fill the hash table

  for( i = 0; i < k ;i++ ) {
      if (label[i] == ResultList.CC) {
          if (value[i] != ResultList.CC)
               myTrace.addWarning("Lookuplist entry" + Integer.toString(i) + " is context change, but value = " + value[i] );
      }
      else
        ht.put(label[i], value[i]);
  } // end of for

  // store the hash table in the global container
  gc.setParameter( myID , (Object) ht);
  myTrace.addDebugMessage("Added lookuplist " + myID + "to global container" );

  // finally copy input to output to allow loop through
  for (i=0; i<var1.length;i++)
    result.addValue(var1[i]);

The lookup function to retrieve values from the hash list is simple. It can be a udf based on a single value, but just as well one can write an UDF for a context or queue. Here is the java code to retrieve a single value from the hash list:

public String gcGetLookupSingle(String label, String id, Container container) throws StreamTransformationException{
Hashtable<String,String> ht = new Hashtable<String,String>(); 
 
String value, myID;

myID = "lookup"  + id.trim();
GlobalContainer gc=container.getGlobalContainer();
AbstractTrace myTrace = gc.getTrace();

ht = (Hashtable) gc.getParameter( myID );

if (ht == null) {
  myTrace.addWarning("Cannot find lookuplist: " + myID);
  return ""; 
}
 value = ht.get( label);

if (value == null ) {
  myTrace.addWarning("Cannot find entry: " + label + " in lookuplist: " + myID);
  return "";
}
else
return value;
}