0 / 0
Loop variables in DataStages

Loop variables

You can specify that the Transformer stage outputs multiple output rows for every input row by defining a loop condition.

About loop variables

You can specify that the Transformer stage repeats a loop multiple times for each input row that it reads. The stage can generate multiple output rows corresponding to a single input row while a particular condition is true.

You can use stage variables in the specification of this condition. Stage variables are evaluated once after each input row is read, and therefore hold the same value while the loop is executed.

You can also specify loop variables. Loop variables are evaluated each time that the loop condition is evaluated as true. Loop variables can change for each iteration of the loop, and for each output row that is generated.

The name of a variable must be unique across both stage variables and loop variables.

Defining a loop condition

You specify that the Transformer stage loops when processing each input row by defining a loop condition. The loop continues to iterate while the condition is true.

You can use the @ITERATION system variable in your expression. @ITERATION holds a count of the number of times that the loop has been executed, starting at 1. @ITERATION is reset to one when a new input row is read.

To define a loop condition:
  1. In the Loop while field, click the expression editor icon.
    1. In the expression editor, specify the expression that controls your loop. The expression must return a result of true or false.
    2. Click Save and return.
  2. In the Loop iteration warning threshold field, define a warning threshold.

It is possible to define a faulty loop condition that results in infinite looping, and yet still compiles successfully. To catch such events, you can specify a loop iteration warning threshold. A warning is written to the job log when a loop has repeated the specified number of times, and the warning is repeated every time a multiple of that value is reached.

So, for example, if you specify a threshold of 100, warnings are written to the job log when the loop iterates 100 times, 200 times, 300 times, and so on. Setting the threshold to 0 specifies that no warnings are issued. The default threshold is 10000, which is a good starting value. You can set a limit for all jobs in your project by setting the environment variable APT_TRANSFORM_LOOP_WARNING_THRESHOLD to a threshold value.

The threshold applies to both loop iteration, and to the number of records held in the input row cache (the input row cache is used when aggregating values in input columns).

Defining a loop variable

You can declare and use loop variables within a Transformer stage. You can use the loop variables in expressions within the stage.

You can use loop variables when a loop condition is defined for the Transformer stage. When a loop is defined, the Transformer stage can output multiple rows for every row input to the stage. Loop variables are evaluated every time that the loop is iterated, and so can change their value for every output row. Such variables are accessible only from the Transformer stage in which they are declared. You cannot use a loop variable in a stage variable derivation.

Loop variables can be used as follows:

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

Any loop variables you declare are shown in a table in the right pane of the links area. The table looks like the output link table and the stage variables table. You can maximize or minimize the table by clicking the arrow in the table title bar.

The table lists the loop variables together with the expressions that are used to derive their values. Link lines join the loop variables with input columns used in the expressions. Links from the right side of the table link the variables to the output columns that use them, or to the stage variables that they use.

To define a loop variable:
  1. In the Loop variables field, click Add loop variable +.
  2. Enter the variable name, initial value, SQL type, extended information (if variable contains Unicode data), precision, scale, and an optional description. Variable names must begin with an alphabetic character (a-z, A-Z) and can only contain alphanumeric characters (a-z, A-Z, 0-9).
  3. Click Apply changes.
Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more