Mapping rule

Description

Mapping rules are used to change the target names of selected tables, indexes, sequences, and other objects.

There are a number of reasons for using mapping rules. It is not uncommon for the target environment to use naming conventions that are different to those of the source environment, for instance, to distinguish between test and production environments.

XDM derives the target names of objects by applying the mapping rules to the source names. Based on these derived target names, XDM examines the target environment for existing target objects and, if the task is configured appropriately, may also generate DDL to create missing target objects.

The mapping rules affect the DDL that is generated by XDM, and the statements and commands that are used to populate the target tables.

If the target environment does not yet exist, the mapping rules affect the generated DDL so that the target tables are created under new names. If the target environment already exists, the compatibility check inspects the target tables as they would be named after applying the mapping rules. If two or more rules map the same object, XDM uses the mapping rule with the highest order number.

Mapping rules will not change data in tables. Modification rules should be used for this purpose.

Copy data between two databases

When setting up an XDM task template to copy data between two different databases, the mapping rules are optional. If the target objects have the same schemas and names as the corresponding source objects, there is no need to add mapping rules. In this case, XDM simply creates the required target objects under the same names as in the source environment.

In a table from icebox task execution the generation is usually restored to the same target environment each time. In this case, no mapping rules are required for the task template.

Copy data within one database

When setting up a table copy task template that copies data within one database, the task template must specify mapping rules in order to ensure that no table is copied onto itself. When copying within one database, XDM checks whether the combination of target schema and target name is different to the corresponding combination of source schema and source name for all selected objects. If an object has not been renamed, an error message will be produced.

Concepts and more information

Properties

The table below documents the available properties for mapping rules. The 'name' column displays the property name as it can be used in Groovy and Java Scripts.

Name

Type

Default

Description

Active

active

Boolean

true

Specifies whether the rule is active and therefore used, or inactive and therefore ignored, when a task is executed.

Description

description

String

n/a

An optional description for this object. The description can contain multiple lines to give more context on the configured object. The description is not used in a technical context.

Field selection mode

fieldSelectionMode

String

SIMPLE

This property controls the types of objects which can be selected by Object type and Field type:

XDM will map the corresponding field’s value to the targetValue.

Database specific

specifies that the Object type field must contain the name of a catalog table and Field type must contain a column of the selected catalog table.

Generic

specifies that all object types and the corresponding field types can be selected for objects that have common definitions in XDM for all DBMS.

Simple

specifies that the standard object types and field types can be selected by Object type and Field type (e.g. object = table, field = name).

Field type

fieldType

String

SCHEMA

Specifies the attribute of a database object that the rule affects. Available options are dependent on the field selection mode.

If the field selection mode is Simple, then the list of available options includes items like schema, name, owner, and authorization ID.

If the field selection mode is Database specific, the list of available options contains the names of columns in the catalog table or view specified by the object type.

The field selection mode Generic contains field types for the generic database model. Typically, the available options are equal to the Database specific field selection mode.

Object type

objectType

String

TABLE

Specifies the type of database object that the rule matches. Available options are dependent on the field selection mode.

If the field selection mode is Simple, then the list of available options includes items such as Table, Sequence, View, and Index. XDM only supports views in Db2 for z/OS and in Db2 for LUW databases. Hence, views can only be specified for those database types.

If the field selection mode is Database specific, the list of available options contains the names of tables and views in the catalogs of the database types supported by XDM.

The field selection mode Generic contains the object types of the generic database model.

Source selection pattern

sourceSelectionPattern

String

%

Specifies the selection pattern for the source field to be mapped.

Tags

tags

Tag

n/a

Contains the tags that apply to this object. These tags can be used in the search to find objects quickly and effortlessly.

Target value

targetValue

String

n/a

Specifies how the target value is derived from the source value. You may specify a fixed target value, a mapping pattern for more flexible mapping rules, or JavaScript for maximum control of the mapping process.

Target value mode

targetValueMode

String

SIMPLE_PATTERN

The target value mode specifies whether the target value is a standard mapping rule pattern, or JavaScript. The JavaScript approach is more flexible. However, for performance reasons, JavaScript mapping should only be used when you need more control of the mapping process. In most situations the Pattern option should be sufficient.

Actions

The available actions are described below. Some actions apply to the list, while others are specific to selected mapping rules.

List Actions

The following actions are available on the mapping rules list. If the action is disabled a tooltip will provide the exact reason for the deactivation. The required permissions are described in detail for each action.

  • Bulk Delete

  • Create

  • Export CSV

  • Import CSV

  • List History

  • Ordering

Delete the selected objects.

The following options are available:

Cascade

Recursively delete depending objects.

When using cascade, dependent objects are deleted first also with cascade enabled. Thus, a cascade deletion is a recursive function that deeply searches for dependent objects and deletes them first. There is only a confirmation for the first object. The dependent objects are deleted without confirmation but only when the user has the DELETE permission.

This feature is only available in development mode. More information about development mode can be found in the chapter User Settings. It should be used with caution.

An object in the result list can have two different states, these are:

DELETED

The object could be deleted.

NOT_DELETED

The object could be not deleted. This may be because the executing person does not have a delete permission on the object or the object is still referenced by others. A detailed reason can be determined with the help of the error message. If the object is still in use, these objects are also displayed.

The following permissions are required on the list:

  • READ

  • WRITE

Creates a new object in the current list. Depending on the object type either a popup dialog is shown for the most important settings, or the complete object is shown in edit mode. The dialog provides the option to create the object and remain in the current list or to switch to the newly created object in edit mode to perform further changes.

The following permissions are required on the list:

  • CREATE

Exports the current list in CSV format. This will start a download operation for your browser.

The following permissions are required on the list:

  • READ

Creates new objects in the list from a CSV file. The format must comply with the format produced by the export. All imported objects will be added to the list. The import terminates with an error message if an object with the same name already exists and Replace rules is set to false.

Replace rules

The Replace rules option determines whether a rule is appended or replaced. If set to true, all current rules will be replaced with the new rules, otherwise the new rules are appended to the existing rules.

The following permissions are required on the list:

  • WRITE

The history list tracks all modifications made to objects within it. A new record is added each time an object is created, edited, or deleted. A record indicates who made the change, which object was affected, and when the change was made.

For more information about the concept of the history refer to the history concepts.

The following permissions are required on the list:

  • READ

Opens a new view on the list that allows the user to specify the ordering of the elements using drag and drop. Confirm the new ordering with Save Changes.

The following permissions are required on the list:

  • READ

  • WRITE

Object Actions

The following actions are available on specific mapping rules. In order to execute the action, the user must possess the necessary permissions for the object. The permissions required for each action are described individually. If the user does not have these permissions, the action will be disabled and the tooltip will provide the exact reason for the deactivation.

  • Copy

  • Delete

  • Edit

  • Object History

The copy action creates an identical copy of the object. A new entry is created in the object list and all properties in the new object are set identical to the copied object.

The following permissions are required:

  • READ

  • WRITE

Delete the object. If the object is still used by another entity, an error message is displayed, and the object is not deleted. The delete operation must be confirmed in a separate popup.

The following options are available:

Cascade

Recursively delete depending objects.

When using cascade, dependent objects are deleted first also with cascade enabled. Thus, a cascade deletion is a recursive function that deeply searches for dependent objects and deletes them first. There is only a confirmation for the first object. The dependent objects are deleted without confirmation but only when the user has the DELETE permission.

This feature is only available in development mode. More information about development mode can be found in the chapter User Settings. It should be used with caution.

The following permissions are required:

  • READ

  • WRITE

Opens the current entity in edit mode.

The following permissions are required:

  • READ

  • WRITE

The history displays all changes made to the respective XDM object, including any changes made to its rules.

Each change record includes information about the operation performed (e.g. CREATE, UPDATE, DELETE), the timestamp, and the user responsible for the change.

For more information about the concept of the history refer to the history concepts.

The following permissions are required:

  • READ

Appendix

Mapping pattern

The mapping pattern is a syntax to map names, schemas or other fields with mapping rules. It is used to specify the target pattern.

The table Possible wild card characters in a mapping pattern describes the wild cards and what they mean. The mapping pattern is case-sensitive, for example the pattern a% is not equal to the pattern A%.

Table 1. Possible Wild card characters in a mapping pattern
Character Description

=

Copy one character from the original name

%

Copy multiple characters from the original name

(

Characters to the left of this symbol are not taken into account when referring to corresponding character positions of the source name

)

Characters to the right of this symbol are not taken into account when referring to corresponding character positions of the source name

-

Skip one character of the original name
The characters =, %, and - can neither be used before a left nor after a right parenthesis.
Using a hyphen only makes sense if the percent and/or the equals sign are used as well.
Left and right parentheses can each only be used once. If left and/or right parenthesis is not specified at all, the entire expression is effectively preceded by an implicit left parenthesis and/or followed by an implicit right parenthesis.

These wild card characters can be combined to create flexible expressions. To clarify the usage, please refer to the examples provided in the table Examples for the use of wild card characters. They are particularly useful to help understand the proper usage of left and right parentheses. Be aware using generic wild cards can accidentally lead to combinations where duplicate target names occur. Such situations lead to an error message and RC = 8 in the copy process.

Examples for Wildcards

For an explanation of the basic mapping pattern syntax, have a look at the following examples:

  1. If the original name is DSN8D71A, and the target value is defined as NEW=NAME, the resulting target name will be NEW8NAME.

  2. If the original name is P390DB, and the target value is AB(%, the resulting name will be ABP390DB. Without the left parenthesis, i.e. with a target value AB%, the first two characters of the original name would have been replaced by AB, resulting in AB90DB as the target name.

  3. If the original name is P390DB, and the target value is %)XY, the resulting name will be P390DBXY. Without the right parenthesis, i.e. with a target value %XY, the last two characters of the original name would have been replaced by XY, resulting in P390XY.

  4. If the original name is P390DB and the target name is AB--%, the resulting name will be ABDB. In this case P3 is replaced by AB, 90 is skipped, and DB is taken from the original name.

Table Examples for the use of wild card characters shows several further examples involving the symbols described above:

Table 2. Examples for the usage of wild card characters
Original name Target name Result Comment

P390DB

Q%

Q390DB

Q replaces P at the start of the original name

P390DB

%XY

P390XY

XY replaces DB at the end of the original name

P390DB

-%

390DB

P is skipped and the rest is copied from the original name

P390DB

-4%

490DB

P is skipped, 3 is replaced by 4, and the rest is copied from the original name

P390DB

%E-

P390E

D is replaced by E and B is skipped

P390DB

Q(%

QP390DB

Q is prepended to the original name

P390DB

%)XY

P390DBXY

XY follows the original name

P390DB

%--)XY

P390XY

DB is skipped, XY follows the original name

P390DB

MY(Q%

MYQ390DB

MY is prepended to the original name and Q replaces P

P390DB

AB%XY

AB90XY

AB replaces P3 and XY replaces DB

P390DB

A(B%XY

AB390XY

B replaces the first character of the original name, because it directly follows (

P390DB

AB%X)Y

AB90DXY

X replaces the last character of the original name, because it directly precedes )

P390DB

A(B%X)Y

AB390DXY

A combination of the previous two examples

P390DB

==--%

P3DB

P3 is copied, 90 is skipped, and the rest is copied

P390DB

%Q=

P390QB

Q replaces D in the original name

P390DB

-%-

390D

P and B are skipped

P390DB

A(%)Y

AP390DBY

A is prepended and Y is appended to the original name

P390DB

A(-%-)Y

A390DY

P and B from the original name are skipped

P390DB

A(=-%-Z)Y

AP90ZY

A precedes, P is copied, 3 is skipped, 90 is copied, D is skipped, B is replaced by Z, Y follows

Example of use in mapping

The object type is set to table, the field type is set to name. The source selection mask is set to C%, and the target value is set to %)_NEW. Using this mapping rule in a task, for every table that has a name starting with C, the suffix _NEW will be appended to the target name. For example, the source table CUSTOMER will be mapped to the table CUSTOMER_NEW in the target environment.

Mapping with Java Script

This section shows an example how you can map the target value using java script. To enable JavaScript mapping you need to specify JavaScript in the property Target value mode. After that you can enter the JavaScript code in the Target value property.

The JavaScript code has access to the following variables. These variables are provided by XDM each time the script is called during the mapping process:

inputValue

The input value that should be mapped by the script. This value is the value of an object like a schema name, table name, etc. The value is of the type that has been specified by Object type and Field type. For example if Object type is set to TABLE and Field type is set to SCHEMA the inputValue contains a table schema that should be mapped by the script.

baseObject

This is a reference to the object on which the value should be mapped. In the above example the baseObject is of type table.

outputValue

In addition to the above input values the script must also return a mapped output value. This value must be stored in the global variable called outputValue. The value that is specified for this variable is used as the new value, thereby replacing inputValue.

Example

The following JavaScript will add the prefix 'T_' whenever the input value starts with 'X':

if (inputValue.startsWith("X")) {
    outputValue = "T_" + inputValue;
} else {
    outputValue = inputValue;
}

In the case there needs to be a condition on the underlying object and one of its properties, the object can be accessed through the baseObject variable. This Java object is the internal representation of the database object. For example, if a column name is mapped, the object is of type AbstractColumn of the internal database objects API. For detailed information on how to work with the internal API, contact the UBS Hainer support.

Here is an example for a column mapping rule that has a condition on the table name the column belongs to:

importPackage(Packages.de.ubs.xdm.utils.databasemodel.model.db);
if(inputValue === 'source_column_name' && baseObject.columnProvider.getName(PropertyObject.ApplyHooks.NO) == 'source_table_name1')
      outputValue =  'target_column_name';
if(inputValue === 'source_column_name' && baseObject.columnProvider.getName(PropertyObject.ApplyHooks.NO) == 'source_table_name2')
      outputValue =  'target_column_name';

Advanced mapping for cross database system DDL generation

For more information about the specific use of mapping rules for cross DDL generation between different database systems look at the chapter Cross DDL Mapping