Executing SQL Commands Before or After a Dataflow

The Execute SQL activity performs operations on database at any point during a process flow. This activity allows you to run the SQL statements both before and after the execution of Spectrum Technology Platform dataflow or an external program. For example, the Execute SQL activity can be used to delete indexes before the execution of a Spectrum Technology Platform dataflow and to create indexes again after the execution of the dataflow. To execute SQL statements using Execute SQL activity, you must create a process flow.

Note: Please refer to Spectrum Dataflow Designer Guide for instructions on how to create and schedule a process flow.
  1. Drag the Execute SQL activity to the canvas.
  2. Double click the Execute SQL activity.
  3. Select a database connection you want to use.
    If you need to make a new database connection, or modify or delete an existing database connection, click Manage.

    If you are adding or modifying a database connection, complete these fields:

    Connection name
    Enter a name for the connection. The name can be anything you choose.
    Database driver
    Select the appropriate database type.
    Connection options
    Specify the host, port, instance, user name, and password to use to connect to the database.
  4. Write the SQL statement in the SQL statement(s) box.
    By default, the Terminate flow on error option is checked which means that the process flow will be terminated if an exception occurs. If the option Terminate flow on error is unchecked and an exception occurs, the process flow will not stop and the exception will be logged in the server logs.
  5. Add the action you want a process flow to perform.
    You can add a job by dragging a job's icon to the canvas, or add an external program by dragging a Run Program icon onto the canvas.
  6. Connect the two activities.
  7. Add additional Execute SQL activity as needed.
    Refer step 2 to step 5 for performing actions on Execute SQL.
  8. When you have added all the jobs, Run Program and Execute SQL activities you want to execute in the process flow, drag a Success activity onto the canvas and connect it to the last activity in the process flow.
  9. Run the Process flow.