DB Command job is used to execute custom SQLs at runtime in Job Flows. The SQLs can be run on supported databases such as Netezza, Teradata, Oracle, DB2, Hadoop, MSSQL and many more. Most common use of DB Command job is to perform operations on a single or multiple tables or database before or after the data flow completes.

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

To create the DB Command Job, follow the below steps. 

Step I: From the DB Command under Jobs menu, drag and drop the database for which you want to create the DB Command. Here for example, we have chosen PostgreSQL.

Step II: The window displays the list of PostgreSQL Data Points and the database/schema defined in themSelect the Data Point and the database/schema combination where you want to execute the SQL defined in the DB Command.

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.

Note: To create a Data Point, refer Working with Data Point.

Step III: Provide the General details of Job.

On Canvas, select the DB command job, and then under Properties, provide the General details. 

Name - The Name field displays the name of the job. To edit the Job name, click the arrow next to the name field.

Description - In the text box, you can provide a description and is optional.

Native Type : Specifies the database type for DB Command job.

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.

Step IV: Optionally specify the retry attempts.

Under Properties, select the Properties tab to enable retry attempts for DB command job execution.

Retry EnabledCheck the retry option if you want to enable retry attempts for DB command job.

  • No. of Retry Attempts: Specify the number of attempts to retry DB command 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.

Step V: Provide the SQL to be executed.

Under Properties, select the Command tab. You can either run the SQL from a file, or input the SQL in the Expression Editor. 

Option I: Input DB Command in Expression Editor. 

1. Next to the DB Command field, click the Expression Editor arrow.

2. 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 syntax and validity of functions used in the SQL, defined for the DB Command job, should be validated by the user before execution. Any issues with the SQL will result in failure during runtime.
  • Ensure there are no comment blocks included in the SQL provided in the DB command job.
  • As a reference, the expression editor displays all the data objects of the same type as the data point selected for the DB Command job. The list of data objects can be seen by selecting Data Objects from the drop down.
  • The expression editor also displays the database functions corresponding to the data point selected for the DB Command job. The list of functions can be seen by selecting Functions from the drop down. The functions that can be used in the SQL is not limited by the list shown.
  • The SQL defined in DB Command job can include parameters, reusable expressions, Runtime status and Runtime statistics.
  • 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 Parameters, Working with Job Flow SQL Parameters, Working with Project Parameters, Working with Studio System Parameters.
  • Expressions - Displays the reusable Diyotta expressions. For more information, refer Working with Expressions.
  • Runtime Job Status and Statistics It consists of Runtime Status and Runtime Statistics of Job. You can use Runtime Status and Runtime Statistics to get particular details of Job. For more information, refer Working with runtime status and statistics.

Option II: Run DB Command from File. 

1. Select the Run DB Command From File option, and input the DB Command File Path, File name and the Output file.

DB Command File Path: Specify the path of file which contains the SQL.

DB Command File Name: Specify the name of the file which contains the SQL.

For Oracle DB Command type, you can choose if you want to execute the DB Command as SQL or Procedural SQL BLOCK. For both SQL and Procedural SQL BLOCK, you can either run the command from a file, or input the command in the Expression Editor.

Note: Optionally provide the below options - 

  • Continue execution on failure - 
    • In case you want to execute multiple SQL Queries in DB Command, then you can select this option to continue the execution of SQL queries even though any of the query fails to execute successfully.
    • For Oracle DB Command type, If you choose Procedural SQL Block, then the option 'Continue execution on failure' is not available and you cannot execute multiple Queries in DB Command.
  • DB Command Output File - Provide the path and the name of the file where the result of the SQL execution should be written to. DB Command job is created with default output file. This can be modified as needed.

Step VI: Optionally change the Data Point

1. Under Properties, click Data Points. The Data Point and/or database/schema assigned to the DB Command job can be changed if needed. Next to the Data Point name, click Change

2. The window lists all the available Data Points. Choose 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 data point as required.

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