Loop Job is used in Job Flow for looping through Jobs repeatedly based on the number of iterations defined in its properties. You can have a single job or multiple linked jobs within loop job to loop through. Loop job is usually used when same set of jobs has be repeatedly run by passing different values which could be list of file names or filter conditions.

Note: To create a new Job Flow, refer Creating New Job Flow

To create a Loop Job, follow the below steps.

Step I: Create Loop Job

1. From the Jobs menu, drag and drop the Loop Job on canvas. Loop start and Loop end Job appears on canvas.

2. Link the Looper start Job to the Job from where the iteration needs to start, and link the Loop end job to the Job where iteration needs to end. For more information about using link in Job Flows, refer Working with link in Job Flow

For reference, here we have linked the Loop start Job to Data Flow instance, and then linked the DB command Job to Loop end Job.

Note: Below conditions should be satisfied to ensure the loop job works without any issues.

  • It is mandatory that all the jobs that need to be within loop start and end job should have at least one in-link and at least one out-link.
  • The in-link must be from loop start job or another job within loop and out-link must be linked to loop end job or another job within loop.

Step II: Provide the General details of Loop Job

On Canvas, select the Loop start job, and then under Properties, provide the General details. 

  • Name - The Name field consists of default name and is editable.
  • Parameter Name - Displays the name of the parameter which will be used to refer to the loop iteration values. The parameter name always starts with $LP_ followed by Loop start job name.
  • Description - In the text box, you can provide a description and is optional.
  • Disable task - Check Disable task, if the job need not be executed as part of the Job Flow, and you do not want to delete the Job. When loop start job is disabled then all the jobs linked to loop start and end job are disabled.

The name of Loop end job cannot be modified and is always as the Looper start Job with suffix '_End'.

Step III: Provide the Command to generate loop iteration values

Under Properties, click Properties tab and provide the detailsDifferent types of commands are available for generating the Loop iterations. Select the type of command to be executed from the Type dropdown.

Option I: SQL

Select the command type as SQL to run a SQL query and generate the values to be iterated though in the loop.

1. From the Type drop-down, select SQL.

2. To select the data point where the Sql query is to be executed, under Data Point, click Add.

3. The Data Point window lists all the available data points. Select the required Data Point. The selected data point and the database/schema will appear in the properties.

If there is any Global project that the user has access to, then the window displays the Project drop-down, which lists all the global projects. You can choose the global project from the Project drop-down, and select the global data point as required.

4. To provide the sql command to be executed for generating the looper iterations result, click on the Expression Editor arrow.

5. The Expression Editor window opens, and you can define the sql here. Once you enter the expression, click Validate to verify that there are no syntax errors. If everything is correct then, a success message is displayed. 

The SQL defined in Loop start job can include Parameters and the parameters can consists of Job Flow Parameter, Job Flow sql Parameter, Project Parameter, or System Parameter. For more information, refer Working with Job Flow ParametersWorking with Job Flow SQL ParametersWorking with Project Parameters, and Working with Studio System Parameters.

Option II: Command

Select the command type as Command to run OS commands or scripts and generate the values to be iterated though in the loop.

1. From the Type drop-down select Command.

2. To assign or change the associated agent where you want to execute the command, click Change.

  • If Default agent is assigned to the Project then automatically, the Default agent will be associated with the Loop job created.
  • If Default agent is not assigned to the Project then, no agent will be assigned automatically and appropriate agent needs to be assigned to the Loop job.

3. The Agent window lists all the available Agents. Choose the required Agent

4. To provide the command to be executed for generating the looper iterations result, click on the Expression Editor arrow.

5. The Expression Editor window opens, and you can define the command here. Once you enter the expression, click Validate to verify that there are no syntax errors. If everything is correct then, a success message is displayed. 

Option III: List:

Select the command type as list, to provide list of values to be iterated through in the loop

1. From the Type drop-down select List.

2. To assign or change the associated agent where you want to execute the command, click Change.

  • If Default agent is assigned to the Project then automatically, the Default agent will be associated with the Loop job created.
  • If Default agent is not assigned to the Project then, no agent will be assigned automatically and appropriate agent needs to be assigned to the Loop job.

3. The Agent window lists all the available Agents. Choose the required Agent

4. To provide the list values for generating the looper iterations result, click on the Expression Editor arrow.

5. The Expression Editor window opens, and you can define the command here. Once you enter the expression, click Validate to verify that there are no syntax errors. If everything is correct then, a success message is displayed. 

The list values defined in Loop start job can include Parameters and the parameters can consists of Job Flow Parameter, Job Flow sql Parameter, Project Parameter, or System Parameter. For more information, refer Working with Job Flow ParametersWorking with Job Flow SQL ParametersWorking with Project Parameters, and Working with Studio System Parameters.

Option IV: Remote Command

Select the command type as remote command to run OS commands, or scripts on remote machine accessible through sftp or ftp and generate the values to be iterated though the loop.

1. From the Type drop-down select Remote Command.

2. To select the Data Point where the Remote command is to be executed, under Data Point, click Add.

3. The Data Point window lists all the available file data points. Select the required Data Point.

If there is any Global project that the user has access to, then the window displays the Project drop-down, which lists all the global projects. You can choose the global project from the Project drop-down, and select the global File data point as required.

4. To assign or change the associated agent where you want to execute the command, click Change.

  • If Default agent is assigned to the Project then automatically, the Default agent will be associated with the Loop job created.
  • If Default agent is not assigned to the Project then, no agent will be assigned automatically and appropriate agent needs to be assigned to the Loop job.

5. The Agent window lists all the available Agents. Choose the required Agent

6. To provide the remote command to be executed for generating the looper iterations result, click on the Expression Editor arrow.

7. The Expression Editor window opens, and you can define the command here. Once you enter the expression, click Validate to verify that there are no syntax errors. If everything is correct then, a success message is displayed. 

Note: For remote command, you need to add the SFTP or FTP DataPoint to get files. These Data Points are already created at the Data Point level. For more information about Data Point, refer Working with FTP Data Point and Working with SFTP Data Point.

Step IV: Select the return type for Loop command 

Select the return type based on the value that is being returned by the command. If the command returns only single value for each iteration then select the corresponding return type from the list. If the command returns more than one value for each iteration then select array[string]/array[int] from the list. The multiple values returned for an iteration is required to be separated by a delimiter which is not part of the return value. To set the return type, select the return type from Return Type drop-down, and provide the delimiter in Delimiter text box.

Step V: Select the iteration mode 

1. Iteration mode can be set as either sequential or parallel

Option I: Sequential: Sequential mode executes the iteration one after the other. If any of the iteration execution fails then, further iterations are stopped.

Option II: ParallelParallel mode executes all the iteration together.

2. Fail if zero rows ReturnedEnable or disable failure of loop job if no records are returned by the loop command. If no rows are returned then, the jobs linked within the loop start and end job are not executed.

  • If the option is checked and the loop command execution does not return any record then the loop end job is marked as failed.
  • If the option is unchecked and the loop command execution does not return any record then the loop end job is marked as succeeded.

3. Retry Enabled: Check the retry option if you want to enable retry attempts for loop job execution.

  • No. of Retry Attempts: Specify the number of attempts to retry loop job execution if in case the job fails to execute. By default, the retry attempts is set to 2.
  • Retry Wait Time (in Seconds): Specify the duration in seconds for the job to retry next execution. By default, the duration is set to 60 seconds. If the Job fails to execute, it retries again for next execution attempt after the specified wait time. 

Use loop job parameter in the jobs

Looper Parameters can be used in the jobs and link conditions present in between Looper Start and Looper End. The Looper Parameters will be displayed in Expression Editor for defining expressions, and can also be typed in as needed. For more information about how to define the link conditions in a job, refer Working with link in Job Flow.

Use Case I: Using loop job parameter to define the name of the source file generated as part of execution of associated data flow instance. 

Step I: On Canvas, click on the job, and then under Properties, click Connections. Click 'E', that is the Extract properties of source object 'SRC_Product'.

Step II: The Extract Properties window opens. 

Specify the loop job parameter in the "value" field for the property "File Name". Here there is no expression editor so, the loop job parameter needs to be typed in. Prefix it with $LP_ as - $LP_Loop_CMD_1. During run-time, the value for property "File Name" will be replaced with the value defined for the parameter - $LP_LOOP_CMD_1. 

You can also use the pre-defined Diyotta Looper Parameters $LP_ITERATOR_ID and $LP_ITERATOR_NUMBER if required. During run-time, the $LP_ITERATOR_ID parameter resolves with a unique number for each job in each iteration, and the $LP_ITERATOR_NUMBER parameter resolves with Iteration Numbers like 1, 2, 3...etc.

Use Case II: Using Job Flow parameter in the Task Command.

Step I: On Canvas, click on the DB command job, and then under Properties, click on the Command Tab.  Open the editor for the command by clicking on the arrow.

The Expression Editor window opens.

Step II: Provide the command to be executed. For illustration, here we are checking if the file as specified in the Loop parameter exists and deleting it.

You can also use the pre-defined Diyotta Looper Parameters $LP_ITERATOR_ID and $LP_ITERATOR_NUMBER if required. During run-time, the $LP_ITERATOR_ID parameter resolves with a unique number for each job in each iteration, and the $LP_ITERATOR_NUMBER parameter resolves with Iteration Numbers like 1, 2, 3...

Use Case III: Using loop job parameter with return type as array[string] to define the name of the source file generated as part of execution of associated data flow instance. 

Considering the return-type of loop parameter is array[string] and delimiter as comma. The command in Loop job needs to be defined to return concatenated list of values separated by the desired delimiter. For example, if SQL command is used then the SQL should be similar to - Select Country||','||Product_cd from product;. Here, value for each iteration is concatenation of values of columns, Country and Product_Cd from the table Product, and separated by delimiter comma. 

The loop parameter $LP_LOOP_CMD_1 gets assigned the list of values for each iteration. Each value in the list can be referenced using the position number of the value. The position number starts from 1. Here, $LP_LOOP_CMD_1[1] will correspond to the value from column Country and $LP_LOOP_CMD_1[2] will correspond to value from column Product_cd.

Step I: On Canvas, click on the job, and then under Properties, click Connections. Click 'E', that is the Extract properties of source object 'SRC_Product'.

Step II: The Extract Properties window opens. 

Specify the loop job parameter in the "value" field for the property "File Name". Here there is no expression editor so, the loop job parameter needs to be typed in. Prefix it with $LP_ as - $LP_Loop_CMD_1 and use the index number to reference the value at a particular position. During run-time, the value for property "File Name" will be replaced with the value defined for the parameter - product_$LP_LOOP_CMD_1[1]_$LP_LOOP_CMD_1[2].dat.

Note:

  • To save the Job Flow, on the Actions menu, click Save. For more information, refer Saving Job Flow
  • To revert the changes before saving the Job Flow, on the Actions menu, click Revert. For more information, refer Reverting changes in Job Flow
  • To execute individual job in the Job Flow, on the Actions menu, click Run Job. For more information, refer Executing individual job in Job Flow.
  • To execute the Job Flow, on the Actions menu, click Run. For more information, refer Executing Job Flow.
  • Once the Job is created and the changes are saved, then, close or unlock the Job Flow so that it is editable by other users. For more information, refer Closing Job Flow and Unlocking Job Flow