SQL Jobs

SQL Jobs start and monitor processes in the SQL target system. Like all other job objects (JOBS), SQL jobs can run independently or they can be added to a group (JOBG) or to a Workflow. For details on the integration between the Automation Engine and SQL please refer to AE and Databases.

This page includes the following:

Prerequisites

Your Automation Engine system is connected to MS SQL via the Agent The Agent sends the commands from the client without interpreting or transforming them to MS SQL. To be able to create and execute SQL jobs from within the Automation Engine, the following prerequisites must be met:

They provide the credentials for the agent to connect to MS SQL.

SQL Commands

SQL Jobs in the Automation Engine are supplied with sets of predefined commands. A command is a group of SQ-specific strings or functions. You build the logic of a SQL Job on its Process page by adding commands to it. For more information, see Process Pages.

User-Friendly Scripting Using Forms

The script editor of the Process page in a SQL Job has two views. The usual one that is available for all Jobs, and the Form view. The Form view is an intuitive, user-friendly way of depicting the values you must enter for a particular command. The Form view of a command can contain dropdown lists, input fields, radio buttons, and so on. For more information, see Forms View on the Process Page.

Defining SQL Jobs

An SQL Job definition is made up of the following pages:

Prerequisites:

To Define an SQL Job

  1. In the Process Assembly perspective click Add Object.
  2. On the dialog scroll down to expand the Job (JOBS) list and select SQL Job. Click Add.
  3. On the Object Name dialog enter the name of the SQL job object and click OK.
  4. Specify the data in the Connection section:

    • Connection Object

      The object that contains the parameters that will be used to connect to the database.

      Important! The database types of the Agent and of the Connection object must match, otherwise the Job fails.

      If you select a Connection object here, the server and database parameters defined in it are used with this Job and the next two fields (Server:Port and Database) are not available.

      When defining the SQL Job object, you can assign it a Login (LOGIN) object on the Attributes Page. If you do not do so, the login information specified in the Connection object will be used.

      Notes:

      • Specifying the login information is mandatory; the job will abort if you assign neither a Login nor a Connection object.
      • When a Connection object of type "Generic JDBC" is used in an SQL job with a database agent that is configured as "GENERIC", the first usage of the agent defines the database vendor to which the agent is assigned to. Once assigned, the agent cannot be used for other JDBC vendors until it is restarted.
    • Server:Port

      Visible if you have selected an entry in Connection Object.

      Name of the computer on which the database has been installed.

      Allowed format: Server name:Port

      The standard ports are:

      • MS SQL Server: 1433
      • Oracle: 1521

        It is not necessary to specify a server for jobs that will be processed in an Oracle database in RAC; the Oracle tnsnames.ora file already contains the relevant connection data.

      • DB2: 50000
      • MySQL: 3306

      For Informix it is required to specify the port.

    • Database

      Visible if you have not selected an entry in Connection Object

      Name of the database.

      For Informix, the name of the server and of the database must be separated by a slash. For example, ol_ids_1110_1/ang

  5. Specify the data in the Job Report section, see Job Reports.

  6. Specify the data in the Result Table Settings section.

    The results of job SQL executions are written to user-friendly reports. In this section you define how you want those reports to look like:

    • Show

      Select the report display options:

      • Headlines in a table

        The reports to show the headlines

      • NULL for empty strings

        The reports to display NULL whenever an empty string is returned.

    • Remove Line Breaks

      Removes line breaks in columns.

    • Column Separator

      Character that should be used to separate columns in the results of SELECT statements.

    • Substitute Character

      Alternative character. If the character specified as column separator is used within a database field, the substitute character specified here is used.

    • Maximum Table Rows

      This limitation applies to each SQL statement. If the result of an SQL statement contains more lines than the ones specified here, they are truncated.

      The number of lines is not limited if "0" is specified here.

    • Maximum characters in column

      The content of a database field is truncated if it contains more characters than those specified here.

      Database fields whose contents were specified as numbers or timestamps are not truncated.

      Note: There are limitations to the PREP_PROCESS_REPORT script function when the report is read column by column.

See also: