SQL Jobs

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:

Standard pages that are always available, no matter what type of object you are defining:
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
- Output Pages
- Variables Page
- Prompt Sets Page
- Rollback Page
The SQL page described here.

To Define an SQL Job

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
Specify the data in the Job Report section. For more information, see Job Reports.
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.