Column map procedure example: Switched lookup

The column map procedure example illustrates how the mask_parms function masks a column by using data from a lookup table.

-------------------------------------------------------------------------------
--
--   IBM Optim sample column map procedure
--
--   Name:          OptimSwitchedLookup
--
--   Revision:      1.0
--
--   Description:   Masks a column by using table lookup.  The lookup table to
--                  be used is determined by the value of another column.
--
--   Input:         Parameter 1 (Required):
--                  A string that indicates the type of lookup to use:
--                  HASH, RANDOM, or LOOKUP
--
--                  Parameter 2 .... n-1 (Required)
--                  An expression that indicates the lookup table to use. The format is
--                  COND(column-name=value) DATASOURCE(datasource_parameters)
--                  This parameter can be repeated multiple times.  If a row does not
--                  satisty any of the COND parameters than it will not be inserted
--                  into the target table.
--
--                  Parameter n (Optional):
--                  A string containing additional parameters to be
--                  copied into the optim.mask invocation.
--                  This is in addition to the datasource_parameters value in
--                  the COND clause of Parameter 2 and the mask_parms_constant field 
--                  that is declared at the start of this script.
--
--
--
--   Output:       - The masked column data as set by the optim.target.setcolumnvalue function
--                 - Text directed to the Optim report by the optim.print function
--
--   Return Codes: 0 - Successful execution
--                 1 - Reject row (Use in cm_transform; row will not be inserted to 
--                     destination table)
--                 2 - Abort process
--
--                 Two helper functions are specified to specify conditions other than 
--                 return code 0.
--                 There is no need to code a return statement when using these 
--                 functions.
--                 error(string) - This call causes the Optim process to abort
--                                 and string is shown in the Optim report
--                                 as an error message.
--                 optim.rejectrow() - This call causes Optim to reject the row
--                                     currently being processed. The row is
--                                     not inserted into the destination table.
--
--
--
-------------------------------------------------------------------------------

function cm_transform()

    -- Change this field to contain parameters that should
    -- be placed into all optim.mask calls
    local mask_parms_constant = 'CACHE=Y,WHENNF=PRE'

    --
    -- Validate number of parameters:
    --
    nparm = optim.parms.num()
    if (nparm  < 2) then
       process_error("Call to column map procedure OptimSwitchedLookup must have 2 or more parameters")
    end

    --
    -- Process Parameter 1 (lookup type)
    parm = optim.parms.get(0)
    if (string.lower(parm) == "hash") then
      provalue = "HASH_LOOKUP"
    elseif (string.lower(parm) == "random") then
      provalue = "RANDOM_LOOKUP"
    elseif (string.lower(parm) == "lookup") then
      provalue = "LOOKUP"
    else
      process_error("Invalid parameter.  Expected HASH, RANDOM or LOOKUP.  Found " .. parm)
    end


    --
    -- Process COND/DATASOURCE parameters
    --
    gotcond = false
    for parmptr = 1, nparm-1 do
      parm = optim.parms.get(parmptr)
      if (string.lower(string.sub(parm, 1, 5)) == "cond(") then
        gotcond = true
        datasource_parameters = process_cond()
        if (datasource_parameters > "") then
          break;
        end
      end
    end
    if (not gotcond) then
      process_error("No COND parameter found")
    end

    -- No COND matched this row, so reject the row
    if (datasource_parameters <= "") then
      optim.rejectrow()
    end

    --
    -- Process optional additional optimmask parameter
    --
    lastparm = optim.parms.get(nparm-1)
    if (string.lower(string.sub(lastparm, 1, 5)) == "cond(") then
       optimmask_additional_parms = ""
    else
       optimmask_additional_parms = lastparm
    end

    --
    -- Construct call to optim.mask, make the call,
    -- and place new value into target column
    --
    mask_parms = "PRO=" .. provalue .. "," .. mask_parms_constant

    -- This use of environment variables to store userid and password
    -- for the system table is for simple illustrative purposes only.
    -- For greater security, store this information in environment variables
    -- in an encrypted format.
    userid = os.getenv("optimmaskuserid")
    if (userid) then
      mask_parms = mask_parms .. ",USER=" .. userid
    end
    password = os.getenv("optimmaskpswd")
    if (password) then
      mask_parms = mask_parms .. ",PASS=" .. password
    end

    mask_parms = mask_parms .. "," .. optimmask_additional_parms

    oldvalue = optim.source.getcolumnvalue()
        newvalue = optim.mask(oldvalue, mask_parms)
        optim.target.setcolumnvalue(newvalue)
end



function process_cond()

  strptr = 6     -- Point to first character after "COND("

  -- Get the column name
  equalsign = string.find(parm, "=", strptr, true)
  if (not equalsign) then
    process_error("Syntax error around character " .. strptr .. " in expression: " .. parm)
  end
  colname = string.sub(parm, strptr, equalsign-1)

  -- Get the column value
  strptr = equalsign + 1
  closeparen = string.find(parm, ")", strptr, true)
  if (not closeparen) then
    process_error("COND expression is missing closing parenthesis in expression: " .. parm)
  end
  colvalue = string.sub(parm, strptr, closeparen-1)

  --Debug - Print scan results:
  --optim.print ("Found colname=" .. colname .. " in parm: " .. parm)
  --optim.print ("Found colvalue=" .. colvalue .. " in parm: " .. parm)

  -- If COND(column-name=value) condition not met for this row, then
  -- do no further processing on this parameter.  Return "" to indicate no hit.
  if (optim.source.getcolumnvalue(colname) ~= colvalue) then
    return ""
  end

  -- Got a match on COND, so get the DATASOURCE value
  strptr = closeparen + 1
  datasourceValuePtr = string.find(parm, "DATASOURCE(", strptr, true)
  if (not datasourceValuePtr) then
    process_error("DATASOURCE clause not found in expression: " .. parm)
  end
  -- (Note: This scanning is very simple; it assumes that there
  -- is no errant text between the COND and DATASOURCE clauses)
  closeparen = string.find(parm, ")", datasourceValuePtr, true)
  if (not closeparen) then
    process_error("COND expression is missing closing parenthesis in expression: " .. parm)
  end
  return string.sub(parm, datasourceValuePtr+11, closeparen-1)

end
function process_error(msg)
  errprefix = "Error in column map procedure for column " .. optim.source.getcolumnname()
  errprefix = errprefix .. " in table " .. optim.source.getdbalias() .. "." .. optim.source.getcreatorid() .. ".
    " .. optim.source.gettablename()
  error(errprefix .. ":\n   " .. msg)
end 


Feedback