Lua for Column Map expressions and procedures

Edit online

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 (CM) 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 can 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 CM expressions and procedures. Optim 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 CM 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).
`optim.setgcinterval()`	Defines how frequently Optim will invoke internal Lua garbage (memory) collection function expressed in the number of rows processed between garbage collection invocations. Accepts one numeric parameter with values from 1 to 2147483647. Setting to 1 will result in the lowest memory usage with increased CPU time. The default in 4096. Note: The frequency will be set globally for all CM procedures (Lua chunks) defined for a given CM.

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.

Optim Data Privacy Provider (ODPP) invocation

The following function may be used to invoke an Optim Data Privacy Provider (ODPP) from an Optim Column Map Procedure (Lua chunk).

Name Description

optim.mask(varname1,varname2,...,parameter_string) For the named variable values, call the data privacy provider specified in the parameter_string. While the variable values may reflect directly the values of (source or destination) columns from the row being processed, there may also represent values computed, or derived from, the values of (source or destination) columns, within the user's Lua chunk. Multiple values can be returned. For detailed information on using masks, see Using optim.mask().

Name	Description
`optim.mask(varname1,varname2,...,parameter_string)`	For the named variable values, call the data privacy provider specified in the `parameter_string`. While the variable values may reflect directly the values of (source or destination) columns from the row being processed, there may also represent values computed, or derived from, the values of (source or destination) columns, within the user's Lua chunk. Multiple values can be returned. For detailed information on using masks, see Using optim.mask().

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 Optim Data Privacy Providers.

Using optim.mask()

A number of Data Privacy Providers are available to use with the optim.mask function. For the list of the supported ODPP providers and the detailed syntax of the request string, see: Optim Data Privacy Providers.

The optim.mask() invocation may use one, or more, input parameters and it may return one, or more, values.

The last parameter will always be the Provider's request string which will include definition (FLDDEFs) for all input and output value, and any details necessary to define the options for your request. The parameters prior to that will be one or more Lua variables that contain the input that the Provider expects. The input variable specified will be the value that is actually used in the Provider and not the name specified on the FLDDEFn parameter. The name on the FLDDEF will be associated with other possible parameters used by the request such as SEARCH, REPLACE, PRExxx , FLDDEFn (for the "old/original" request syntax) or SOURCE, DEST, PRExxx and FLDDEFn (for the "new/enhanced" request syntax).

For more details see Optim Data Privacy Providers.

Note: The order of the Lua variables specified on the optim.mask() invocation is important and it should reflect the logical order of the SEARCH, REPLACE, PRExxx , FLDDEFn (for the "old/original" requestsyntax) or SOURCE, DEST, PRExxx and FLDDEFn (for the "new/enhanced" request syntax) parameters of the request string.

Lookup providers (simple lookup, hash lookup, random lookup) are available for use with optim.mask() in Column Map(CM) Procedures, even though such Providers are not available for direct use in a CM. There are additional considerations when using optim.mask() when invoking the lookup providers. There may be multiple input and output variables, so care must be taken to specify them correctly and in the right logical order. The setting for lib= parameter should reflect that the Data Privacy function will use the SQL interface to access the LOOKUP table in Db2 on z/OS, so it is necessary that the packages for ODPP are bound and included with the Optim Db2 plan. Specify lib=DB2ZOSSQL to invoke that interface. User=, pass=, and conn= parameters are ignored and not necessary but need to follow the lib= parameter if specified. In addition, no special characters are allowed as values of the user=, pass=, and conn= parameters.

When a CM procedure invokes the optim.mask() function using the LOOKUP Provider, and does not use the WHENNOTFOUND=PRESERVE keyword to control the function, or any Data Privacy Provider where a row error is returned and does not use the Lua “protective call” pcall() function, an error is being raised by optim.mask, the destination values have undefined values and the row is being discarded. This is expected behavior. The user has no ability to control the outcome in such a scenario. However, the user can change their CM procedure Lua chunk to utilize the Lua “protected call” pcall() function.

If running Lookup Provider with a optim.mask() call and it is defined in a pcall() environment, the user will be able to detect and handle the scenarios when using parameter WHENNOTFOUND=ERROR (which is the default), and the LOOKUP value(s) have not been found in the LOOKUP table, or any returned row error from other Providers as well.

The user needs to change their CM PROCEDURE Lua chunks to utilize the Lua “protected call” pcall() function surrounding the optim.mask() invocation, for example:

rcstatus, out_firstn, out_lastn = pcall(optim.mask,myempno,myempnum,myfname,mylname,mask_to_use)

The user should evaluate the rcstatus value:

When TRUE, optim.mask LOOKUP processing was successful and the destination variables have values assigned.

When FALSE, the optim.mask LOOKUP processing has encountered an error and the destination values have no meaningful values assigned. An “error” table will be placed on the Lua stack with detailed information about the error. The following values will be provided:

error.code: the general error code of PST_CM_EXIT_REJECT_ROW. This will force the row to be discarded when optim.mask() has not been protected by pcall().

error.reason: the actual code passed from the Provider. Could be 6030 for “Lookup data not found”, as an example.

error.message: the error message pulled by optim.mask for the error returned from the Provider. For example: “IOQDP0439E: Lookup data not found”.

The user should decide how to proceed. The user can choose to assign values to the destination column(s) using some defaults or the source values. When a value has been assigned to the destination column, on which the CM PROCEDURE has been defined, Optim will assume that the user detected the Privacy Provider error (LOOKUP as example) and handled it to their satisfaction. The row will be processed successfully and not dropped. If the user does not assign a value to the destination column, Optim will assume that the row should be dropped because of LOOKUP (or other Provider) error.

See an example of these capabilities in samplib member FOPLUAE.

With version Optim 11.7, LOOKUP has been enhanced to allow lookup table that have a different CCSID than source data. See examples in samplib, FOPLUAJ and FOPLUAK.

Native 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

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 CM 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 CM. For details and syntax, refer to Data Privacy functions

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 CM 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 CM. For details and syntax, refer to Data Privacy functions

Note: Restriction on using backslash escape character and Japanese character sets and any character sets which might be incompatible with EBCDIC CCSID 1047/037, and compatible CCSIDs, in representation of the backslash character or any other special characters within the LOOKUP request:Lua chunks attached to Column Map procedures or expressions might contain certain special characters, or Lua operators, which have different (hexadecimal) code point values depending on the CCSID of the terminal session used to enter the Lua chunk. The Lua compiler embedded in Optim has been configured to interpret correctly Lua chunks entered using EBCDIC CCSID 1047/037 and compatible CCSIDs. Compiler errors might be reported when attempting to process Lua chunks entered using different CCSIDs. See Column map procedures.

Note: When escape characters are being used within a HASH_LOOKUP request in a Column Map Procedure's Lua chunk, such escape characters need to be represented by \\u or \\r values because \u and \r are not valid escape sequences in Lua.

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.

Legacy note: Legacy data might or might not be translated depending on Optim site options and Legacy Table specifications (see discussion in Legacy Data Translation). Depending on these settings, the character and graphic Legacy data might be treated as FOR-BIT-DATA or as encoded in the Optim plan CCSID. This will affect the behavior of the Lua getcolumnvalue() and setcolumnvalue() functions.

Name	Description
optim.source.getcolumnvalue() optim.source.getcolumnvalue("`COLNAME`") optim.source.getcolumnvalue("COLNAME1", "COLNAME2",value) optim.source.getcolumnvalue(`n`)	Get a value from the current source column (), the named source column ("`COLNAME`", "`COLNAME1`", ...), or column (`n`) in the CM, 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: `DECIMAL` `DOUBLE` `FLOAT` `DECIMAL FLOAT` `NUMBER` Note: This function returns the column value as a 64bit INTEGER for column with integer type data types. Note: For Db2 CCSID-sensitive columns, this function returns the data in the CCSID of the Optim plan. Data from Legacy Tables can be returned in the CCSID of the plan or “as-is”, without any conversions, depending on the Legacy site options and Legacy Table settings. See also ‘Legacy note’ above. Use this function only with the `cm_transform()` function or in complex expressions.
optim.source.getcolumnasbytes() optim.source.getcolumnasbytes("`COLNAME`") optim.source.getcolumnasbytes("COLNAME1", "COLNAME2",value) optim.source.getcolumnasbytes(`n`)	Get a value from the current source column (), the named source column ("`COLNAME`", "`COLNAME1`", ...), or column (`n`) in the CM, where `n` is a number representing the `n`th column. Use this function for any type of data, including numeric and character type data, to obtain its native representation as a string of bytes. For CCSID-sensitive data there will be no conversion performed. 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 CM, 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, in CHARACTERS for the string data types and BYTES for other data types, of the current source column (), the named source column ("`COLNAME`"), or column (`n`) in the CM, 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.getcolumnlengthbytes() optim.source.getcolumnlengthbytes("`COLNAME`") optim.source.getcolumnlengthbytes(`n`)	Get the length, in BYTES for all data types, of the current source column (), the named source column (`"COLNAME"`), or column (`n`) in the CM, 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 CM, 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 CM, 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.getcolumnencoding() optim.source.getcolumnencoding("`COLNAME`") optim.source.getcolumnencoding(`n`)	Get the data encoding for the specified source column. The value returned is in the form of a Lua string: “A”: ASCII encoding, “E”: EBCDIC encoding, “U”: Unicode encoding, “ ”: None of the above, data is not character-type.
optim.source.getcolumnccsid() optim.source.getcolumnccsid("`COLNAME`") optim.source.getcolumnccsid(`n`)	Get the data CCSID for the specified source column. The value returned is in the form of a Lua number representing the CCSID of the data, or 0 for data without a CCSID assigned.
`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 CM, 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. Note: For Db2 CCSID-sensitive source columns, the value provided should be in the CCSID of the Optim plan and conversion will be performed to the column’s CCSID as required. Legacy Table data will be converted or assigned "as-is", without any conversions, depending on the Legacy site options and Legacy Table settings. See also ‘Legacy note’ above. Use this function only with the `cm_transform()` function or in complex expressions.
optim.source.setcolumnfrombytes(`value`) optim.source.setcolumnfrombytes("`COLNAME`", `value`) optim.source.setcolumnfrombytes("`COLNAME1`", `value`)	Set the value of the current source column (`value`), the named source column or columns ("`COLNAME`", "`COLNAME1`", ...), or column (`n`) in the CM, 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. Note: The value provided to this function should represent the data in its native representation as a string of bytes. There will be no data conversions of any kind. Use this function only with the `cm_transform()` function or in complex expressions.
optim.target.getcolumnvalue(`value`) optim.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 CM, 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: DECIMAL DOUBLE FLOAT DECIMAL FLOAT NUMBER Note: This function returns the column value as a 64bit INTEGER for columns with integer type data type. Note: For Db2 CCSID-sensitive columns, this function returns the data in the CCSID of the Optim plan. Data from Legacy Tables can be returned in the CCSID of the plan or “as-is”, without any conversions, depending on the Legacy site options and Legacy Table settings. See also ‘Legacy note’ above. Use this function only with the `cm_transform()` function or in complex expressions.
optim.target.getcolumnasbytes() optim.target.getcolumnasbytes("`COLNAME`") optim.target.getcolumnasbytes("COLNAME1", "COLNAME2",value) optim.target.getcolumnasbytes(`n`)	Get a value from the current target column (), the named target column ("`COLNAME`", "`COLNAME1`", ...), or column (`n`) in the CM, where `n` is a number representing the `n`th column. Use this function for any type of data, including numeric and character type data, to obtain its native representation as a string of bytes. For CCSID-sensitive data there will be no conversion performed. 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 target column (), the named target column ("`COLNAME`"), or column (`n`) in the CM, 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 CM, 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. Note: For Db2 CCSID-sensitive destination columns, the value provided to this function should have the data in the CCSID of the Optim plan and conversion will be performed to the column’s CCSID as required. The Legacy Table data will be converted or assigned "as-is", without any conversions, depending on the Legacy site options and Legacy Table settings. See also ‘Legacy note’ above. Use this function only with the `cm_transform()` function or in complex expressions.
optim.target.setcolumnfrombytes(`value`) optim.target.setcolumnfrombytes("`COLNAME`", `value`) optim.target.setcolumnfrombytes("COLNAME1", "COLNAME2",value) optim.target.setcolumnfrombytes(`n,value`)	Set the value of the current target column (`value`), the named target column or columns ("`COLNAME`", "`COLNAME1`", ...), or column (`n`) in the CM, 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. Note: The value provided to this function should represent the data in its native representation as a string of bytes. There will be no data conversions of any kind. 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, in CHARACTERS for the string data types and BYTES for other data types, of the current target column (), the named target column ("`COLNAME`"), or column (`n`) in the CM, 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.getcolumnlengthbytes() optim.target.getcolumnlengthbytes("`COLNAME`") optim.target.getcolumnlengthbytes(`n`)	Get the length, in BYTES for all data types, of the current destination column (), the named destination column (`"COLNAME"`), or column (`n`) in the CM, 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 CM, 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 CM, 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.getcolumnencoding() optim.target.getcolumnencoding("`COLNAME`") optim.target.getcolumnencoding(`n`)	Get the data encoding for the specified target column. The value returned is in the form of a Lua string: “A”: ASCII encoding, “E”: EBCDIC encoding, “U”: Unicode encoding, “ ”: None of the above, data is not character-type.
optim.target.getcolumnccsid() optim.target.getcolumnccsid("`COLNAME`") optim.target.getcolumnccsid(`n`)	Get the data CCSID for the specified target column. The value returned is in the form of a Lua number representing the CCSID of the data or 0 for data without a CCSID assigned.
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 CM, 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.target.iscolumnset()`	Returns TRUE value if the (current) CM PROCEDURE has already set the value of the (current) destination column for the current row by invoking optim.target. setcolumnvalue() or optim.target. setcolumnfrombytes() Any arguments specified on the call will be ignored.
`optim.target.columnisset()`	Sets the state of the current destination column as having a value already assigned, without actually calling any of the optim.target. setcolumnvalue() or optim.target. setcolumnfrombytes() functions. This function can be used while processing Legacy Tables, especially columns which may overlap with other columns in storage. A CM PROCEDURE may determine that the location in storage has already been processed, and the value set, by another CM PROCEDURE. Without using the `columnisset()` function, Optim would report an error if the CM PROCEDURE decided not to set the destination column value using: optim.target. setcolumnvalue() or optim.target. setcolumnfrombytes() Any arguments specified on the call will be ignored.
`optim.rejectrow()`	Skip row and go to the next row. Use this function only with the `cm_transform()` function or in complex expressions.