Sybercron Changes

Introduction

Sybercron now tracks the execution of your stored procedures. If a stored procedure does not complete successfully or its return result is less than zero, Sybercron will send E-mail to the "sender" notifying them that the procedure did not complete successfully. In addition, if the return result is one of the SQL server reserved values and its meaning is known, your E-mail will indicate that as well.

A stored procedure can also fail for more serious reasons than just a negative return result: Sybercron was unable to get a stream socket to Sybernet, Unix reboots, or the SQL server crashes. Sybercron will detect this and send you E-mail.

Entries in the CRON table that are submitted for execution are now written to the table CRON_HISTORY. It is now possible to look at all executions of your procedure and determine when they were started, finished, and what their result was. This table resembles the CRON table with the addition of some new columns.

The STATUS column in CRON_HISTORY will contain one of the following values:

WAITING (indicates a job has been Submitted to Sybercron, but has not started runnning)
RUNNING (this stored procedure is now running in Sybase)
COMPLETED (the stored procedure completed without error)
SUSPECT (the stored procedure failed to run, failed while running, or returned a negative return result)

All entries marked as SUSPECT causes E-mail to be sent to the "sender."

The SENDER field is no longer optional and will default to your E-mail address (based on entries in CRON_EMAILS).

Sybercron Monitor

A new stored procedure (sp_html_cron_monitor) now allows you to monitor your stored procedures. The display updates itself every 60 seconds if nothing is running. If one or more procedures are WAITING, RUNNING or QUEUED, the display updates itself every 10 seconds. When invoked, 12 entries are displayed with the following columns:

STATUS
The STATUS column is a drop-down select list which is updated automatically each time the display is refreshed and reflects the current status of that procedure.
You can select a SCHEDULED procedure and change its STATUS to QUEUED. This will insert this procedure at the front of the queue and make it the next procedure to execute. QUEUED entries are just like SCHEDULED entries except their TIMESTAMP has expired (TIMESTAMP <= getdate()), a condition which means they are ready to run as soon as Sybercron "wakes up" and selects it from the CRON table.
You can select a RUNNING procedure and change its STATUS to KILL. This will, of course, kill that stored procedure. Note: If you're going to kill something, make sure it's the one you want to kill and be quick about it: a process ID can be reused almost immediately by the SQL Server.
Any other combination (for example, changing WAITING to SCHEDULED) is not allowed.
For a description of status values, see the section on the Normal Flow of Execution (below).
PROCNAME
The name of your stored procedure. For RUNNING entries, the current command name (from sysprocesses) is appended to this name.
TITLE
The title that you have specified. For E-mail, TITLE is also the subject of your mail.
PLATFORM
The name of the PLATFORM that Syberron is to run from (SPOCK,KIRK,ASTRO).
RESULT
The return result of your stored procedure. WAITING and RUNNING procedures will have a value of NULL (since they haven't returned). SCHEDULED and QUEUED entries will be blank in this field.
DATE
For QUEUED and SCHEDULED entries, this is the TIMESTAMP that it will be executed. For WAITING and RUNNING entries, this is the TIMESTAMP when it started. For COMPLETED and SUSPECT entries, this is the TIMESTAMP when it finished.
CPU
This field contains the total CPU time in ticks for COMPLETED and SUSPECT entries or the total accumulated CPU time in ticks for RUNNING entries. The field is blank for QUEUED and SCHEDULED entries.
TIME
For COMPLETED and SUSPECT entries this column represents the elapsed time of your stored procedure. For RUNNING and WAITING entries it represents the amount of time it has been running. For QUEUED entries, it represents the amount of time (as a negative value) that this procedure has been waiting to run. And for SCHEDULED entries it represents the amount time left before this procedure is selected for execution.

Normal Flow of Execution

The STATUS column is probably the one you are most interested in. Here is the normal flow of STATUS values as they are tracked by the Sybercron Monitor:

A stored procedure which is scheduled to be executed on some date and time in the future is marked as SCHEDULED.

A stored procedure is ready to run; its TIMESTAMP is less than the current time, but Sybercron is still in a "sleep" state or other stored procedure are running and there are no more available processes that can be forked at the moment.

Sybercron has now selected this procedure. It will be executed as soon as it is handed off to another process which is forked by Sybercron. You probably may never see a process go to the WAITING state since this occurs so fast.

Your stored procedure is now running in Sybase.

Congratulations! Your stored procedure completed successfully; however, if its return result is less than zero, its STATUS changes to SUSPECT and E-mail is sent to the "sender."

Oops! Something went wrong. Either the stored procedure completed successfully with a negative return result or something serious is wrong. The latter would occur if the SQL Server is restarted, Unix is rebooted, or Sybercron had trouble procuring a socket to Sybernet. In either case, an appropriate E-Mail message is sent to the "sender."

The Monitor Window

The default monitor window displays the last 3 COMPLETED or SUSPECT entries, all WAITING or RUNNING entries, and the rest filled with the next QUEUED and SCHEDULED entries.

The buttons at the bottom of the screen allow you to scroll forward and backward through this display; for example, clicking the Backward button shows more COMPLETED entries while clicking the Forward button shows more SCHEDULED entries. The Refresh button resumes the default display. Regardless of the display you choose, WAITING and RUNNING entries are always displayed (when available).

Changing the STATUS from COMPLETED to SUSPECT (because the return result was less than zero) or from RUNNING to SUSPECT (because something serious has happened) is done in Sybase and not by Sybercron (since Sybercron could be dead at this point). If the SQL Server is dead, this won't occur until it is restarted (so don't expect any E-mail until it is).