Exclude rule

Description

Exclude rules are used to prevent objects from being copied, even if the object names match one or more selection criteria.

In order to understand exclude rule functionality, it is essential to understand how XDM decides which objects are to be copied. In the first instance, XDM selects a set of database objects, which we can refer to as the selection set. For TC task templates, this is defined by selection rules. For RLP task templates the selection set is determined by way of relationship to the start table.

After the initial selection set has been determined, exclude rules can be used to restrict which objects are actually copied. There are two types of exclude rules: normal exclude rules and inverted exclude rules.

Normal exclude rules exclude those objects matching the rule. Database objects which should not be copied are thereby explicitly defined.

Inverted exclude rules allow objects matching the rule to be included in the selection set and exclude objects not matching the rule. Thus, inverted exclude rules specify which database objects are available for copying, rather than specifying which objects should not be copied.

Exclude rules are entirely optional. They can be set in the task template or in a task of this template.

You have the option to define more than one exclude rule for an object, and it is also possible to define normal and inverted exclude rules for the same object.

Exclude rules are applied after all selection rules have been evaluated. Therefore, if an object is covered both by one or more selection rules, and one or more exclude rules, the object will not be copied. When an object is excluded from processing, XDM will not generate DDL statements for this object, and no data will be copied from the source object.

In this case an object is copied if all the following conditions are met:

  1. The object is included in the initial selection set.

  2. The object is matched by an inverted exclude rule.

  3. The object is not matched by a normal exclude rule.

It is also possible to define an exclude rule on a connection for global use on that connection. Exclude rules on connections are applied to all objects using the connection to access the source environment.

Exclude rules for aliases

For aliases, exclude rules can be set in these task templates and tasks:

  • Table copy task templates and tasks,

  • Table to icebox task templates and tasks,

  • Structure compare task templates and tasks, and

  • Structure to icebox task templates and tasks.

When the setting Using aliased tables is selected, the alias will be treated as a table. In this case an exclude rule with object type aliases won’t exclude any aliases, as they will not be viewed as an alias.

Exclude rules for grants

For grants, exclude rules can be set in these task templates and tasks:

  • Table copy task templates and tasks,

  • Table to icebox task templates and tasks,

  • Structure compare task templates and tasks, and

  • Structure to icebox task templates and tasks.

When the object type is Grant, the Schema pattern and Name pattern are replaced with Grantor pattern and Grantee pattern respectively.

The grantor pattern and grantee pattern can be set to specify which grants should be excluded.

Exclude rules for columns

For columns, exclude rules need a schema pattern and a name pattern for the corresponding table, and a column pattern for the column.

This option removes a column from all processing. No data is selected from that column and no DDL is generated for it. This can lead to errors if the column is used by other objects, for example an index.

This option can be used if a source column should not be transferred to the target, but also as a workaround if a column cannot be copied for some reason, and is not necessarily required in the target.

Examples:

  1. You want to copy the schema TEST with a table copy task template using selection rules, but not the table TEST.ACCOUNT. You would specify a selection rule with schema pattern TEST and name pattern % together with an exclude rule with schema pattern TEST and name pattern ACCOUNT.

  2. Consider the situation where a table PERSON has child tables PER_01 and PER_02, from which you wish to extract data with a subset task template. Logically the start table for the task template would be PERSON. Furthermore, suppose the tables CONTRACTS, ACCOUNTS, and ADDRESS also have foreign key references to PERSON, but you do not wish to extract data from these tables. You could solve the problem by specifying an inverted exclude rule with the name pattern PER%.

  3. You want to exclude all grants with grantee OTTO from the selected target tables, because the user does not exist in the source environment. You would specify an exclude rule with scope Target, object type GRANT, grantor pattern % and grantee pattern OTTO to remove the grants from the selection.

Concepts and more information

Properties

The table below documents the available properties for exclude 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.

Column pattern

columnPattern

String

Specifies a column pattern which is used to match the name of database table columns.

A column pattern is only relevant for the object type COLUMN.

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.

Invert (Exclude all objects not matching this rule)

invert

Boolean

false

Specifies whether the rule is inverted. Database objects which do not match an inverted rule will be excluded.

Name pattern

namePattern

String

%

Specifies a selection pattern which is used to match the name of database objects of the specified object type.

For grants, the name pattern specifies a grantee, or a selection pattern that matches one or more grantees. In this case, the property is shown as Grantee pattern in the user interface.

Object type

objectType

ObjectType

TABLE

Specifies the type of database object that the rule matches. The list of available object types includes items such as table, sequence, view, index, alias, and column. XDM only supports views in Db2 for z/OS and in Db2 for LUW databases. Hence, views can only be specified in rules for those database types.

Schema pattern

schemaPattern

String

%

Specifies a selection pattern which is used to match the schema of database objects of the specified object type.

For grants, the schema pattern specifies a grantor, or a selection pattern that matches one or more grantors. In this case, the property is shown as Grantor pattern in the user interface.

Scope

scope

Scope

SOURCE

Can be set to either Source or Target. If set to Source, the patterns specified in the fields Schema pattern and Name pattern refer to the names of source objects. If set to Target, they refer to the names of target objects. Exclude rule scope should only be used in structure compare task templates and tasks.

For exclude rules in from icebox tasks, the scope will be ignored. In this case, the table schema must be set to the source schema and the table name must be set to the source name, because the table exclusion is executed during the icebox generation extract in stage 1 of the task.

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.

Actions

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

List Actions

The following actions are available on the exclude 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

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

Object Actions

The following actions are available on specific exclude 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.

  • Check

  • Copy

  • Delete

  • Edit

  • Object History

This action validates the object and its dependencies, reporting configuration errors that could cause issues during task or workflow execution. The validation will cascade through the child objects of the checked objects and objects referenced by them.

For instance, if an installed application of an environment is checked, the check will process the application model, the specified version, the connection, modification sets, and involved modification methods. If an object has rules, all active rules will be checked. The modeling connection and version, including their modification sets and methods, will also be checked. Deactivated objects will not be included in recursive checks, but can be checked individually if the check is executed on the object itself.

Checks often require additional information from the context of the objects being checked, such as necessary connections or custom parameter values. The check will gather information from the objects being checked and use it to perform checks on child objects. Any required additional information must be provided before the check begins. The check queries the user to provide these missing information.

Database object checks

For all rules which reference database objects such as tables, columns, etc, the check verifies that the those objects exist in the database system. If a connection can be inferred from the context, then this connection is used. If no connection is available in the context, it must be specified before the check is executed.

Connection checks

For objects which configure access to external systems, such as connections or storage locations, the configuration check verifies that access can be established using the given credentials. Furthermore, additional operations on database connections are performed to check whether the credential user has the necessary authorization to access relevant database objects. In particular, the credential user’s permission to read source tables and write to target tables is verified. Similarly, for storage locations the check verifies that the credential user has permission to write to the working directory.

Code checks

For all entities containing code segments, such as modification methods or condition scripts, the syntax for the code is checked. This does not check, however, whether at run time all necessary variables are likely to be available.

The following permissions are required:

  • READ

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