Defining an SQL Script job

This procedure describes how to define a database job with a script that is stored on a file system.

NOTES:

• Control-M for Databases does not support conversion of database separators between different database types. The following list the database separators for each database:
  • Oracle, PostgreSQL, and DB2 statement separators are defined as ;
  • Oracle block separator is defined as /
  • DB2 block separator is defined as @

    A block separator must appear on a new line at the end of each block.

• Control-M for Databases only supports standard SQL (ANSI SQL-92). Proprietary commands of command line programs like SQLPLUS, ISQL, OSQL, PSQL or SQLCMD are not supported.

Before you begin

Ensure that you have met the following requirements:

• A configured database connection profile, as described in Connection profile management.
• Successful completion of Accessing the Database job properties pane.

To define an SQL Script job:

1. In the General tab, from the Connection Profile field, click .

   The Select Connection Profile dialog box appears.

2. Select the connection profile that you want to use and click OK.
3. From the Execution Type drop-down list, select SQL Script.
4. In the Name field, click Browse.
5. Browse for the required SQL script, and click Open.

   To retain comments in an SQL Script, see Retaining comments in an SQL Script and Embedded Query.

6. If there are script variables for this script, in the Script Variables table, click and type the variable and its value.

   If the script content is select count(*) from $1, then $1 is the variable that requires a value in the Scripts Variables table.

   Variables and the conventions of the SQL script are not displayed in the Control-M for Databases job properties pane.

7. Click More Options.
8. To automatically commit queries, select the Auto Commit checkbox.
9. In the Job Output option area, select one or more of the following:
   • Append execution log to Job Output: Shows the Environment information, Request statement, and Job statistics in the Output.
   • Append SQL output to Job Output: Shows the job output in a Text, XML, CSV, or HTML format.

     From the Output format drop-down list, select an output format.

     If you select CSV output format, define a CSV separator in the CSV Separator field. The CSV separator enables you to separate your output data with specific delimiter characters.

   For more information about defining a job in the job properties pane, see Control-M User Guide.