Perspectium DataSync Agent provides four types of scheduling:

  1. Straight Forward Polling Interval

  2. Continuous Listener

  3. On Demand Execution

  4. Cron Scheduling

This section outlines how the <schedule> directive can be used to schedule either a sharer or a subscriber task. The actual schedule placed within the <schedule> tags is referred to as a pattern.


Polling Interval


This will be defined in within the <polling_interval> directive. A polling interval within the task will take precedence over one outside of the tag, so you can have multiple tasks all run at different intervals. There are different configurations you can perform in conjunction with polling interval as well.

(info) NOTE: The value needs to be 5 seconds or greater. If the value is set to less than 5 seconds, it will default to 5 seconds.

Polling Example:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <polling_interval>30</polling_interval>
    	<subscribe>
	    <task>
                <!-- Default to 30 second polling interval -->
	        <task_name>Subscribe_1</task_name>	        	
   	        <message_connection password="password" user="user">https://user.perspectium.net</message_connection>
             	<instance_connection password="password" user="user">https://user.service-now.com</instance_connection>
            	<handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                    .
                    . 
                    .
             </task>
         </subscribe>
	 <share>
	     <task>
                 <!-- Defined as 60 second polling interval -->
	         <task_name>Share 1</task_name>
	         <handler>com.perspectium.replicator.sql.sharer.SQLSharer</handler>
                 <message_connection password="password" user="user">>https://user.perspectium.net</message_connection>
		 <polling_interval>60</polling_interval>           
                     .
                     .
                     .  	
             </task>
     	 </share>
         <share>
             <task>
                 <!-- Defined as 15 second polling interval -->
                 <task_name>Share 2</task_name>
                 <handler>com.perspectium.replicator.sql.sharer.SQLSharer</handler>
                 <message_connection password="password" user="user">>https://user.perspectium.net</message_connection> 	     
                 <polling_interval>15</polling_interval>           
                     .
                     .
                     .                   
             </task>
     	</share>
   </agent>
</config>


Continuous Listener


The following directive is used by a share task that needs to be scheduled to run continuously. For example if your <share> task is constantly monitoring an interface for messages that when received are processed in some way and then published to the message bus.

Continuous Listener Example

<config>
    <agent>
	<share>
            <task>
                <task_name>listener_task</task_name>
                <schedule>listener</schedule>
                    .
                    .
                    .
            </task>
        </share>
    </agent>
</config>


On Demand Execution


This section describes the configuration requirements and options for leveraging the Perspectium SQL Sharer DataSync Agent to replicate database tables on an on-demand basis. The agent will receive a message from a specified queue and then perform the request of that message.

This is accomplished by sending “SQL command” messages to the agent, passing a SQL query in the message so that when the agent receives the message it will query data from a table in the database the agent is configured to use and then send records from this query in messages that can be consumed by subscribing ServiceNow instances or other agents. These messages will each represent one record and will be in the standard topic, type, key, name, value message format that Perspectium sends out from ServiceNow instances.

config.xml requires the <ondemand_connection> directive in order for the on-demand agent to run:

Directive

Example

Use

Default

<ondemand_connection>

<ondemand_connection user="username" password="password" queue="psp.out.replicator.agent_share_queue">amqp://localhost</ondemand_connection>

Server connection configuration for where the agent should be listening for SQL command messages.

Required


Current <task> directives in agent.xml specifically for the SQL on-demand sharer agent are outlined in the following table (please note other required <task> directives for all agents as specified in the agent configuration):

Directive

Example

Use

Default

<handler>

<handler>com.perspectium.replicator.sql.sharer.SQLCommandSharer</handler>

The name of the SQL on-demand command sharer handler class

Required

<topic>

<topic>replicator</topic>

The “topic” value for messages shared by the agent

Required

<type>

<type>agent</type>

The “type” value for messages shared by the agent

Required

<key>

<key>will be overridden</key>

A required field for the Sharer agent but this field should be left with the value “will be overridden” as the agent will use its name as the “key” value in messages.

Required

<name>

<name>.bulk</name>

The “action” name (such as “.insert”, “.bulk” or “.delete”) as the value specified here will be appended to the table_name specified in the SQL command message as a directive for the action on the receiving end.

Required

<schedule>

<schedule>ondemand</schedule>

To specify this agent will be sharing on an on-demand basis.

Required


On Demand Example Sharer

To setup the SQL on-demand sharer agent, you will have to configure the agent to listen for SQL command messages from a specific queue on a Perspectium server in config.xml and then specify in agent.xml a server it will send messages to (the queue for the agent to share messages to is specified in the SQL command message). Note the queue the agent listens to for SQL command messages should be a different queue from any subscribe queues if the agent also is a subscriber agent. Please contact support with any questions on your Perspectium server and/or queue.

Below is a sample config.xml and agent.xml for sharing messages on demand:

config.xml


<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <common>
	<ondemand_connection user="username" password="password" queue="psp.out.replicator.agent_share_queue">amqp://localhost</ondemand_connection>
    </common>
</config>    


agent.xml

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent> 
	<share>
            <task>
                <task_name>sql_command_sharer</task_name>
                <handler>com.perspectium.replicator.sql.sharer.SQLCommandSharer</handler>
 
                <max_writes_per_connect>7</max_writes_per_connect>
                <polling_interval>15</polling_interval>
                <message_connection user="username" password="password">amqp://localhost</message_connection>
		<encryption_key>The cow jumped over the moon</encryption_key>
 
		<topic>replicator</topic>
		<type>agent</type>
		<name>.bulk</name>
		<key>will be overridden</key>
 
		<schedule>ondemand</schedule>
 
		<database_type>mysql</database_type>
                <database_server>localhost</database_server>
                <database_port>3306</database_port>
                <database_user>database_user</database_user>
                <database_password>database_password</database_password>
                <database_parms>autoReconnect=true</database_parms>
                <database>psp_repl</database>
             </task>
	</share>     
    </agent>
</config>


In this example, you specify in config.xml that you will be listening for on-demand messages in the message queue “psp.out.replicator.agent_share_queue” on the server “localhost”.

Once SQL command messages are received here and the agent reads them, the agent will then use the configurations as specified in the <task> directive in agent.xml. It will query the database configured with the SQL query passed in the SQL command message and then share out records in messages to the Perspectium server configured in <message_connection>.

Sending On-Demand MessagesOnce the agent is configured to listen for on-demand SQL command messages, you then send messages to the listening queue (such as “psp.out.replicator.agent_share_queue” in the above example) and the agent will then query the specified table in the database and send out messages.Please contact support to set up sending SQL command messages to a queue.The message should contain the following values:

DirectiveExampleUseDefault
<attributes>queue=psp.out.servicenow.instance_name\ntable_name=ticketSpecify the queue to share messages to and the table name to use in the “name” value of shared messages.Required
<topic>commandThis should always be “command”.Required
<type>“run_once”Currently only “run_once” (to execute this command once when the agent receives the message) is supported.Required
<key>agent_nameA “key” for this message (such as the instance name or agent name) where this message is coming from.Required
<name>sql_command_sharerThis should always be “sql_command_sharer”.Required
<value>select * from ticketThe SQL query to run.Required


For example, if the following message was sent:

{ "attributes":"queue=psp.out.servicenow.instance_name\ntable_name=ticket", "topic":"command", "type":"run_once", "key":"instance_name", "name":"sql_command_sharer", "value":"select * from ticket" }

The agent would run the SQL query “select * from ticket” and then share out records from this query as messages to the queue psp.out.servicenow.instance_name (on the Perspectium server specified in <message_connection> in agent.xml).


Cron Scheduling


This scheduling pattern is split into five space separated parts, surrounding the string:

* * * * *

In order of how the * appears, these * represent:

  1. Minutes sub-pattern

    • Which minutes of the hour should the task be launched. The values range is from 0 to 59.

  2. Hours sub-pattern

    • Which hours of the day should the task be launched. The values range from 0 to 23.

  3. Days of month

    • Which days of the month should the task be launched. The values range from 1 to 31, with L as a special value for the last day of the month.

  4. Months

    • Which months should the task be launched. The values range from 1 (January) to 12 (December) as well as: “jan”, “feb”, “mar”, “apr”, “may”, “jun”, “jul”, “aug”, “sep”, “oct”, “nov” and “dec” (without quotations).

  5. Days of week

    • Which days of the week should the task be launched. The values range from 0 (Sunday) to 6 (Saturday) as well as: “sun”, “mon”, “tue”, “wed”, “thu”, “fri” and “sat”.

The star wildcard character is also admitted, indicating “every minute of the hour”, “every hour of the day”, “every day of the month”, “every month of the year” and “every day of the week”, according to the sub-pattern in which it is used.

Once the scheduler is started, a task will be launched when the five parts in its scheduling pattern will be true at the same time.

Cron Scheduling Examples

For each of the following examples you would place it within the schedule tag like so: <schedule>0 12 * * *</schedule> or however you determine you scheduling to be.

Basic Scheduling
5 * * * *
This pattern causes a task to be launched once every hour, at the begin of the fifth minute (00:05, 01:05, 02:05 etc.).
* * * * *
This pattern causes a task to be launched every minute.
* 12 * * MonThis pattern causes a task to be launched every minute during the 12th hour of Monday.
* 12 16 * MonThis pattern causes a task to be launched every minute during the 12th hour of Monday the 16th. But only if the date is the 16th and its Monday.
Each sub-pattern can also contain two or more comma separated values
59 11 * * 1,2,3,4,5
This pattern causes a task to be launched at 11:59AM on Monday, Tuesday, Wednesday, Thursday and Friday.
Values intervals are admitted and defined using the minus character.
59 11 * * 1-5This pattern is equivalent to the previous one.
The slash character can be used to identify step values within a range, it can also be used in conjunction with the “-” interval character.
*/5 * * * *
This pattern causes a task to be launched every 5 minutes (0:00, 0:05, 0:10, 0:15 and so on).
3-18/5 * * * *
This pattern causes a task to be launched every 5 minutes starting from the third minute of the hour, up to the 18th (0:03, 0:08, 0:13, 0:18, 1:03, 1:08 and so on).
*/15 9-17 * * *
This pattern causes a task to be launched every 15 minutes between the 9th and 17th hour of the day (9:00, 9:15, 9:30, 9:45 and so on… note that the last execution will be at 17:45).
All the previously described syntax rules can be used together.
* 12 10-16/2 * *
This pattern causes a task to be launched every minute during the 12th hour of the day, but only if the day is the 10th, the 12th, the 14th or the 16th of the month.
* 12 1-15,17,20-25 * *
This pattern causes a task to be launched every minute during the 12th hour of the day, but the day of the month must be between the 1st and the 15th, the 20th and the 25, or at least it must be the 17th.
Finally, you combine more scheduling patterns into one, with the pipe character "|"
0 5 * * *|8 10 * * *|22 17 * * *
This pattern causes a task to be launched every day at 05:00, 10:08 and 17:22.