Adding a JDBC Stored Procedure Job

To add a stored procedure job:

  1. Click Definitions > Jobs on the Navigation pane to display the Jobs pane.

  2. Click Add on the TA toolbar or right-click in either the Navigation or Jobs pane and choose Add JDBC Job from the context menu to display the JDBC Job Definition dialog.

  3. Click the JDBC Job Name field, and enter a name up to 50 characters in length for your job.

    The JDBC Job Name is an identifier for TA only. All of the other job definition information, such as Job Class, Owner, and Parent Group, is also the same as a standard TA job and is used in the same way.

    If you are putting your JDBC job into a group, note that unless the parent group selected has a JDBC adapter connection assigned, you must clear the Inherited option on the Run tab before you can choose a connection using JDBC.

  4. Choose an adapter name from the Agent/Adapter Name list.

  5. Choose a runtime user from the Runtime User list.

  6. Click the JDBC Job tab, then choose the Stored Procedure job type from the Type list.

    • If you did not click a database in the Connection Definition dialog, you must choose one from the Database drop-down menu.

    • If you did click a database in the Connection Definition dialog, it appears here on the JDBC Job tab as read-only in the Database field.

  7. Choose the procedure to execute from the Stored Procedure list when the job is run.

    You can use an asterisk (*) to limit the number of stored procedures displayed in the drop-down menu. For example, if you enter store*, only procedures that begin with store are displayed in the drop-down menu.

    Note: For each parameter of the stored procedure, click Add to display the Stored Procedure Parameter dialog.

  8. Enter the parameter value into the Value field, or click a predefined variable on the Variables button list, then click OK.

    A new row is created each time you add a value so you can add any number of required parameters for the stored procedure.

  9. Click the Enabled option to enable or disable the job definition.

  10. Click the SQL subtab to view the stored procedure.

    Note: The SQL subtab is populated only if a query has been defined to retrieve stored procedure help or text on the Options tab of the Connection Definition dialog as described in Adding a Connection Using JDBC.

    This subtab is read-only.

  11. Choose how the query results are formatted from the Output format drop-down menu:

    • XML – Writes the query results in XML format.

    • Align Columns – Displays the values in the most readable format

    • CSV Format – Separates values with commas.

    • Raw – Separates values with a user-defined character.

  12. Click the Delimiter field, and specify the custom character to use for delimiting the column data from the query results if Raw or CSV Format is selected for Output Format.

  13. Click the Include Headers option to write out the column headers of the results as well.

  14. Click the Include Warning option to include notices and warnings generated during SQL execution in the output.

  15. Check the Redirect output to file checkbox if you want the job output to be stored in a file. This redirection option is independent of the Save Output Option located on the Options tab.

    Example: If the Save Output Option is set to Discard and the Redirect output to file checkbox is selected, then the job output will be saved to the specified file while the Output tab for the job run will be empty.

    Example: If the Save Output Option is set to Append or Replace and the Redirect output to file checkbox is selected, the Output tab on the Job Activity dialog shows the redirection message.

  16. Click Append to append current output to the existing output in the output file. Click Replace to replace the existing output with current output in the output file.

    The Output Base Directory field specifies the output base directory, if configured. This is a read-only field.

    • If the output base directory is not configured, the field value is shown as “not configured” in the Output Base Directory field. In this case, the job will not execute and will have the “Error Occurred” status. The corresponding error messages will be shown in audit log files.

    • If the output base directory is configured, the base directory path is shown in the Output Base Directory field. Example base directory path: /opt/Tidal/{adapter_guid}/, where adapter_guid is the GUID of the adapter.

      The base directory path is activated by specifying a value in the sysval_longstr column of the sysval “195”. This can be accomplished by executing an update statement to the database as mentioned in these examples.

      Example: MS SQL Query: insert into sysval (sysval_id, sysval_longstr, sysval_lstchgtm) values (195, 'c:\temp\adapter\output', GETDATE()).

      Example: Oracle SQL Query: insert into sysval (sysval_id, sysval_longstr, sysval_lstchgtm) values (195, '/tmp/adapter/output', SYSDATE).

      Here, the sysval_longstr field contains the output base directory path. An optional sysval_integer field indicates whether the task GUID subdirectory will be appended to output base directory or not. If the value of sysval_integer is 1, the task GUID will be appended. In all other cases, the task GUID will not be appended.

  17. Enter the directory path relative to the output base directory in the Output Relative Directory field. Example output relative directory path: relativedir/path.

  18. Click the Output File Name field, and enter the name of the file in which the job output is stored. This file is placed in the path resulting from step 19 and 20.

    Example: Iif the Output File Name is specified as “sqljob.xml”, then the job output is stored in/opt/Tidal/{adapter_guid}/relativedir/path/sqljob.xml.

  19. Click OK.