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.

This page includes the following:

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

• Standard pages that are always available, no matter what type of object you are defining:
  • General Page
  • Version Management Page
  • Documentation Page
• Additional pages that are always available for executable objects:

  • Process Pages

    Note: SQL Jobs have an extra Child Post Process page. You build the logic of the child processes on this page.

  • Attributes Page
  • Registering External Output Files
  • Variables Page
  • Prompt Sets Page
  • Rollback Page
• The SQL page described here.

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.

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 SQLProcess pages contain an additional view called Forms, an intuitive and user-friendly way of depicting the values in a particular command. The Form view of a command can contain dropdown lists, input fields, radio buttons, and so on. For more information, see Process Pages and Forms View on the Process Page