Lua for column map expressions and procedures

A column map or column map procedure can contain either a complex expression (unstructured set of Lua statements) or a set of standard functions (structured set of Lua statements).

Complex expression

A complex expression is an unstructured block of statements. When a column map expression or procedure contains a complex expression, the expression is called for every row processed. A complex expression is functionally the same as a cm_transform() function, but a complex expression cannot contain a formal function statement.

In the following example, a complex expression replaces nil values in a column with 1 and leaves all other values unchanged.

srcvalue = optim.source.getcolumnvalue()
if srcvalue == nil then 
  optim.target.setcolumnvalue('1') 
else 
  optim.target.setcolumnvalue(srcvalue) 
end

Structured Lua expression

A structured Lua expression must contain the cm_transform() function. It also may contain any valid combination of other standard supported Lua functions. The standard Lua functions cannot call each other, but can call other user-defined functions. User-defined functions cannot call the standard Lua functions.

For an example of a structured Lua expression, see Sample structured Lua expression.

Standard Lua function names

Use the following function names in your Lua column map expressions and procedures. Processing calls each of these functions automatically at the point indicated.

Name	Description	Required
`cm_load()`	This function is called before any tables are processed.	No
`cm_unload()`	This function is called after all tables are processed.	No
`cm_starttable()`	This function is called at the start of processing for each table.	No
`cm_endtable()`	This function is called at the end of processing for each table.	No
`cm_transform()`	This function is called for every row processed.	Yes

Global functions

The following functions are available in all column map expression and procedure execution contexts.

Name	Description
`optim.print()`	Print messages to process report, up to a maximum of 1,000 lines.
`optim.date()`	Get current system date formatted in DB2^® ISO character format (different from os.date()).
`optim.time()`	Get current system time formatted in DB2 ISO character format (different from os.clock()).
`optim.timestamp()`	Get current timestamp formatted in DB2 ISO character format (different from os.date(“*t”)).
`optim.userid()`	Get current user’s TSOID or BATCH JOB owner.
`optim.sqlid()`	Get current user’s SQLID.
`optim.imskey()`	Get value of the concatenated IMS™ key for the record being processed, when available (a nil, otherwise).

Extract and archive file functions

Use the following functions to get information about source and target extract or archive files.

Name	Description
`optim.source.getdbalias()`	Get the source location/server name if a location has been defined while connecting to DB2. Alternatively, get the string `SUBSYS: ssss`, where `ssss` is the source local DB2 subsystem to which the Optim solution is connected.
`optim.source.getcreatorid()`	Get the creator ID for the source file.
`optim.target.getdbalias()`	Get the target location/server name if a location has been defined while connecting to DB2. Alternatively, get the string `SUBSYS: ssss`, where `ssss` is the target local DB2 subsystem to which the Optim solution is connected.
`optim.target.getcreatorid()`	Get the creator ID for the target file.

Data privacy provider function

The following function calls a data privacy provider.

Name	Description
`optim.mask(colname,parameter_string)`	For the named source column, call the data privacy provider specified in the `parameter_string`.

Note:

If you are using data type DECIMAL_370, you must specify PRECISION in the parameter_string.
For details and syntax for the data privacy providers, refer to Data privacy providers.

LOOKUP functions

Use optim.lookup to execute any of the native LOOKUP functions, such as LOOKUP, RAND_LOOKUP, and HASH_LOOKUP. optim.lookup can be used only within a complex expression or the cm_transform() function.

Name	Description
`lookup_object_name=optim.lookup(lookup_function_parameter_string)` `lookup_object_name=optim.lookup(lookup_object_name)`	A successful execution of optim.lookup () returns a named object that, for improved performance, can be used in subsequent calls. For example: `lookup_object_name=optim.lookup(lookup_object_name)`. Performance improvement is most likely when multiple `optim.lookup()` instances are used in a single column map procedure or Lua expression. The `lookup_function_parameter_string` includes the function name and parameters required for the specified lookup function, as it would be specified in a column map. For details and syntax, refer to Data Privacy functions

Name

Description

lookup_object_name=optim.lookup(lookup_function_parameter_string)

lookup_object_name=optim.lookup(lookup_object_name)

A successful execution of optim.lookup () returns a named object that, for improved performance, can be used in subsequent calls. For example: lookup_object_name=optim.lookup(lookup_object_name). Performance improvement is most likely when multiple optim.lookup() instances are used in a single column map procedure or Lua expression.

The lookup_function_parameter_string includes the function name and parameters required for the specified lookup function, as it would be specified in a column map. For details and syntax, refer to Data Privacy functions

Table functions

Use the following functions to get information about the source and target tables. Table functions can be used only within a complex expression or within the cm_starttable(), cm_endtable(), and cm_transform() functions.

Name	Description
`optim.source.gettablename()`	Get the name of the source table.
`optim.target.gettablename()`	Get the name of the target table.

Column functions

Use the following functions to get information about the source and target columns, transform column data, and write the result to the target column.

Name	Description
`optim.source.getcolumnvalue()` `optim.source.getcolumnvalue("COLNAME")` `optim.source.getcolumnvalue(n)`	Get a value from the current source column (), the named source column ("`COLNAME`"), or column (`n`) in the column map, where `n` is a number representing the `n`th column. Use this function for nonnumeric columns and for numeric columns that have data types other than the following data types: `BIGINT` `DECIMAL` `DOUBLE` `FLOAT` `DECIMAL FLOAT` `NUMBER` Use this function only with the `cm_transform()` function or in complex expressions.
`optim.source.getcolumnasdouble()` `optim.source.getcolumnasdouble("COLNAME")` `optim.source.getcolumnasdouble(n)`	Get a value in double-precision format from the current source column (), the named source column ("`COLNAME`"), or column (`n`) in the column map, where `n` is a number representing the `n`th column. Use this function to get data from columns that have the following data types: `BIGINT` `DECIMAL` `DOUBLE` `FLOAT` `DECIMAL FLOAT` `NUMBER` Use this function only with the `cm_transform()` function or in complex expressions.
`optim.source.getcolumnlength()` `optim.source.getcolumnlength("COLNAME")` `optim.source.getcolumnlength(n)`	Get the length of the current source column (), the named source column ("`COLNAME`"), or column (`n`) in the column map, where `n` is a number representing the `n`th column. Use this function in a complex expression or with the `cm_starttable()`, `cm_endtable()`, and `cm_transform()` functions.
`optim.source.getcolumnname()` `optim.source.getcolumnname(n)`	Get the name of the current source column (), or column `n` in the column map, where `n` is a number representing the `n`th column. Use this function in a complex expression or with the `cm_starttable()`, `cm_endtable()`, and `cm_transform()` functions.
`optim.source.getcolumntype()` `optim.source.getcolumntype("COLNAME")` `optim.source.getcolumntype(n)`	Get the DB2 data type of the current source column (), the named source column ("`COLNAME`"), or column (`n`) in the column map, where `n` is a number representing the `n`th column. Use this function in a complex expression or with the `cm_starttable()`, `cm_endtable()`, and `cm_transform()` functions.
`optim.source.getnumcolumns()`	Get the number of columns in the source table. Use this function in a complex expression or with the `cm_starttable()`, `cm_endtable()`, and `cm_transform()` functions.
`optim.source.setcolumnvalue(value)` `optim.source.setcolumnvalue("COLNAME",value)` `optim.source.setcolumnvalue("COLNAME1",value)`	Set the value of the current source column (`value`), the named source column or columns ("`COLNAME`", "`COLNAME1`", ...), or column (`n`) in the column map, where `n` is a number representing the `n`th column. This function requires you to specify a value, as either the only parameter, or the last parameter. Use this function only with the `cm_transform()` function or in complex expressions.
`optim.target.getcolumnvalue(value)` o`ptim.target.getcolumnvalue("COLNAME")` `optim.target.getcolumnvalue("COLNAME1","COLNAME2",value)` `optim.target.getcolumnvalue(n)`	Get the value of the current target column (`value`), the named target column or columns `COLNAME`", "`COLNAME1`", ...), or column (`n`) in the column map, where `n` is a number representing the `n`th column. Use this function for nonnumeric columns and for numeric columns that have data types other than the following: BIGINT DECIMAL DOUBLE FLOAT DECIMAL FLOAT NUMBER Use this function only with the `cm_transform()` function or in complex expressions.
`optim.target.getcolumnasdouble()optim.target.getcolumnasdouble("COLNAME")optim.target.getcolumnasdouble(n)`	Get a value in double-precision format from the current source column (), the named source column ("`COLNAME`"), or column (`n`) in the column map, where `n` is a number representing the `n`th column. Use this function to get data from columns that have the following data types: BIGINT DECIMAL DOUBLE FLOAT DECIMAL FLOAT NUMBER Use this function only with the `cm_transform()` function or in complex expressions.
`optim.target.setcolumnvalue(value)` `optim.target.setcolumnvalue("COLNAME",value)` `optim.target.setcolumnvalue("COLNAME1","COLNAME2",value)` `optim.target.setcolumnvalue(n, value)`	Set the value of the current target column (`value`), the named target column or columns ("`COLNAME`", "`COLNAME1`", ...), or column (`n`) in the column map, where `n` is a number representing the `n`th column. This function requires you to specify a value, as either the only parameter, or the last parameter. Use this function only with the `cm_transform()` function or in complex expressions.
`optim.target.getcolumnlength()` `optim.target.getcolumnlength("COLNAME")` `optim.target.getcolumnlength(n)`	Get the length of the current target column (), the named target column ("`COLNAME`"), or column (`n`) in the column map, where `n` is a number representing the `n`th column. Use this function in a complex expression or with the `cm_starttable()`, `cm_endtable()`, and `cm_transform()` functions.
`optim.target.getcolumnname()` `optim.target.getcolumnname(n)`	Get the name of the current target column (), or column `n` in the column map, where `n` is a number representing the `n`th column. Use this function in a complex expression or with the `cm_starttable()`, `cm_endtable()`, and `cm_transform()` functions.
`optim.target.getnumcolumns()`	Get the number of columns in the target table. Use this function in a complex expression or with the `cm_starttable()`, `cm_endtable()`, and `cm_transform()` functions.
`optim.target.getcolumntype()` `optim.target.getcolumntype("COLNAME")` `optim.target.getcolumntype(n)`	Get the data type of the current target column (), the named target column ("`COLNAME`"), or column (`n`) in the column map, where `n` is a number representing the `n`th column. Use this function in a complex expression or with the `cm_starttable()`, `cm_endtable()`, and `cm_transform()` functions.
`optim.target.iscolumnnullable()` `optim.target.iscolumnnullable("COLNAME")` `optim.target.iscolumnnullable(n)`	Determine whether the target column is nullable: the current target column (), the named target column ("`COLNAME`"), or column (`n`) in the column map, where `n` is a number representing the `n`th column. The function returns true if the column is nullable and returns false if the column is not nullable. Use this function in a complex expression or with the `cm_starttable()`, `cm_endtable()`, and `cm_transform()` functions.
`optim.rejectrow()`	Skip row and go to the next row. Use this function only with the `cm_transform()` function or in complex expressions.