Column map procedures

A column map procedure is a procedure that is used to mask or transform the data in a source column. You can specify a column map procedure for data transformations in a convert, insert, load, or restore process that are beyond the scope of native column map functions. Using the Lua scripting language you can define a named or local column map procedure. A local column map procedure is stored in the column map in which you define it, and is usable only in that column map. A named column map procedure is stored in the Optim directory and can be reused or shared with other users.

For details on defining column maps, see Column Maps.

One function of a column map procedure is to generate values that could not otherwise be defined for the destination column. This function is useful for handling special processing and data manipulation according to site-defined rules. Column map procedures are not limited to data transformation, however. You can also use a column map procedure to reject rows on the basis of custom processing, to create a report tailored to the needs of your site, or to implement conditional data transformations.

Note: A CM procedure, specified on a destination column, is required to set the value of such column by means of optim.target.setcolumnvalue() or optim.target.setcolumnfrombytes() functions. See Lua for Column Map expressions and procedures.

Functions optim.target.iscolumnset() and optim.target.columnisset() can be used to determine or manipulate the "set state" of the destination column.

Note that DB2® data provided to and returned from the Lua environment will be generally assumed to be encoded in EBCDIC and have the CCSID of the Optim™ plan. There are functions provided to retrieve and set the data “as-is” in its original CCSID and encoding. Special consideration may need to be given in respect to processing of data described by the Legacy Tables. In general, while using the prior release's compatibility mode, no conversions will be performed on the Legacy Table data. Legacy data may or may not be translated depending on Optim site options and Legacy Table specifications. This is discussed in the Common Elements Manual in topic "Legacy Data Translation". Depending on these settings, the character and graphic Legacy data may 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.

Lua chunks attached to Column Map Procedures or CM Expressions may 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 correctly interpret Lua chunks entered using EBCDIC CCSID 1047/037 and compatible CCSIDs. Compiler errors may be reported when attempting to process Lua chunks entered using different CCSIDs.

To prevent such errors, the Optim Lua compiler has been modified to accept a special dedicated comment statement -- LuaSpcChr followed by values to define the representation of certain Lua special characters and operators.

The -- LuaSpcChr comment line may appear anywhere in the chunk before the first use of any of the characters #\˜ˆ[]{}.

The special characters must be specified in the following order and all characters have to be specified.

-- LuaSpcChr #\˜ˆ[]{} (in the exact order)

  •    # = value for the Lua length operator
  •    \ = value for the backslash escape character
  •    ˜ = value for the “not” inequality operator
  •    ˆ = value for the exponentiation operator
  •    [ = value for the left square bracket character
  •    ] = value for the right square bracket character
  •    { = value for the left curly brace character
  •    } = value for the right curly brace character

For example, the exponentiation operator (ˆ) is defined at hexadecimal code point x'B0' in EBCDIC CCSID 37. However, in EBCDIC CCSID 500 it is defined at code point x'5F'. In addition, the left and right brackets are defined at code points x'4A' and x'5A' in CCSID 500 respectively instead of at x'BA' and x'BB' as in CCSID 37. Therefore, when using a terminal at CCSID 500, you can specify the following comment for Optim to properly recognize and process these operators and characters in your Lua chunk (hex values listed below characters):

-- LuaSpcChr #\˜ˆ[]{}

664DA8E98C8947EA545CD
0003412733890B01FAA00

For instructions on defining a local column map procedure using Lua functions, see Defining a local Column Map Procedure using Lua functions. To define a named column map procedure, see Defining a named Column Map Procedure using Lua functions.