Automation Center Documentation

Stored Procedure

From OpsWise Documentation Wiki

image:pdficon_small.gif Open as PDF

Contents

Before You Begin

To run a Stored Procedure task, you first need to create a Database Connection, which defines the information needed to locate and access the database.

Overview

The Stored Procedure task allows you to execute a stored procedure against the database specified in the task.

Two variables are supported for this task type that allow you to capture any SQLException data:

  • ${ops_sql_error_msg} is the message from the SQLException.
  • ${ops_sql_state} is a return code that indicates the outcome of the most recently executed SQL statement. It is populated from the SQLException, if any.

Creating a New Stored Procedure Task

  1. From the Navigation Pane, select Stored Procedure Tasks.
  2. From the wizard, select New. OpsWise Automation Center displays the Stored Procedure Task screen.
  3. Using the field descriptions provided below as a guide, complete the fields as needed.
  4. Click the Submit button to save the record and return to the menu, or, right-click on the title bar and select Save to save the record and remain on the current display.
  5. If appropriate, repeat these steps for any additional tasks you want to add.

Shown below is a sample Stored Procedure task screen. Task run statistics appear after the first time this task has been launched.

image:ops_Stored Procedure Task.gif

Stored Procedure Task Field Descriptions

The table below describes the fields, buttons, and tabs on the Stored Procedure task definition and task instance screens. Most fields appear on both screens; however they do not always appear at the same spot. In the latter case, the table provides a field description at the location found on the task definition screen. Some fields appear only on one of the screens. Field labels shown in bold appear only on the task instance screen and are labelled as such.

Field Name Description
Task Name Required. Name used within OpsWise Automation Center to identify this task. Up to 40 alphanumerics; variables supported. It is the user’s responsibility to develop a workable naming scheme for tasks.
Invoked by Task instance only; system-supplied. How the task instance was launched. Options:

Trigger: [Trigger Name] - The instance was launched by the named trigger. Workflow: [Workflow Name] - The instance was launched by the named workflow.

Manually Launched - The instance was launched by a user. To identify the user who launched the instance:
Task Task instance only; system-supplied.
Execution User Task instance only; system-supplied.
Instance Reference Id Task instance only; system-supplied.
Database Connection Optional. The name of the OpsWise Automation Center database connection that defines the database. Click the magnifying glass to browse for an existing database connection or add a new one.
Credentials Optional. The login credentials OpsWise Automation Center will use to access the remote machine where the database resides. See Credentials.
Maximum Rows Optional. If necessary, specify a limit to the number of rows you want returned by the Stored Procedure statement. This value overrides any value you specify in the database connection.
Credentials Variable Optional. If enabled, the Credentials field (see above) converts from a reference field (where you browse and select a record) into a text field that allows you to enter a variable. Use the format: ${variable name}. The variable must be a supported type as described in Using Variables.
Summary User-supplied description of this task.
Status Task instance only; system-supplied. See Task Status.
Status Description Task instance only; system-supplied. Provides additional information, if any, about the status of the task.
Start Time Task instance only; system-supplied. The time the task started.
Duration Task instance only; system-supplied. The amount of time the task took to run.
End Time Task instance only; system-supplied. The time the task completed.
Rows Retrieved Task instance only; system-supplied. The number of rows retrieved by the SQL procedure.
SQL State Task instance only; system-supplied.
SQL Error Message Task instance only; system-supplied. Any error messages returned by the SQL procedure.
Hold on Start If enabled, when the task is launched it appears in the Activity display with a status of Held. The task runs when the user releases it. .
Member of Groups User-defined. Allows you to select one or more Opswise groups that this task definition belongs to. Click on the lock icon to unlock the field and select groups.
User-Estimated Duration The estimated amount of time it should normally take to run this task. The value in this field is used by OpsWise Automation Center to calculate the User Estimated End Time when the task is launched. You can view this information by selecting the Task Instance record from the Activity screen.
Stored Procedure Name The name of the file containing the stored procedure being executed against the database. Variables supported.
Result Processing Specifies how OpsWise Automation Center should determine whether the Stored Procedure command failed or completed successfully. Options:
  • Skip Result Processing
  • Success Exitcode Range - The Stored Procedure command is considered completed successfully if its exitcode falls within the range specified in the Exit Codes field (see below).
  • Failure Exitcode Range - The Stored Procedure command is considered failed if its exitcode falls within the range specified in the Exit Codes field (see below).
  • Success Result Set Contains - The Stored Procedure command is considered completed successfully depending on the value in a specific database column (see Column Name, Operator and Value fields below).
  • Failure Result Set Contains - The Stored Procedure command is considered failed depending on the value in a specific database column (see Column Name, Operator and Value fields below).
Exit Codes If Result Processing = Success Exitcode Range or Failure Exit Code Range, this field specifies the range. Format: Numeric. Use commas to list a series of exitcodes; use hyphens to specify a range. Example: 1,5, 22-30.
Column Name If Result Processing = Success Result Set Contains or Failure Result Set Contains, this field specifies the name of a database column that is being checked for a specific value. Options:
  • Standard Output (STDOUT)
  • Standard Error (STDERR)
  • File
Operator If Result Processing = Success Result Set Contains or Failure Result Set Contains, this field specifies the operator being used for the comparison. Options: =, !=, >, >=, <, <=, regex.
Value If Result Processing = Success Result Set Contains or Failure Result Set Contains, this field specifies the value being compared with the Column name using the operator specified.
Auto Cleanup When data is retrieved as the result of executing a stored procedure, the data is written to a temporary table in the database. If Auto Cleanup is enabled, the data is discarded upon completion of the task. This feature is useful for testing your stored procedure or in any other situation where you do not want a copy of the retrieved data.
Late Start If enabled, and if the task instance starts after the time specified in the Late Start Time field (see below), the task instance is flagged as late. To determine whether a task instance started late, open the task instance and locate the Started Late field; the field is checked if the instance started late.
Late Start Time (hh:mm) The time after which the task start time is considered late. Use hh:mm, 24-hour time.
Late Finish If enabled, and if the task instance finishes after the time or period specified, the task instance is flagged as late. You can specify a time or duration to determine a late finish (see Late Finish Type below). To determine whether a task instance finished late, open the task instance and locate the Finished Late field; the field is checked if the instance finished after the specified time or lasted longer than expected.
Late Finish Type If Late Finish is enabled, use this field to specify whether you want to use time (see Late Finish Time below) or duration (see Late Finish Duration below) to determine whether the task instance finished late.
Late Finish Time (hh:mm) If Late Finish Type is Time, use this to specify the time after which the task finish time is considered late. Use hh:mm, 24-hour time.
Late Finish Duration If Late Finish Type is Duration, use this to specify the longest amount of time this task instance should take to run. You can specify any combination of days, hours, minutes, and seconds.
Early Finish If enabled, and if the task instance finishes before the time or period specified, the task instance is flagged as early. You can specify a time or duration to determine an early finish (see Early Finish Type below). To determine whether a task instance finished early, open the task instance and locate the Finished Early field; the field is checked if the instance finished before the specified time or did not last as long as expected.
Early Finish Type If Early Finish is enabled, use this field to specify whether you want to use time (see Early Finish Time below) or duration (see Early Finish Duration below) to determine whether the task instance finished early.
Early Finish Time (hh:mm) If Early Finish Type is Time, use this to specify the time before which the task finish time is considered early. Use hh:mm, 24-hour time.
Early Finish Duration If Early Finish Type is Duration, use this to specify the shortest amount of time this task instance should take to run. You can specify any combination of days, hours, minutes, and seconds.
Maximum Retries User-defined. The maximum number of times the system should retry this task after it has started and gone to a failed state.
Retry Indefinitely User-defined. Enabled or disabled. Indicates whether the system should continue trying indefinitely to run this task. If you enable this field, it overrides maximum retries (above).
Retry Interval (Seconds) User-defined. The number of seconds between each retry.
Current Retry Count System-supplied. Displays only for a running task instance. Displays the number of times the system has retried the task after it first went to failure status.
User Estimated End Time Task instance only; system-supplied.
Shortest Estimated End Time Task instance only; system-supplied.
Average Estimated End Time Task instance only; system-supplied.
Longest Estimated End Time Task instance only; system-supplied.
First Time Ran System-supplied. Displays after task has been run. The date and time this task first ran.
Lowest Instance Time System-supplied. Displays after task has been run. The shortest amount of time this task has taken to run.
Last Time Ran System-supplied. Displays after task has been run. The last date and time the task ran.
Average Instance Time System-supplied. Displays after task has been run. The average amount of time this task takes to run.
Number of Instances System-supplied. Displays after task has been run. The number of times this task has run.
Highest Instance Time System-supplied. Displays after task has been run. The longest amount of time this task has taken to run.
Last Instance Duration System-supplied. Displays after task has been run. The amount of time the task took to run the last time it ran.
Submit button Submits the new record to the database.
Update button Saves updates to the record.
Launch Task button Manually launches the task.
Delete button Deletes the current record.
Stored Procedure Parameters tab Allows you to add one or more parameters that will be used when the procedure executes. See Adding Stored Procedure Parameters.
Task Virtual Resources tab Allows you to create virtual resource definitions for controlling access to resources by multiple tasks. See Creating Virtual Resources.
Variables tab Displays all variables associated with this task.
Actions tab Allows you to specify actions that the system will take automatically based on events that occur during the execution of this task. Events supported are task instance status, exit codes, late start, late finish, and early finish. Actions include:
Task Instances tab System-supplied. Displays a list of all instances generated by this task.
Triggers tab Displays a list of all triggers that have been defined to launch this task. Also allows you to add new triggers. For instructions, see Triggers.
Notes tab Displays all notes associated with this task. See Runbook Notes.

Adding Stored Procedure Parameters

You can enter one or more parameters for each stored procedure.

Adding a Parameter

  1. Open the Stored Procedure task to which you want to add the parameter.
  2. Click on the Stored Procedure Parameters tab or scroll to the parameters section. The system displays a list of parameters, if any.
  3. Click the New button.
  4. Use the field descriptions provided below to fill in the screen, as shown in the following example.

    5. image:ops stored procedure parameters.gif

  5. Click the Submit button.

Stored Procedure Parameter Field Descriptions

Name Description
Parameter Position Position of this parameter within a list of parameters.
Parameter Mode Mode of this parameter. Options:
  • Input
  • Output
  • Input/Output
Parameter Type Type of parameter. Options:
  • VARCHAR
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • REAL
  • DOUBLE
  • NUMERIC
  • DECIMAL
  • DATE
  • TIME
  • TIMESTAMP
  • VARBINARY
  • BOOLEAN
Value is Null
Input Value Input value of the parameter, if any.
Description Description of this parameter.

Deleting a Parameter

Display the parameter you want to delete and click the Delete button. Or, you can delete one or more parameters as follows:

  1. From the parameters list, click on the box associated with the parameter or parameters you want to delete.
  2. From the Actions on selected rows menu, select Delete.

Viewing a Parameter

  1. From the parameters list, scroll to the parameter you want to read.
  2. Click the underlined field displayed in the leftmost column. The system displays the contents of the parameter.

Specifying When a Task Runs

You can run the task as part of a workflow, specify triggers that run the task automatically based on times or events, or run the task manually.

Monitoring Task Execution

You can monitor all system activity from the Activity screen and can view activity history from the Activity History screen.

Retrieved from "http://www.opswisesoftware.com/documentation/index.php/Stored_Procedure"
Personal tools