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