Defining an Embedded Query database job

This procedure describes how to define a database job by creating a query or editing a predefined query.

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 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 Embedded Query database 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 Embedded Query.
4. Do one of the following:
   • To create an SQL query statement, in the Query Text field, type the required query.
   • To edit an existing SQL statement, do the following:
     1. Click Insert from File, and select the required SQL file.
     2. In the Query Text field, edit the query, or click Query Designer, as described in Defining a database job with the Query Designer.

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

5. Click More Options.
6. To automatically commit queries, select the Auto Commit checkbox.
7. 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.