Reduction rule

Description

Reduction rules specify which objects should be reduced in an XDM task execution.

They are used to control the amount of data when fetching rows from a table. To this end the SELECT statement to fetch the data can be customized. The statement can be either a conditional expression (e.g. WHERE clause), or a template that defines the entire SELECT statement.

Once the set of rows to be copied has been determined, the reduction rules are applied to this set. When a row is removed from the set, because it does not satisfy the condition of a reduction rule, XDM automatically removes all dependent rows from the set as well. Therefore, reduction rules will be set on the source tables.

All selected rows which fulfill the reduction rule condition are copied, all other rows are discarded.
If a reduction rule is set for a table that is not selected in the task execution, the rule will be ignored.
Every reduction rule in the task execution is used. If there are two or more rules on a table, only the last matching rule is used. All previous rules are ignored for this table.

The reduction rule expression specifies a conditional expression that determines whether a row is to be copied or not.

There are two ways to define an expression:

  • By defining a WHERE condition for a SELECT statement generated at runtime

  • By defining a template that contains the full SELECT statement.

XDM evaluates the expression as a SELECT statement when processing the keys during subset extract. If the full SELECT statement template is not defined, the expression is interpreted as a WHERE clause for a SELECT statement. This must be entered in the form WHERE <condition>. The first part of the statement is generated by XDM.

It is possible to use runtime parameters as variables in the query. This is both for the WHERE condition and for the template possible. When using a SELECT template, the variables ${columns}, ${tableName}, and ${keys} will be set automatically in the task execution.

Using the WHERE clause

Usually the expression is set by defining a WHERE clause. A SELECT query that uses the specified WHERE clause is generated. Only rows which satisfy the resulting SELECT query are copied.

It is not possible to use conditions containing columns from other tables. If it is necessary to use such condition, use full templates instead.

Using the SELECT template

With a template, it is possible to customize the SELECT and FROM clauses of the statement. A SELECT template starts with <#--template--> and has to contain a valid SQL SELECT statement, as well as the variables ${columns} for the list of columns, ${tableName} for the name of the table and ${keys} for the list of keys selected by XDM when evaluating the start condition. These values will be set automatically by XDM during task execution (see default template below).

The variables ${columns}, ${tableName} and ${keys} must be part of the template, otherwise the template cannot be processed correctly.
A template should be used for complex reduction rules that contain more than a straight forward WHERE clause.

The default template is defined as:

<#--template-->
SELECT ${columns}
   FROM ${tableName} "T"
   WHERE ${keys}

Examples

Thin out rows

A typical use for a reduction rule is to thin out the rows that are copied from the child table of a relationship. For example, if a table contains history records, and a data relation rule specifies that the rows from the child table are also to be copied, a reduction rule can be added to the child table in order to specify that not all dependent rows, but only those that fulfill a certain conditional expression, are supposed to be copied. An example for this conditional expression is:

WHERE ID < 1000

Copy data depending on condition

Another usage of reduction rules is to filter rows that have a special value in one column. Assume, you want to copy customers with their contracts. The contracts have different types named A, B and C, which are stored in the CONTRACT_TYPE column of the contract table. For your purpose, only contracts of type A are necessary. To avoid to copying contracts of type B and C, you would define a reduction rule for the table PROD.CONTRACT and the column CONTRACT_TYPE:

WHERE CONTRACT_TYPE = 'A'

Copy data depending on a condition in another table

If the condition is set in another table, it is not possible to use the WHERE condition. We modify the example Depending Condition: Instead of a CONTRACT_TYPE column with the types as data, the type is stored in its own table called PROD.CONTRACTTYPE. The ID of this table is stored as foreign key in the column CONTRACT_TYPE. Again, only contracts of type A are necessary. To satisfy this requirement, a reduction rule template with a join between PROD.CONTRACT and PROD.CONTRACTTYPE is used. Note that PROD.CONTRACT is the subject of the reduction rule and therefore specified by the variable ${tableName}.

<#--template-->
SELECT ${columns}
   FROM ${tableName} "T" JOIN "PROD"."CONTRACTTYPE" "C" ON "T"."CONTRACT_TYPE" = "C"."ID"
   WHERE ${keys} AND "C"."TYPE" = 'A'

For more details refer to the reduction rules description in the reference.

Concepts and more information

Properties

The table below documents the available properties for reduction 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 selection pattern which is used to match the names of columns. The rule will be applied to the matching columns. If you use patterns for the table schema or table name, then the rule will be applied to all columns which match the column pattern in all matching tables.

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.

Expression

expression

String

n/a

Specifies a conditional expression that determines whether a row is to be copied or not.

There are two ways to define the expression:

  • Define a WHERE condition for a SELECT statement generated at runtime, or

  • Define a template that contains the full SELECT statement.

XDM evaluates the expression as a SELECT statement when processing the keys during subset extract. If the full SELECT statement is not defined, the condition is entered before the WHERE statement. Here the first part of the statement is generated automatically by XDM, only the WHERE clause can be specified.

At the top of the page you can find a few examples.

The keyword WHERE must be part of the statement, if no template is used.
It is possible to use runtime parameters as variables in the query. When using a SELECT template, the variables ${columns}, ${tableName}, and ${keys} will be set automatically in the task execution.

Name pattern

namePattern

String

%

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

Schema pattern

schemaPattern

String

%

Specifies a selection pattern which is used to match the schema of a database table.

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 reduction rules.

List Actions

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

  • 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

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