 |
|
|
 | Client - Server Synchronization |  | 602SQL Server | Server Properties |  |
Some types of database applications require regular execution of certain server actions without direct input from some client. As an example, it is possible to mention regular stock control or computation of activity statistics at the end of certain period.
Such actions can be scheduled on the server. The SQL server will take care of starting these actions at the pre-determined time.
Each planned action is labeled with a unique name. The action subject matter is execution of a SQL command, typically calling a procedure defined in some application or predefined system procedure.
Server starts the scheduled action on the separate thread. Name of this thread consists of "@" character and of action name.
The action is executed with operating administrator (CONFIG_ADMIN) permissions. Therefore it is necessary to assign to operating administrator group necessary permissions for the objects and data that the scheduled action is going to process.
Up to 5 rules can be defined for starting of each action. These rules can be in two formats:
The scheduled action will not be started if the server is not running at the pre-determined time. After switching the SQL server on, the scheduled actions are started like follows:
The action will not be started if it is still running after the previous start. For evaluation whether the time for starting an action according to some rule has already arrived, the starts executed in the past according to other rules are also considered: For example, if action is started with 1 hour interval and it was started at 8 and 9 o'clock, and, according to another rule it is started always at 9:30, then the next start will take place at 10:30, 11:30 etc.
The scheduled actions that have been already started are aborted prematurely only in case of server stop. Otherwise it is assumed that actions will terminate by themselves.
CALL _sqp_create_sysext_object('_TIMERTAB');
Permission to read this table is granted to EVERYBODY group, permission to write is granted to CONFIG_ADMIN administrator group. These permissions can be granted also to other entities.
The table contains following columns
| Name | Type | Meaning |
| ACTION_NAME | CHAR(62) | action name, primary key to the table |
| SQL_STATEMENT | CHAR(100) | SQL command that will be executed |
| RESULT | CHAR(100) | a space for entering the action result (the scheduler does not process this field) |
| PRIORITY | INTEGER | priority of thread that executes an action; the thread will have decreased priority if it is <0; increased priority if it is >0, or a normal priority if it is =0 |
| DISABLED | BOOLEAN | if it is true, the action will not be executed |
| TIME_WINDOW | TIME | admissible delay when starting an action scheduled for specified time; if it is NULL, the action will be started with an arbitrarily long delay. |
| LAST_RUN | TIMESTAMP | date and time when this action was started for the last time |
| PLAN_CODE_1 | INTEGER | -1, if the action is started with the PLAN_TIME_1 interval, or a bit map of days in a week, if it is started at the specified time |
| PLAN_TIME_1 | TIME | an interval for action start or a time for start within the range of day; accuracy approximately 1 minute |
| ... | ... | ... |
| PLAN_CODE_5 | INTEGER | -1, if the action is started with the PLAN_TIME_5 interval, or a bit map of days in a week, if it is started at the specified time |
| PLAN_TIME_5 | TIME | an interval for action start or a time for start within the scope of day; accuracy approximately 1 minute |
If the table specifies a call of procedure that is stored in some application, it is necessary to prefix its name with the application name.
If the scheduler starts some action, it will overwrite the LAST_RUN field in the table. An action can overwrite the RESULT field.
Days within a week when an action should be started can be entered in PLAN_CODE_x using the sum of the following values: 1 for Sunday, 2 for Monday, 4 for Tuesday, 8 for Wednesday, 16 for Thursday, 32 for Friday and 64 for Saturday. For each day, the value is equal to 127.
If PLAN_CODE_x is equal to zero or NULL, no action start is specified.
Actions can be scheduled by modification of the table content using the SQL commands. For example, a command:
INSERT INTO _SYSEXT._TIMERTAB(ACTION_NAME, SQL_STATEMENT, PLAN_CODE_1, PLAN_TIME_1)
VALUES ('Sklad-Kontrola1', 'CALL Sklad.Kontroluj(123)', -1, TIME'04:00:00')
Provides for execution of the Kontroluj procedure from the Apl application with parameter 123 each 4 hours.
The command
INSERT INTO _SYSEXT._TIMERTAB(ACTION_NAME, SQL_STATEMENT, PLAN_CODE_1, PLAN_TIME_1)
VALUES ('Sklad-Kontrola2', 'CALL Sklad.Kontroluj(123)', 127, TIME'23:59:00')
Provides for execution of the Kontroluj procedure from the Apl each day at midnight.
The command:
UPDATE _SYSEXT._TIMERTAB SET DISABLED=true WHERE ACTION_NAME='Sklad-Kontrola1'Disables further execution of these checks. The command:
UPDATE _SYSEXT._TIMERTAB SET RESULT=textovy_popis WHERE ACTION_NAME='Sklad-Kontrola1'writes some result to the RESULT field.
An action name represents a key to the table containing action schedule. Maximum name length is twice the object name length. It is advisable to create action names like follows: connect the schema (application) name and the selected name valid within the schema. Doing that, you will achieve:
| Client - Server Synchronization | | 602SQL Server | Server Properties | |