Defining a database job with the Query Designer

This procedure describes how to define a database job with the Query Designer, which acts as a wizard to build an embedded query. You can also write an embedded query and then use the Query Designer to finish the statement or vice versa. At any point in the query, you can use the Query Designer to add to the statement.

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 a database job with the Query Designer:

1. Complete steps 1-4 in Defining an Embedded Query database job.
2. In the Filter field, type <tableNameOrViewName>* or <partOfTableOrViewName>* and click Load.

   The generated items appear in the Available Objects table.

3. Select the required table or view and click .

   The selected items appear in the Selected Objects table.

4. In the Relations area, click .
5. To create a relationship between two fields, from the Column drop-down lists, select the field names.
6. In the Output Definition area, click .
7. For each field name, apply output definitions as required.

   The SQL query statement appears in the Statement area.

8. To view a sample output of the query, click Preview and then click Insert.

   The query statement appears in the Query text field.

9. Click More Options.
10. To automatically commit queries, select the Auto Commit checkbox.
11. 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 Sysout.
    • 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.