Job Flow sql parameters are used to define parameters as result of the sql query. These parameters values can be used across all jobs in the Job Flow to parameterize various properties in themThey can also be used in expressions defined for other parameters in job flow.

It is mandatory that the result of the query should have two columns. The first column represents the key, and the second column represents the value corresponding to the key. The query can return single row or multiple rows and each row in the result should be identifiable by a unique key name.

You can create and use Job Flow sql parameters in the following way.

Step I: Add or Copy a Job Flow sql parameter

To add a Job Flow SQL parameter, on the Job Flow Properties menu, click SQL Parameters. Then, to add a new Job Flow SQL parameter, click on Click Hereor click Paste to paste the parameter you have already copied.

Note: To get to the Properties menu, either click on the empty area of the Job Flow canvas, or click on the Job Flow name in the search jobs drop-down.

Note: Job Flow slq parameter can be copied across or within same Job Flows. To copy a Job Flow sql parameter, select the parameter and use the Copy action to copy the parameter.

Step II: Provide the name, datatype and the data point corresponding to the database where sql needs to be executed for the parameter.

The canvas will show the parameter fields.

1. Click on the param name text box, if you want to modify the name. The names for each job flow parameter in the job flow has to be different.

2. Depending on the return value, specify the data type, precision and scale.

3. To specify the data point, click the arrow under the Database field. 

Note: Following operations are allowed on the Parameter entries: Add, Cut, Copy, Paste, Up, Down, Delete, and Search.

4. The window displays the list of Data Points with the Database/schema defined in them. Select the Database where you want to execute the sql query.

5. The selected Data Point and Database is displayed in corresponding fields against the parameter.

Step III: Define the dependency to execute the sql param on jobs within the job flow.

By default the dependency is set as "Initialization" of the Job Flow. This means that the sql query associated with the sql parameter will be executed as the part of Job Flow initialization which happens before start of execution of any job in the Job Flow.

To set the dependency on a job in the Job Flow, follow below steps.

1. From the drop-down, under the field "DependentJobName" corresponding to the the sql parameter, select the name of the job on which the execution of the sql parameter should be dependent on.

2. Select the Job order based on the dependent job, as to when the execution of the sql parameter should happen. This could be Before or After.

    • If Before is selected then, the sql parameter will be executed just before the start of the dependent job.
    • If After is selected then, the sql parameter will be executed after the successful completion of the dependent job.

Step IV: Provide the sql query corresponding to the sql parameter.

1. To open the Editor, under the SQL field, click on the arrow

2. This opens the Expression Editor window. You can provide the sql query in editor which would return the result in two columns - key and value. The query can also include Project Parameter, Job Flow parameters, other Job Flow sql parameters and Diyotta System Parameters.

Once you enter the sql query, click Validate. If the syntax format is correct, a success message is displayed.

Here for example, the sql query is defined to pick the data in the column File_name from table control_table by filtering on start_date and country_cd depending on the value of job flow parameters - start_date and country_cd. The query returns a single row with two columns. The first column represents the key name - filename and the second column represents the value for this key.

3. Optionally specify the default value that needs to be considered if the sql query does not return any value. If you want to modify the default value, then click on the default field text box and enter the new value.

The sql query can include other parameters such as Job Flow Parameters, Project Parameters, and Diyotta System Parameters. For more information, refer Working with Job Flow parametersWorking with Project Parameters, and Working with Studio System Parameters.

Step V: Use the Job Flow sql parameter to parameterize different properties in the Job flow, or the Jobs in it.

  • In an expression editor of jobs and options in the Job Flow, the Job Flow sql parameters can be selected from the Job Flow sql parameters list in the parameters section.
  • When Job Flow parameter needs to be specified in fields, where expression editor is not available, then type in the Job Flow sql parameter name with $FS_ prefixed.

Use Case I: Using Job Flow sql parameter to define the name of the target file generated as part of execution of associated Data Flow instance. For illustrating the use of Job Flow sql parameter in the Job Flow, refer the use cases below.

1. On Canvas, click on the job, and then under Properties, click Connections. Click 'L', that is the Load properties of target object 'TGT_SRC_DEPARTMENT'. 

2. The Load Properties window opens.

Specify the Job Flow sql parameter in the "value" field for the property "File Name". Here there is no expression editor so, the Job Flow sql parameter needs to be typed in. Prefix it with $FS_ as - $FS_file_properties. Specify the key name along with the Job Flow sql parameter name to pick the value corresponding to it during execution - $FS_file_properties.filename. During run-time, the value for "File Name" property will be replaced with the value returned by the sql corresponding to the Job Flow sql parameter - "file_properties" and the key - "filename".

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

1. On Canvas, click on the task command job and then under Properties, click on the Command Tab.  Open the editor for the command by clicking on the arrow.

2. The Expression Editor window opens. Provide the command to be executed. For illustrating, here we are checking if the file as specified in the Job Flow sql parameter exists and deleting it.

Note: The expression editors have a section to display all the applicable parameter. To list all the Job Flow sql parameters, click on the Job Flow SQL Parameters.

Click on the Job Flow SQL Parameter name to use it in the expression. Specify the key name along with the Job Flow sql parameter name to pick the value corresponding to it during execution - $FS_file_properties.filename. During run-time, the parameter will be replaced with the value returned by the sql corresponding to the Job Flow sql parameter - "file_properties" and the key - "filename".

Note: 

  • The sql defined for the sql parameter can return multiple rows with each row identified by unique key name - first column. To use value corresponding to a key, specify the job flow sql parameter along with the key as illustrated in the use cases above. The sql returning multiple rows can be defined in multiple ways.

Example I:Select 'key1', <column name> from <table name>
union
Select 'key2', <column name> from <table name>
union
Select 'key3', <column name> from <table name>

Example II:Select <key column name>, <value column name> from <table name>

  • The Job Flow sql parameter is executed in the order set in the list of Job Flow sql parameters. If an already defined Job Flow sql parameter is used in the sql query of the Job Flow sql parameter, then it is mandatory then the used Job Flow sql param be above in the list. For example, if below are the Job Flow sql parameters defined and $FS_File_Name is used in $FS_File_Name_Department then, it is mandatory that $FS_File_Name be above $FS_File_Name_Department in the list.

  • If the sql parameter name ($FS_****) used anywhere in the job flow or associated jobs does not match any of the job flow parameters defined in the job flow then, during the execution of the job flow these references ($FS_****.<key>) will be replaced by default value of -1.
  • If the sql parameter name ($FS_****) used in the job flow or associated jobs match any of the job flow parameters defined in the job flow, but the "key" referenced along with it does not exist in the result of the query then, during the execution of the job flow these references ($FS_****.<key>) will be replaced by default value of NULL.