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.

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:

• An SQL Agent is available and active

• One of the following is available

  • A Login (LOGIN) that provides the necessary credentials for the job to be executed
  • A Connection (CONN) connection that is assigned to the SQL Job

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:

• 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
  • Output Pages
  • Variables Page
  • Prompt Sets Page
  • Rollback Page
• The SQL page described here.

Prerequisites:

• The SQL agent is up and running and it contains the necessary rights in the client in which you are defining the SQL job.
• A Connection (CONN) object has been defined that provides the connection data to the MS SQL database. This allows the Agent to login into the database.

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:

• Jobs (JOBS)
• JCL for SQL