Lookup Hashmap

The traditional way to process an entry is to intake from the iterator, then use lookup connectors or for each connectors to get additional data.

Data Flow

But that lookup can be slow, sometimes exceedingly slow, even when properly indexed. An alternative is to make an in-memory hashmap of that lookup if your data model supports it.

Create this hashmap by changing your lookup connector to a passive iterator and execute your map in the initialization phase.

  Other_Database (Passive)
    After Initialize (create hashmap here)
Data Flow   

Open up the 'After Initialize' section and make a hashmap. 

var sqlStatement = "select SOMETHING from SOME.TABLE where CHECKIN < GETDATE();
var connResult = thisConnector.connector.execSQLSelect(sqlStatement);
if (connResult != "") {
task.logmsg("ERROR","Unable to connect to DB");
system.abortAssemblyLine("Unable to continue");
else { 
// Let's store this in a hashmap for quick retrieveal. Values will be an array so we can handle
// multiple values. 
// We'll start with educated guess of 9000 so we don't invoke a resize right off the bat
var campusMap = new java.util.HashMap(9000)
var key = "";
var value = "";   
var values = [];
var entry = thisConnector.connector.getNextSQLSelectEntry();
while (entry != null) {
key = entry.getAttribute("STUDENTNUMBER").getValue();
value = entry.getAttribute("HALLNAME").getValue();
// Get any existing array there might be and push if there is one
values = campusMap.get(key)
if (values)
values = [value];
// Store the new array of values
entry = thisConnector.connector.getNextSQLSelectEntry()
task.logmsg("INFO","List size "  + campusMap.size());

Now, rather than make a SQL query you can simply put in a snippit like this in the - Note the .setValues method that takes an array as input

var key = work.MD_SOURCE_EMPLID_PS.getValue();
var campus = campusMap.get(key);

if (campus != null ) 
Don't forget you may need to flatten your multi-valued Attributes. If you do this in your output map a stray a ret.value = null will disable the update, so it may be best to do it after the snippit above

var attribute = work.MD_CAMPUS_ENR;

var values = attribute.getValues()

// If there are any values, 
if (values.length > 0 ) {
var flatValues= "";
for (val in values) {
if (val != null && val.trim() != "") {
flatValues += val + ";";
if (flatValues != "") 
flatValues = flatValues.substring(0,flatValues.length -1);


A more elegant solution would be to output the attribute using a built-in parser