Evaluation sequences for transformer stage

To write efficient Transformer stage derivations, it helps to understand what items get evaluated and when.

The evaluation sequence for a Transformer stage is:

Evaluate each stage variable initial value
For each input row to process:
   Evaluate each stage variable derivation value, unless
   the derivation is empty
   For each output link:
      Evaluate constraint, if true:
         Evaluate each column derivation value
         Write the output row
   Next output link
Next input row

The evaluation sequence for a Transformer stage that has a loop condition defined is:

Evaluate each stage variable initial value
For each input row to process:
   Evaluate each stage variable derivation value (unless empty)
   Evaluate each loop variable initial value
   While the evaluated loop condition is true:
         Evaluate each loop variable derivation value (unless empty)
         For each output link:
            Evaluate constraint, if true:
               Evaluate each column derivation value
               Write the output row
         Next output link
   Loop back to While
Next input row

The stage variables, loop variables, and the columns within a link are evaluated in the order in which they are displayed on the parallel job canvas. Similarly, the output links are also evaluated in the order in which they are displayed.

Examples

Certain constructs are inefficient if they are included in output column derivations, because they are evaluated once for every output column that uses them. The following examples describe these constructs:

The same part of an expression is used in multiple column derivations.

For example, if you want to use the same substring of an input column in multiple columns in output links, you might use the following test in a number of output columns derivations:

IF (DSLINK1.col1[1,3] = "001") THEN ...

In this case, the evaluation of the substring of DSLINK1.col1[1,3] is repeated for each column that uses it. The evaluation can be made more efficient by moving the substring calculation into a stage variable. The substring is then evaluated once for every input row. This example has thus stage variable definition for StageVar1:

DSLINK1.col1[1,3]

Each column derivation starts with this test:

IF (StageVar1 = "001") THEN ...

This example can be improved further by also moving the string comparison into the stage variable. The stage variable is then defined as follows:

IF (DSLink1.col1[1,3] = "001") THEN 1 ELSE 0

Each column derivation starts with this test:

IF (StageVar1) THEN

The improved construct reduces both substring function evaluations and string comparisons.

An expression includes calculated constant values.

For example, a column definition might include a function call that returns a constant value:

Str(" ",20)

This function returns a string of 20 spaces. The function is evaluated every time the column derivation is evaluated. It is more efficient to calculate the constant value once. You can assign an initial value to a stage variable in the Variables tab of the Stage Properties window. The initial value is set to using this expression:

Str(" ", 20)

You do not supply the derivation of the stage variable on the main Transformer page. The initial value of the stage variable is evaluated once, before any input rows are processed. Because the derivation expression of the stage variable is empty, the stage variable is not re-evaluated for each input row. Change any expression that previously used the function Str(" ", 20) to use the stage variable instead.

The same considerations apply to any expression, or part of an expression, that generates a constant value. For example, the following expression concatenates two strings:

"abc" : "def"

The abcdef concatenation is repeated every time the column derivation is evaluated. Since the subpart of the expression is constant, this constant part of the expression can again be moved into a stage variable, using the initial value setting to perform the concatenation once.

An expression requiring a type conversion is used as a constant, or it is used in multiple places

For example, an expression might include the following code:

DSLink1.col1+"1"

In this example, the "1" is a string constant, and so must be converted from a string to an integer each time the expression is evaluated. The solution in this case is to change the constant from a string to an integer:

DSLink1.col1+1

If DSLINK1.col1 is a string field, however, then a conversion is required every time the expression is evaluated. If an input column is used in more than one expression, where it requires the same type conversion in each expression, it is more efficient to use a stage variable to perform the conversion once. You can create, for this example, an integer stage variable, specify its derivation to be DSLINK1.col1, and then use the stage variable in place of DSLink1.col1, where that conversion is required. When you use stage variables to evaluate parts of expressions, you must set the data type of the stage variable correctly for that context. Otherwise, needless conversions are required wherever that variable is used.

The advice on the use of stage variables equally applies to loop variables when you use the Transformer stage looping facilities. You add the evaluation to a stage variable if it is evaluated once per input row, or to a loop variable if it is evaluated once per looped output row when a loop condition was specified.

Transformer Stage

What is Transformer Stage?

DataStage provides several stages to load the data into the data warehouse or data marts. The stages classified into General, Database, Developement and Debug, File, Processing, Real time etc. and the transformer stage is a processing stage.

Local Variables:

You can declare and use your own variables within a Transformer stage. Such variables are accessible only from the Transformer stage in which they declared. Since these variables are local to the transformer, the name local variables.

The uses:

The values assigned by expressions.
They are used in expressions which define an output column derivation.
Expressions evaluating a variable can include other variables or the variable being evaluated itself.

Advantageous of stage variables:

1. Same part of an expression used in multiple column derivations.

For example, IF (DSLINK1.col1 [1, 3] = "001") THEN { statements evaluated here} used in multiple output column derivations, the expression evaluates for each column that uses it. Use the stage variable by moving this substring calculation and the substring evaluates just once for every input row.

2. Where an expression includes calculated constant values.

A column definition might include a function call that returns a constant value, such as: Str(" ",20).This returns a string of 20 spaces. In this case, the function would be evaluated every time the column derivation is evaluated. It is more efficient to calculate the constant value just once for the whole Transformer.

Use the stage variable to use this function. This case, the function would still be evaluated once for every input row.

Assign the initial value from the Stage Properties to the stage variable and leave the derivation empty. This way, the function evaluated only once for whole transformer processing.

3. Where an expression requires a type conversion and used as a constant, or used in multiple places

System variables

WebSphere DataStage provides a set of variables containing useful system information that you can access from an output derivation or constraint.

Name Description:

@FALSE The value replaced with 0.

@TRUE The value replaced with 1.

@INROWNUM Input row counter.

@OUTROWNUM Output row counter (per link).

@NUMPARTITIONS The total number of partitions for the stage.

@PARTITIONNUM The partition number for the particular instance.

Transformer stage: Input page

Partitioning tab:

The Partitioning tab allows you to specify details about how the incoming data partitioned or collected when input to the Transformer stage. It also allows you to specify that the data should be sorted on input.

By default the Transformer stage will attempt to preserve partitioning of incoming data, or use its own partitioning method according to what the previous stage in the job dictates.

In The transformer stage, the stage tab allows to set various properties in the General like Execution mode, Preserve partitioning, Advanced tab, Triggers tab etc.