There are multiple ways to create a new job:
The Job Editor's dialog box will be opened. Enter a logical job name - the name under which the job will be identified in the Object Explorer and all selection lists. Choose the Job Type. There are two types of jobs: OS Command and SQL Script.
The OS Command Job allows you to execute operating systems command or shell script via Telnet or SSH protocol. When executing a job, the Sentinel server automatically establishes a connection and logins to the host specified in the job and then executes a specified command or script using one of above mentioned protocols. The text output produced by the command execution is captured and stored in the job history log in the Sentinel's repository. In addition, the output is sent in real-time to the connected Server Studio JE client that requested this job execution.
Important When defining the OS command, you have to make sure that it does not require any interaction with the user - i.e. prompting for any input parameters. Once launched, the command should be executed to the end. If the command, such as Informix ONTAPE utility, needs some input, you should develop a wrapper shell script that calls the command and feeds it with all required input. For example, you can redirect a command's input stream to a text file and put all required responses to the command's prompts into this file. In addition, you should always provide a reasonable time out, if you know that there is a chance that the command might prompt for some input. When the time out is provided, Sentinel automatically breaks the command execution after time out expires and the command is not finished yet. For example, if you know that your backup job usually takes not longer than 1 hour, you should specify 2 hours time out interval. When the command times out, the job failure alert is automatically sent to the user who launched the job's execution.
In case of normal (not timed-out) command's termination, you should provide a means for Sentinel to recognize if the job's execution was successful or not. Usually each command provides some type of text output at the end, which identifies either command's successful completion or some type of an error. When defining the OS Command job, you have an option to enter either success text pattern or failure text pattern. By default, if no status text patterns are provided, Sentinel considers a job completed successfully if it did not time out and returned to the operating system prompt. For example, ONTAPE utility displays text ?Program over' when it terminates successfully. If your command does not provide a standard text message for either success or failure situation, you can develop a wrap shell script, that analyzes the status of the command execution and provides some standard text output pattern that can be used when defining OS Command Job. The status text patterns are case-sensitive.
Tip Always test a defined job interactively by executing it in Server Studio JE UI and viewing the produced status and text output before using it as a response to a monitor alert. To test a defined job, select it under the Jobs node in the Object Explorer and choose Execute from the shortcut menu. Press the Test button to make sure that the telnet or SSH connection is successful. This operation does not actually execute the job command; it only tests if the required connection can be established with the specified host and port name.
You can define a job as SQL script. The script can consist of any number of valid SQL statements separated by semicolon (?;) character. It can also contain stored procedure calls. An example of SQL script job is UPDATE STATISTICS command.
Tip You can also use a stored procedure to execute an operating system command at the server host if for some reason Telnet or SSH protocols do not work for your system.
To define the job schedule switch to Schedule tab.
The scheduler has the following options:
The SPL SYSTEM statement can be used from a stored procedure to execute an OS command, as the following syntax examples show:
SYSTEM "OS command"; or
SYSTEM variable_name
If the supplied expression is not a character expression, an expression is converted to a character expression before the operating-system command is made. The complete character expression passes to the operating system and executes as an operating-system command. The operating-system command that the SYSTEM statement specifies cannot run in the background. The database server waits for the operating system to complete execution of the command before it continues to the next procedure statement. The procedure cannot use a value or values that the command returns.
If the operating-system command fails (that is, if the operating system returns a nonzero status for the command), an exception is raised that contains the returned operating-system status as the ISAM error code and an appropriate SQL error code. In DBA- and owner-privileged procedures that contain SYSTEM statements, the operating-system command runs with the permissions of the user who is executing the procedure.
When the operating-system command that SYSTEM statement specifies is executed, no guarantee exists that the environment variables that the user application set are passed to the operating system. To ensure that the environment variables that the application set are carried forward to the operating system, enter a SYSTEM command that sets the environment variables before you enter the SYSTEM command that causes the operating-system command to execute.
The following example shows a SYSTEM statement in a stored procedure. The SYSTEM statement in this procedure causes the UNIX operating system to send a mail message to the system administrator.
CREATE PROCEDURE sensitive_update()
.
.
.
LET mailcall = 'mail headhoncho < alert';
-- code that evaluates if operator tries to execute a
-- certain command, then sends email to system
-- administrator
SYSTEM mailcall;
.
.
.
END PROCEDURE; -- sensitive_update
You can use a double-pipe symbol (||) to concatenate expressions with a SYSTEM statement, as the following example shows:
CREATE PROCEDURE sensitive_update2()
DEFINE user1 char(15);
DEFINE user2 char(15);
LET user1 = 'joe';
LET user2 = 'mary';
.
.
.
-- code that evaluates if operator tries to execute a
-- certain command, then sends email to system
-- administrator
SYSTEM 'mail -s violation' ||user1 || ' ' || user2
|| '< violation_file';
.
.
.
END PROCEDURE; --sensitive_update2
The following example shows a SYSTEM statement in a stored procedure. The first SYSTEM statement in this procedure causes the Windows NT operating system to send an error message to a temporary file and to put the message in a system log that is sorted alphabetically. The second SYSTEM statement in the procedure causes the operating system to delete the temporary file.
CREATE PROCEDURE test_proc()
.
.
.
SYSTEM 'type errormess101 > %tmp%tmpfile.txt |
sort >> %SystemRoot%systemlog.txt';
SYSTEM 'del %tmp%tmpfile.txt';
.
.
.
END PROCEDURE; --test_proc
The expressions that follow the SYSTEM statements in this example contain two variables, %tmp% and %SystemRoot%. Both of these variables are defined by the Windows NT operating system.