The Optim™ sample library, SFOPSAMP, includes the Lua sample files.
The following sample structured Lua expression illustrates the structure of a column map expression or procedure using the standard functions: cm_starttable, cm_endtable, and cm_transform. It can be found in the FOPLUAT sample file.
--Lua
------------------------------------------------------------------------
--
-- Purpose: Display metadata for tables, columns as well as
-- execution environment.
--
-- Name: FOPLUAT (OPTIM.TBLINFO)
--
-- Author: IBM Corporation
--
-- Revision: 1.0
--
-- Description: Captures and writes environment, table and column
-- metadata.
-- This data is written to the Optim process report
--
-- Input: Parameters and values needed to process request.
--
-- Arguments: None
--
-- Setup Parms: 'SOURCE' to limit output only to the source
-- metadata.
-- 'TARGET' to limit output only to the target
-- metadata.
-- 'ENVIRON' to limit output only to the environment
-- data.
-- 'ALL' to produce source, target and environment
-- data output.
-- 'NONE' which will not produce any environment output
--
-- Output: Entries within Optim Process report showing
-- specifics about the processing environment,
-- tables and columns.
--
-- Returns: 0 or error which ends the process with no changes
-- made.
--
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-- cm_starttable function - Called at the start of processing for each
-- table
-----------------------------------------------------------------------
function cm_starttable()
optim.print(" *** Start of Process ***")
-- Default scope is "ALL"
ScopeLimit = "ALL"
-- We have not built the message table yet
MSGTableBuilt = false
-- Table of valid scope values
local ScopeTable = {["SOURCE"]=1,["TARGET"]=2,["ENVIRON"]=3,
["ALL"]=4,["NONE"]=5}
if (ScopeTable[ScopeLimit] == nil) then
Process_Msg("MSG001", true)
elseif (ScopeLimit ~= "ALL") then
Process_Msg("MSG009", false, ScopeLimit)
else
Process_Msg("MSG010", false)
end
Process_Msg("MSG003", false, optim.timestamp())
if (ScopeLimit == "ENVIRON" or ScopeLimit == "ALL") then
-- Capture environment attributes and options (when they
-- become available)
Process_Msg("MSG011", false, "Current Time : ",
optim.timestamp())
Process_Msg("MSG011", false, "Active SQL ID : ",
optim.sqlid())
Process_Msg("MSG011", false, "Active User ID : ",
optim.userid())
Process_Msg("MSG011", false, "Source DB2 Subsystem : ",
optim.source.getdbalias())
Process_Msg("MSG011", false, "Target DB2 Subsystem : ",
optim.target.getdbalias())
Process_Msg("MSG011", false, "Optim Temp Directory : ",
optim.app.TempDir())
Process_Msg("MSG011", false, "Optim Data Directory : ",
optim.app.DataDir())
Process_Msg("MSG011", false, "Optim Script Name : ",
optim.app.Script())
Process_Msg("MSG011", false, "Optim CompanyName : ",
optim.app.CompanyName())
Process_Msg("MSG011", false, "Optim Release : ",
optim.app.OptimRelease())
Process_Msg("MSG011", false, "Optim Build : ",
optim.app.OptimBuild())
Process_Msg("MSG011", false, "Optim Error : ",
optim.app.Error())
Process_Msg("MSG011", false, "Instance : ",
optim.app.Instance())
Process_Msg("MSG011", false, "ThreadId : ",
optim.app.ThreadId())
Process_Msg("MSG011", false, "ThreadHandle : ",
optim.app.ThreadHandle())
Process_Msg("MSG011", false, "Operating Sys : ",
optim.app.Platform())
Process_Msg("MSG011", false, "Operating SysRel : ",
optim.app.OpSysRelease())
Process_Msg("MSG011", false, "Operating SysBuild : ",
optim.app.OpSysBuild())
Process_Msg("MSG011", false, "Operating ServPak : ",
optim.app.OpSysCSD())
Process_Msg("MSG011", false, "Server UserId : ",
optim.app.ServerUserId())
Process_Msg("MSG011", false, "Computer Name : ",
optim.app.ComputerName())
end
-- If requested get source table metadata
if (ScopeLimit == "SOURCE" or ScopeLimit == "ALL")then
--Identify source creator, table name
Process_Msg("MSG005", false, optim.source.getcreatorid(),
optim.source.gettablename())
--Identify Source columns
for i = 1, optim.source.getnumcolumns(), 1 do
local CurSrcColName =optim.source.getcolumnname(i)
Process_Msg("MSG007", false, i, CurSrcColName,
optim.source.getcolumntype(CurSrcColName),
optim.source.getcolumnlength(CurSrcColName))
end
end
-- If requested get target table metadata
if (ScopeLimit == "TARGET" or ScopeLimit == "ALL")then
--Identify target creator, table name
Process_Msg("MSG006", false, optim.target.getcreatorid(),
optim.target.gettablename())
--Identify target columns
for i = 1, optim.target.getnumcolumns(), 1 do
local CurTgtColName =optim.source.getcolumnname(i)
if (optim.target.iscolumnnullable(CurTgtColName)) then
NullAble = "YES"
else
NullAble = "NO"
end
Process_Msg("MSG008", false, i, CurTgtColName,
optim.target.getcolumntype(CurTgtColName),
optim.target.getcolumnlength(CurTgtColName),
NullAble)
end
end
end
------------------------------------------------------------------------
-- cm_transform function - Called for each row processed
------------------------------------------------------------------------
function cm_transform()
optim.target.setcolumnvalue(optim.source.getcolumnvalue())
end
------------------------------------------------------------------------
-- cm_endtable function - Called at the end of processing for each
-- table
------------------------------------------------------------------------
function cm_endtable()
Process_Msg("MSG004", false, optim.timestamp())
end
------------------------------------------------------------------------
-- Process_Msg function - Called when a message is requested to be
-- issued.
-- The message is written to the Optim process report.
-- 1st argument is the message identifier indicating what message to
-- issue.
-- 2nd argument is the boolean value 'TRUE' or 'FALSE' to indicate if
-- the process should be aborted.
-- 3rd through nth arguments define any substitution values to be used
-- within the message text that require a value.
-- Example: Process_Msg("MSG001",true,"Optim","Distributed",11)
-- requests that message "MSG001" is searched for in MsgTable and
-- issued with the values "Optim", "Distributed" and 11 being
-- substituted for the '&S' literals respectively and the process is
-- to be aborted.
------------------------------------------------------------------------
function Process_Msg(...)
local IntEMsg
-- If not done already, build the message table
if (MSGTableBuilt == false) then
MSGTableBuilt = true
-- MsgTable contains the text of the messages, values '&S' will be
-- replaced with a value passed by the caller
MsgTable = {
["MSG001"] =
"Scope Value Not ENVIRON, SOURCE, TARGET, ALL or NONE",
["MSG002"] = "Only [SOURCE|TARGET|ENVIRON|ALL] Allowed",
["MSG003"] =
"*** Start of Table Processing &S ***",
["MSG004"] =
"*** End of Table Processing &S ***",
["MSG005"] =
"* Source Table: &S.&S *",
["MSG006"] =
"* Destination Table: &S.&S *",
["MSG007"] =
"Source Column &S: &S &S(&S)",
["MSG008"] =
"Destination Column &S: &S &S(&S), NULLABLE(&S)",
["MSG009"] =
"Scope Limited to: &S",
["MSG010"] =
"Scope: Unlimited",
["MSG011"] =
"&S &S"
}
end
-- If called without minimum arguments, syntax error
if (select("#",...) < 2) then
IntEMsg = " Minimum 'Process_Msg' Syntax is "..
"'Process_Msg(MSGID,true|false)'\n"
WriteRec(IntEmsg)
-- If the requested message is not in the table, inform caller
-- and return
elseif (MsgTable[select(1,...)] == nil) then
IntEMsg = " Requested Message, " .. select(1,...) ..
" Not Found In Message Table\n"
WriteRec(IntEmsg)
-- Issue the message using the text from the table and any
-- substitution values supplied
else
if (select("#",...) == 2) then -- No substitutions
WriteRec(" " .. MsgTable[select(1,...)] .. "\n")
else -- Some substitutions
-- Run through argument list to replace '&S' entries with
-- requested value(s) passed
-- Copy message text from the table
local Msg = MsgTable[select(1,...)]
for i = 1, select("#",...) - 2, 1 do
-- Replace '&S' with passed argument
Msg = string.gsub(Msg, "&S", select(i+2,...), 1)
end
-- Issue message with substitutions passed to this function
WriteRec(" " .. Msg .. "\n")
end
-- ABORT process after issuing msg?
if (select(2,...) == true) then
error(" *** Aborting Process as Requested by User ***\n",2)
end
end
end
------------------------------------------------------------------------
-- WriteRec function - Called to write output to the process report
------------------------------------------------------------------------
function WriteRec(Record)
optim.print(Record)
end
Sample PROP and Lua Use Case
Optim provides the ability to specify the name of a Lua Column Map Procedure as an argument to the PROP function. The PROP function will then propagate the modified target primary key or foreign key to all related tables. Therefore, consider the following use case.
Use Case Description
The user has extracted all rows from the demo FOPDEMO.OPTIM_SALES table and all related rows from the child FOPDEMO.OPTIM_CUSTOMERS table. The user would like to mask the primary key SALESMAN_ID of the OPTIM_SALES table by using the Affinity Data Privacy Provider.
The user would also like to propagate the masked SALESMAN_ID primary key column of the OPTIM_SALES table to the SALESMAN_ID foreign key column of the OPTIM_CUSTOMERS table.
Below is a sample Optim Column Map, using the available FOPLUAA Lua sample that can be used to achieve this use case.
Figure: Lua Use Case
------------------ Define Internal Column Map ----------------------------
Command ===> Scroll ===> PAGE
Corresponding Columns MUST Have Compatible Data Types
Use LIST UNUSED Command for List of Unused Source Columns
Use LIST ALL Command for List of All Source Columns VAL ON
MOVE
1 OF 11
-------------------FOPDEMO.OPTIM_SALES------------FOPDEMO.OPTIM_SALES--------
Cmd Source Column Data Type Num Destination Column Data Type Status
--- ------------------->> ---------- --- ------------------ ---------- -------
*** *********************************** TOP ***********************************
___ PROP(PROC S.FOPLUAA) 1 SALESMAN_ID CH(6) Lua
___ FIRST_NAME VCH(15) 2 FIRST_NAME VCH(15) EQUAL
___ LAST_NAME VCH(15) 3 LAST_NAME VCH(15) EQUAL
___ NATIONALITY VCH(30) 4 NATIONALITY VCH(30) EQUAL
___ NATIONAL_ID VCH(30) 5 NATIONAL_ID VCH(30) EQUAL
___ PHONE_NUMBER VCH(20) 6 PHONE_NUMBER VCH(20) EQUAL
___ AGE SMALLINT 7 AGE SMALLINT EQUAL
___ SEX CH(1) 8 SEX CH(1) EQUAL
___ TERRITORY VCH(14) 9 TERRITORY VCH(14) EQUAL
___ EMAIL_ADDRESS VCH(70) 10 EMAIL_ADDRESS VCH(70) EQUAL
___ MANAGER_ID CH(6) 11 MANAGER_ID CH(6) EQUAL
*** ********************************* BOTTOM *********************************
The user would also like to take the value of the propagated SALESMAN_ID foreign key column of the OPTIM_CUSTOMERS table, and prepend it to the EMAIL_ADDRESS column in the format salesman_id@domain. This can be achieved by applying sample Lua script FOPLUAP that uses function optim.target.getcolumnvalue() to obtain the value of the already propagated SALEMAN_ID column.
Below is a sample Optim Column Map, using the available FOPLUAP Lua sample that can be used to achieve this use case.
Figure: Lua Use Case
------------------ Define Internal Column Map ----------------------------
Command ===> Scroll ===> PAGE
Corresponding Columns MUST Have Compatible Data Types
Use LIST UNUSED Command for List of Unused Source Columns
Use LIST ALL Command for List of All Source Columns VAL ON
MOVE
1 OF 23
--------------FOPDEMO.OPTIM_CUSTOMERS------------FOPDEMO.OPTIM_CUSTOMERS--------
Cmd Source Column Data Type Num Destination Column Data Type Status
--- ------------------->> ---------- --- ------------------ ---------- -------
*** *********************************** TOP ***********************************
___ CUST_ID CH(5) 1 CUST_ID CH(5) EQUAL
___ CUSTNAME CH(60) 2 CUSTNAME CH(60) EQUAL
___ ADDRESS1 VCH(100) 3 ADDRESS1 VCH(100) EQUAL
___ ADDRESS2 VCH(100) 4 ADDRESS2 VCH(100) EQUAL
___ LOCALITY VCH(56) 5 LOCALITY VCH(56) EQUAL
___ CITY VCH(60) 6 CITY VCH(60) EQUAL
___ STATE VCH(30) 7 STATE VCH(30) EQUAL
___ COUNTRY_CODE CH(2) 8 COUNTRY_CODE CH(2) EQUAL
___ POSTAL_CODE VCH(15) 9 POSTAL_CODE VCH(15) EQUAL
___ POSTAL_CODE_PLUS4 CH(4) 10 POSTAL_CODE_PLUS4 CH(4) EQUAL
___ PROC S.FOPLUAP 11 EMAIL_ADDRESS VCH(70) Lua
___ PHONE_NUMBER VCH(20) 12 PHONE_NUMBER VCH(20) EQUAL
___ YTD_SALES DEC(7,2) 13 YTD_SALES DEC(7,2) EQUAL
___ SALESMAN_ID CH(6) 14 SALESMAN_ID CH(6) EQUAL
___ NATIONALITY VCH(30) 15 NATIONALITY VCH(30) EQUAL
___ NATIONAL_ID VCH(30) 16 NATIONAL_ID VCH(30) EQUAL
___ CREDITCARD_NUMBER VCH(19) 17 CREDITCARD_NUMBER VCH(19) EQUAL
___ CREDITCARD_TYPE VCH(30) 18 CREDITCARD_TYPE VCH(30) EQUAL
___ CREDITCARD_EXP CH(4) 19 CREDITCARD_EXP CH(4) EQUAL
___ CREDITCARD_CVV VCH(4) 20 CREDITCARD_CVV VCH(4) EQUAL
___ DRIVER_LICENSE VCH(30) 21 DRIVER_LICENSE VCH(30) EQUAL
___ CREDITCARD_HISTORY CL(10K) 22 CREDITCARD_HISTORY CL(10K) EQUAL
___ CCH_ROWID ROWID 23 CCH_ROWID ROWID ROWID_A
*** ********************************* BOTTOM **********************************