SQL Jobs

SQL Jobs start and monitor processes in the SQL target systems. Automic Automation Your system is connected to MS SQL via the Agent. The Agent sends the commands from the Client to MS SQL without interpreting or transforming them.

Prerequisites

To create and execute SQL Jobs, the following must be true:

  • An SQL Agent is available and active
  • One of the following is available

    • A Login object that is assigned to the Agent
    • A Connection objects that is assigned to the SQL Job

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

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 more information about the integration between the Automic Automation and SQL, see AE and Databases.

For more information, see:

This page includes the following:

SQL Commands

SQL Jobs are supplied with sets of predefined commands. A command is a group of SQL-specific strings or functions. You build the logic of a SQL Job on its Process page by adding commands to it.

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.

Defining SQL Jobs

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

To Define an SQL Job

  1. 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 object on the Attributes . 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

  2. Specify the data in the Job Report section. For more information, see Job Reports.

  3. 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: