Specify Column Values in a Column Map

Rules for column maps differ between Compare and Move or Archive. These differences are discussed in the following section.

Compare Validation Rules

Compare automatically matches every column in the Source 1 table to the Source 2 column that has the same name and compatible data type. Click a Source 1 grid column to select a column name from the list of unmatched Source 1 columns. Select the blank space to exclude a column from the comparison. No other editing of Source 1 specifications is possible.

Move/Archive Validation Rules

The Column Map Editor lists the pairs of columns that are mapped. You can edit Source specifications in the following ways:

You can map a Source column to more than one Destination column as long as the data types are compatible.

Select Columns from a List

Right-click and select List Columns from the shortcut menu (or select List Columns from the Tools menu) to list columns from the source table. (The List Columns command is enabled when the Column Map uses Move/Archive validation rules.)

list columns display showing each column's name and data type

Table

Fully qualified name of the source table.

Column Name

Name of each column in the source table. To select a column, click and drag the column name from the List Columns dialog to the Source Column grid cell in the Column Map Editor. The status is updated automatically.

To change the column display, select commands from the View menu in the List Columns dialog, as follows:

  • To view all columns in the Column Map, select All.
  • To view all unused columns, select Unused. If the table contains many columns, it may be helpful to display only unused columns.

Data Type

Data type for each column in the source table.

Edit Source Column Names

Overtype source column values or right-click to select commands to Clear, Find, or Replace source column values. (If you clear a Source Column grid cell, the corresponding destination column value is not affected.)

A source column value in bold type can be viewed and edited from the Source Column dialog only. To open the dialog, double-click the value or use the Expand Source Column command. For more information, see Expand Source Column.

You can use any of several methods to map values to Destination Columns. Specify:

Value Description
Column Name An explicit column name (column names are case‑insensitive).
NULL NULL. The destination column must be nullable.
Numeric Constant A numeric constant. The constant value must fit into the destination column as defined by its data type, precision, and scale.
Boolean Constant A Boolean constant (TRUE or FALSE).
Special Register A special register:
  • CURRENT DATE
  • CURRENT_DATE
  • CURRENT TIME
  • CURRENT_TIME
  • CURRENT TIMESTAMP
  • CURRENT_TIMESTAMP
  • CURRENT SQLID
  • CURRENT_SQLID
  • CURDATE( )
  • CURTIME( )
  • GETDATE( )
  • GETTIME( )
  • SYSDATE( )
  • NOW( )
  • WORKSTATION_ID
  • USER
String Literal A string literal, enclosed in single quotes. The destination column must contain character data.

Example: 'CA' or '90210'.

Hexadecimal Literal A hexadecimal literal.

Example: X‘1234567890ABCDEF' or 0X1234567890ABCDEF

Date/Time Literal A date/time literal, enclosed in single quotes. Separate the date and time with a space. To format the date/time with a decimal fraction, place a period after the time, followed by the fraction. The date format is determined by the settings in Regional Options on the Control Panel of your computer.
Note: For Oracle Timestamp with Time Zone columns, you must specify the time zone suffix last.
Substring Function A Substring Function to use a portion of a source column value.
Random Function A Random Function to generate a random value.
Sequential Function A Sequential Function to define an incremental sequential value.
Identity or Serial Function An Identity or Serial Function to direct the DBMS to supply a sequential value (integer) for a destination column.
Oracle Sequence Function An Oracle Sequence Function to assign a value to a destination column using an Oracle Sequence.
Propagate Primary or Foreign Key Value Function A Propagate Function to assign a value to a primary or foreign key column and propagate that value to all related tables.
Concatenated Expressions A Concatenated Expression to obtain a derived value.
Numeric Expressions A Numeric Expression to obtain a derived numeric value.
Column Map Procedure A Column Map Procedure to obtain site-specific values. See Column Map Procedures.
Exit Routine An Exit Routine to obtain site-specific values. See Exit Routines for Column Maps.
Note: Information about functions and expressions is on the following pages. For details on using Exit Routines in a Column Map, see Exit Routines for Column Maps.
Data Privacy Functions

These functions require an Optim Data Privacy license. For detailed information, see Data Privacy Functions. To use these functions to map values to Destination Columns, specify:

Value Description
Lookup Functions A Lookup Function to obtain the value for one or more destination columns from a lookup table.
Random Lookup Function A Random Lookup Function to generate a random value for one or more destination columns from a lookup table.
Hash Lookup Function A Hash Lookup Function to obtain the value for one or more destination columns from a lookup table, according to a hashed value derived from a source column.
Shuffle Function A Shuffle Function to replace a source value with another value from the column.
Transformation Library Functions The Transformation Library Functions to mask personal data such as a social security number, credit card number, or e-mail address.
Age Function An Age Function to age the source column value.
Currency Function A Currency Function to convert source column currency values.

Expand Source Column

Use the Source Column dialog to enter long source column values or functions up to 999 characters. The Source Column dialog is available only for Column Maps using Move/Archive validation and is not available for columns that contain a reference to a Column Map Procedure. You can enter a reference to Column Map Procedure in the Source Column dialog, but the dialog will not be available for the column after the value is defined.

To open the Source Column dialog, right-click the source column cell and select Expand Source Column from the shortcut menu (or place the cursor in the cell and select Expand Source Column from the Tools menu).

source column dialog

If the source column value includes carriage return/line feed pairs, the value is displayed in bold type in the Column Map and can be viewed and edited in the Source Columndialog only. Carriage return/line feed pairs are removed from a reference to a Column Map Procedure. Carriage return/line feed pairs are not displayed in the value and are created by using the Enter key to make a line break in the Source Columndialog (they may also be present in an imported Column Map). To open the Source Column dialog for a value in bold type, double-click the value or use the Expand Source Column command.

Enter text in the Source Text area. Click OK to exit the dialog and validate the entry (if the text is not valid, the dialog will remain open and display error messages). Click Cancel to exit the dialog and clear the entry, retaining the original source column value.

Shortcut Menu Commands

Right-click the Source Text area to display the following:

Restore Text to Original Value
Remove text entered in the dialog and display the original source column value. You will be prompted to confirm your selection.
Validate Text
Validate the value according to Move/Archive validation rules. The dialog will display error messages.

Substring Function

The Substring Function returns a substring of the contents of the named column. The syntax is:

SUBSTR(columnname, start, [length])

columnname
Name of a character or binary column.
start
The position of the first character in the string.
length
The number of characters to use.
  • If the locale uses a comma as the decimal separator, you must leave a space after each comma that separates numeric parameters (for example, after the comma between start and length.
  • start and length are integers greater than or equal to 1.
  • start plus length cannot exceed the total data length plus 1.
  • column-name and start value are required. If you specify only one integer, it is used as the start value. The substring begins at start and includes the remainder of the column value.

Example

If the PHONE_NUMBER column is defined as CHAR(10), you can use the Substring Function to map the area code. To obtain a substring of the first three positions of the phone number (area code) for the destination column, specify:

SUBSTR(PHONE_NUMBER, 1, 3)

Random Function

The Random Function returns a number selected at random within the range indicated by the low and high values. The syntax is:

RAND(low, high)

low
Lowest possible random value.
high
Highest possible random value.
  • Use the Random Function with character or numeric data.
  • If the locale uses a comma as the decimal separator, you must leave a space after the comma.
  • low and high are integers within the range -2,147,483,648 to 2,147,483,647.
  • low and high are further limited by the data type and length for the destination column.
  • low must be less than high.
  • When you use the Random Function in a concatenated expression, a variable length string is returned.

Example

You can use the Random Function to mask or change sales data for a test database. Assume the YTD_SALES column is defined as DECIMAL(7,2). The maximum number of digits to the left of the decimal is 5; the possible range for this column is -99999 to 99999. To create test data within a range from 1000 (low) to 89999 (high), specify:

RAND(1000, 89999)

In this example, the function returns random sales values within the range you specified from 1000.00 to 89999.99.

Sequential Function

The Sequential Function returns a number that is incremented sequentially. The syntax is:

SEQ(start, step)

start
Start value.
step
Incremental value.
  • Use the Sequential Function with character and numeric data.
  • If the locale uses a comma as the decimal separator, you must leave a space after the comma.
  • start and step are integers within the range of -2,147,483,648 and 2,147,483,647.
  • start and step are further limited by the data type and length of the destination column.
  • If the calculated value exceeds the length of the destination column, the function automatically resets to the start value.
  • When you use the Sequential Function in a concatenated expression, a variable length string is returned.

Example 1

You can use the Sequential Function to change customer data for a test database. Assume that the CUST_ID column is defined as CHAR(5). To increment by 50, starting at 1, specify:

SEQ(1, 50)

In this example, the function returns CUST_ID values starting at '00001' and increments by 50 to generate '00051', '00101', etc. When the result exceeds '99951', the function resets to the start value of 1.

Example 2

You can use the Sequential Function in a Column Map to mask sales data for a test database. Assume that the YTD_SALES column is defined as DECIMAL(7,2). To increment by 100 starting at 1000, specify:

SEQ(1000, 100)

In this example, the function returns YTD_SALES values starting at 1000 and increments by 100 to generate 1100, 1200, etc. When the result exceeds 99999, the function resets to the start value of 1000.

Example 3

Assume that the SALESMAN_ID column is defined as CHAR(6). To insert values beginning with ‘NJ,' followed by a number starting at 50 and incremented by 10, use the function in a concatenated expression:

'NJ'||SEQ(50, 10)

In this example, the function returns SALESMAN_ID values starting at 'NJ50 ' and increments by 10 to generate 'NJ60 ', 'NJ70 ', etc. When the result exceeds 'NJ9990', the function resets to the start value.

Identity or Serial Function

The Identity and Serial Functions direct the DBMS to supply a sequential value (integer) for a destination column. The syntax for these functions is:

IDENTITY( )

SERIAL ( )

  • Use the Identity Function for Identity columns in DB2®, Sybase ASE, and SQL Server databases.
  • Use the Serial Function for Serial columns in Informix® databases.
  • Both functions are valid for Insert (update/insert) and Load Processing, but are not valid for Convert Processing.
  • If rows are updated in an Insert Process (update/insert), the destination column targeted by the Identity Function or Serial Function retains the original value. In addition, if the destination column is part of the primary key, the column value remains unchanged when the row is updated.
  • You can use the Identity Function or Serial Function with the Propagate Function for Insert or Convert Processing; however, you cannot propagate Identity or Serial columns in a Load Process.

Oracle Sequence Function

The Oracle Sequence Function assigns a value to the destination column using an Oracle Sequence. The syntax is:

schema.seqname.NEXTVAL [INCL_UPD]

schema
Qualifier for the Oracle Sequence name.
seqname
Name of the Oracle Sequence that assigns sequential values.
NEXTVAL
Keyword that inserts the next Oracle value into the destination column.
INCL_UPD
Optional keyword that updates a sequence value assigned to a column when rows are updated during an Insert Process. If not specified (default), the column value remains unchanged when the row is updated.
  • You can use the Oracle Sequence Function to assign unique sequential values for rows to be inserted into an Oracle database.
  • The Oracle Sequence Function is valid when used in a Column Map for Insert or Load Processing, but is not valid for Convert Processing.
  • If rows are updated in an Insert (update/insert) Process and the destination column is part of the primary key, the column value remains unchanged when the row in the destination table is updated. To use Oracle Sequence when performing an update/insert, include INCL_UPD with the function.
  • During a Load Process, the process uses the Oracle Sequence Function to assign a new value to each destination row. The Load calls the DBMS to obtain these values. If you choose not to run the Oracle Loader, these sequence values are never used.

Example 1

To assign a sequential value to increment customer numbers, where the name of the Oracle Sequence is schema.numeven, specify:

schema.numeven.NEXTVAL

Example 2

To expand the first example and update an existing sequence value, specify:

schema.numeven.NEXTVAL(INCL_UPD)

Propagate Primary or Foreign Key Value Function

The Propagate Function assigns a value to a primary key or foreign key column and propagates that value to all related tables. The syntax is:

PROP( { value [, columnname| ] EXIT exitname |
PROC { LOCAL | identifier.name } } )

value
Value to assign to the column. Specify any valid Column Map source value (for example, a column name, string literal, expression, or function). The value must be appropriate for the column.
columnname
Name of the source column containing the value that is the subject of the function. The resulting value is inserted into the destination column of the mapped table and the appropriate destination column in the participating related tables.

The column name is required only if no source column matches the destination column in both name and data type. If not specified, the name of the destination column is used.

exitname
SeeExit in a Column Map
identifier.name
SeeProcedure in a Column Map
  • If the locale uses a comma as the decimal separator, you must leave a space after each comma that separates numerical parameters.
  • The Propagate Function is valid in a Column Map for Insert (but not Update or Update/Insert), Load, or Convert Processing.
  • When you use the Propagate Function, at least one related table must be included in the process. You can use Propagate multiple times for the same process.
  • You can use the Propagate Function for either a primary key column or its corresponding foreign key column, but not both.
  • If multiple columns define a relationship, you can use the Propagate Function for one or more of those columns. However, in an Optim extended relationship, you can specify the Propagate Function only on column-to-column relations.
  • You can use the Identity Function or the Serial Function within the Propagate Function for Insert or Convert Processing; however, you cannot propagate the Identity Function in a Load Process.
  • The parameters specified in the Propagate Function are not validated until run time. If there are conflicts, the process does not run.
  • Insert can have propagate cycles. However, Load and Convert Processing may not result in propagate cycles. Cycles are detected when the process is validated at run time. If a Load or Convert Request generates a propagate cycle, the process does not run.
  • Optim remembers the source values and the values assigned to corresponding destination columns. Therefore, you can propagate to destination columns where the source is an expression. When the evaluated expression matches a source value, Optim assigns the corresponding destination value. When the evaluated expression does not match any source values, a conversion error occurs.

Before executing an Insert, Load or Convert Process, you can review the Column Map to verify how the Propagate Function is used in the process.

Example 1

You can generate a random number, assign it to the default destination column, and propagate the number in the destination columns of related tables. To generate a value between 10000 and 99999, insert it into the mapped destination column and propagate it to the destination columns of related tables, specify:

PROP(RAND(10000, 99999))

Example 2

You can perform the same function as in Example 1 when the source and destination column names do not match. To include the name of the source column (CUST_NUMBER) in the Propagate Function, specify:

PROP(RAND(10000, 99999), CUST_NUMBER)

Example 3

You can use Oracle Sequence to generate the value for the destination column and propagate that value in destination columns of the related tables. To propagate the Oracle Sequence named, schema.numeven, specify:

PROP(schema.numeven.NEXTVAL)

Concatenated Expressions

Concatenation allows you to combine column values or combine a column value with another value, using a concatenation operator (CONCAT, ||, or +). A concatenated expression can include character values or binary values, but not both:

Character Values
Concatenated character values can be character columns, string literals, substrings of values in character columns, the sequential function, or the random function.
Binary Values
Concatenated character values can be binary columns, hexadecimal literals, substrings of binary columns, the sequential function, or the random function.
  • A concatenated expression cannot include a zero-length string literal (' '), a special register, or the Age Function.

Example

Assume that the CUSTOMERS table stores an address in two columns: ADDRESS1 and ADDRESS2. The SHIP_TO table stores an address in one column: ADDRESS. You can use a concatenated expression to combine address information from two columns in one table to one column in another.

To combine the address, specify one of the following:

CUSTOMERS Table SHIP_TO Table
ADDRESS1 || ADDRESS2 ADDRESS
ADDRESS1 CONCAT ADDRESS2 ADDRESS
ADDRESS1 + ADDRESS2 ADDRESS

Numeric Expressions

You can use a numeric expression to specify a value in the source column whenever the data types for the corresponding source and destination columns are compatible. An arithmetic expression consists of:

operand1 operator operand2

Each operand must be a numeric column or a numeric constant. The operator specifies whether to add ( + ), subtract ( - ), divide ( / ), or multiply ( * ).

Example 1

To increase the value in a column UNIT_PRICE defined as DECIMAL(5,2) by 10 percent, specify:

1.1 * UNIT_PRICE

Example 2

To divide the value in an INTEGER column ON_HAND_INVENTORY in half, specify:

ON_HAND_INVENTORY / 2.