Specifying SQL Command at Runtime

This procedure describes how to configure a dataflow to support runtime options for SQL Command and also how to specify the job executor arguments to do this.

  1. Open the flow in Spectrum Enterprise Designer.
  2. If you want to configure runtime options for a stage in an embedded flow, open the embedded flow.
  3. Click the Dataflow Options icon on the toolbar or click Edit > Dataflow Options. The Dataflow Options dialog box appears.
  4. Click Add. The Define Dataflow Options dialog box appears.
  5. Expand the SQL Command stage.
  6. Select a SQL Command option. It can be PreSqlCommand, SqlCommand, or PostSqlCommand.
    PreSqlCommand
    SQL statements that you want to execute before the records coming into the stage are processed. These SQL statements are executed once per run after the dataflow starts running but before the SQL Command stage processes the first record.
    An example use of pre-SQL would be to create a table for the records that will be processed.
    SqlCommand
    SQL statements you want to execute for each record in the dataflow.
    PostSqlCommand
    SQL statements that you want to execute after all the records are processed. These SQL statements are executed once per run after the SQL Command stage is finished but before the dataflow finishes.
    An example use of post-SQL would be to build an index after processing the records.
    The selected SQL Command option name is displayed in Option name and Option label fields. This is the option name that will have to be specified at run time in order to set this option.
  7. Enter a description of the option in the Description field.
  8. In the Target field, select the option Selected stage(s).
  9. If you want to limit the values that can be specified at runtime, edit the options in the Legal values field by clicking on the icon just to the right of the field.
  10. If you want to change the default value, specify a different value in the Default value field.
    Note: For a service, you can only modify default values before exposing the service for the first time. Once you expose the service you can no longer modify default values using Spectrum Enterprise Designer. Instead, you must use Spectrum Management Console. For more information, see Specifying Default Service Options.
  11. Click OK.
  12. Continue adding options as desired.
  13. Click OK in the Dataflow Options dialog box when you are done adding options.
  14. Save and expose the dataflow.
  15. Create a text file containing the SQL statement you want to use at runtime.
    The text file may look like this:
    SqlCommand = UPDATE CustomersSET 
ContactName='Alfred Schmidt'
City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';

    In this example, SqlCommand is one of the SQL Command stage's option names.

  16. Use the -o argument when running a job executor from command line. 
    java -jar jobexecutor.jar -h "noipa019sh-l1" -u "admin" -p "admin" -s "8080" -o "options.txt" -j "FetchOracleData" -w
    The filename (options.txt) specifies a name of the text file that you created in step 14.
    For more information, see Running A Job from the Command Line