Propagate Primary or Foreign Key Value Function

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 that contains 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

A column map exit name.

identifier.name

A column map procedure name.

• 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 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)