User Tools

Site Tools


sql_replicator

Perspectium SQL Replicator Agent Configuration Guide

Subscriber Agent

Introduction

This section describes the configuration requirements and options for leveraging the Perspectium SQL Subscriber Replicator Agent for replicating table changes made to a Service Now instance.

The current required directives available are outlined in the following table.

Directive Example Use Default
task_name <task_name>a unique name for the task</task_name> Unique task name Required
instance_name <instance_name>the_name.service-now.com</instance_name> Service now instance Required
instance_user <instance_user>serviceNowUser</instance_user> User used to request a table schema Required
instance_password <instance_password>serviceNowUser</instance_password> Instance user's password Required
decryption_key <decryption_key>shared secret key</decryption_key> Secret key shared with the instance Required

The instance directives provide the agent with the data it needs in order to establish a session with the Service Now Instance to build and maintain a cache of information related to the tables or schemas that are being replicated.

  <instance_name> will be the fully qualified domain name of the instance. 
  <instance_user> is used to define the name of a valid user defined within the specified instance. 
  <instance_password> is used to define the password associated with the configured instance user.

Starting in version 3.2.2, the ability to use a Proxy Server to communicate to the ServiceNow Instance is provided.

Enabling use of an HTTP(S) Proxy

Several organizations use such a proxy to provide a more secure or controlled method in which to access servers within the Internet.

The following template is used when using the <instance_proxy> proxy configuration directive.

<instance_proxy>schema:host:port</instance_proxy>

Port is optional and if it's excluded then no ':' should follow the host name or IP.

In the following example the traffic to the proxy is encrypted via an SSL connection.

   <instance_proxy>https://192.168.40.131</instance_proxy>
   

In this next example the traffic is un-encrypted (using http not https) to a proxy name proxy.acme.com via TCP port 5678.

   <instance_proxy>http:proxy.acme.com:5678</instance_proxy>
   

In this next example the traffic is encrypted https to a proxy named proxy.acme.com via TCP port 8443.

   <instance_proxy>https:proxy.acme.com:8443</instance_proxy>
   

The following table reflects the available optional configuration directives:

Directive Example Use Default
amqp_uri <amqp_uri>amqp://localhost</amqp_uri> Primary Message Bus Location Optional
amqp_user <amqp_user>admin</amqp_user> User for logging into primary bus Optional
amqp_password <amqp_password>adminadmin</amqp_password> User's password for message bus Optional
polling_interval <polling_interval>20</polling_interval> Interval between each run Optional
max_reads_per_connect <max_reads_per_connect>1500</max_reads_per_connect> Messages to process per interval Optional
max_writes_per_connect <max_writes_per_connect>1500</max_writes_per_connect> Messages to process per interval Optional
schedule <schedule>* * * * *</schedule> Process messages every minutes Optional
schemas_directory <schemas_directory>directory name</schemas_directory> directory holding the schema definitions Optional
cant_access_directory <cant_access_directory>directory name</cant_access_directory> directory holding the name of table which can't be accessed. Optional
date_format <date_format>MM/dd/yyyy</date_format> format of the date for all tables. Optional
date_time_format <date_time_format>MM/dd/yyyy HH:mm:ss</date_time_format> format of the date and time for all tables. Optional
key <key>ven0001</key> to specify a “key” or instance to subscribe to and ignore records from all other sources e.g. only records coming from the ServiceNow instance ven0001 will be subscribed to and all other messages will be skipped. Introduced in v3.8.0 Optional

The following table provides the optional target database configuration options.

Directive Example Use Default
database_type <database_type>mysql|sqlserver|oracle|postgres|etc</database_type> The type of database replicated to Required
database <database>psp_repl</database> Name of the target database Optional
database_server <database_server>IP or FQDN of the database server</database_server> IP or FQDN of the database server Required
database_user <database_user>database user name</database_user> User for logging into the database Required
database_password <database_password>database user password</database_password> Password for logging into the database Required
database_port <database_port>port used by the server</database_port> Port used by the server Optional
database_parms <database_parms>parms passed to the database server</database_parms> Parameters passed to the database server Optional
database_sid <database_sid>SID (Oracle Only) </database_sid> SID used for Oracle database Optional
  The <database> directive allows you to specify or override the default database. 
  The database used is specified by using the <database_type> directive and you must specify one of the following types:

Examples of databases used in <database_type>example</database_type> directive:

Database <database_type> Names
Oracle oracle
SQL Server sqlserver
MySQL mysql
SAP Hana hana
Amazon Redshift redshift
IBM DB2 db2
HP Vertica vertica
Postgres postgres
  The <database_server> directive is used to provide either a fully qualified domain name of the database server or its IP address.
  The <database_port> directive is used to override the standard port used by the servers. 
 The <database_user> directive is used to specify the user name that will be used to login to the database. The user configured must have permissions which allow it to create and modify tables.
 The <database_password> directive is used to specify the password associated with the configured user.
 The <database_parms> directive can be used to provide additional information to the database server during establishment of the connection.  
 The <database_sid> directive is optionally used to specify the SID for Oracle database.
 

For Example:

The following configures each of the available directives.

<agent>
     <subscribe>
         <task>
            <task_name>SQL_replication</task_name>
            <instance_name>someones.service-now.com</instance_name>
            <instance_user>perspectium</instance_user>
            <instance_password>apasswordhere</instance_password>
       	    <schemas_directory>someones.schemas</schemas_directory>
            <cant_access_directory>someones.cantaccess</cant_access_directory>
            <database_server>54.215.8.21</database_server>
            <database_user>mke</database_user>
            <database_password>dbpassword</database_password>
            <database_port>1433</database_port>
            <database_type>sqlserver</database_type>
            <database_parms>autoReconnect=true</database_parms>
         </task>
     </subscribe>
</agent>

Additional SQL Subscriber directives

The following table reflects additional directives that apply to all SQL type Subscribers.

Postgres Server

Argon
The Postgres implementation does not dynamically create the database specified in the <database> configuration directive. The database must be created prior to being used by the agent.

In order to integrate with a Postgres database, you must obtain the JDBC driver from the Postgres website. Currently, Perspectium supports version 42.1.4 of the Postgres JDBC driver. Once you have obtained the driver, it needs to be placed into the Agent's Jars directory, which is a sub-directory within the Replicator Agent's application folder.

Postgres Server is offered as a premium service, please contact our support team support@perspectium.com to inquire about this service.

SAP Hana

In order to integrate with a SAP Hana database, you must use the JDBC driver (ngdbc.jar) that is installed as part of the SAP Hana client installation. Once you have obtained the driver, it needs to be placed into either the Agent's jars or extlib directories, which are sub-directories within the Replicator Agent's application folder.

SAP Hana is offered as a premium service, please contact our support team support@perspectium.com to inquire about this service.

MS SQL Server

In order to ensure proper interaction between the Perspectium SQL subscriber and an instance of a MS SQL Server database, the user that is configured must have the ability to create a database and or the default database ‘psp_repl’ must be pre-created.

If you experience a connection timeout error you can add the following directive within your <task> definition to increase the number of seconds that the attempt will last before failing. In this example we've defined waiting for 30 seconds.

 <database_parms>loginTimeout=30</database_parms>

Argon
Because ntext is being deprecated in a future version of MS SQL Server, as of the Argon release, the agent will create large fields (fields defined with a size of 251+ characters) as nvarchar(max). This will also allow searching of these fields since nvarchar(max) fields support distinct queries that are not supported with ntext.

Oracle

In order to ensure proper interaction between the Perspectium SQL subscriber and an instance of an Oracle 11g database server, the user that is configured must have the ability to create a table. Furthermore, an Oracle database with a service name must be pre-created. The database directive will be configured with the service name of the database.

As of v3.20.0 a TNS string can be used to connect to an Oracle database. The TNS string should be inserted into the database_directive and the agent will use it to connect. When using this the database_port and database_sid directives are no longer required. The database_user and database_password directive are still required.

Redshift

In order to integrate with a Redshift database you must obtain the JDBC driver from AWS. Currently, Perspectium supports version 4.1 of the Redshift JDBC driver. Once you have obtained the driver from AWS, it needs to be placed into the Agent's Jars directory, which is a sub-directory within the Replicator Agent's application folder.

Starting with v3.22.0 of the Replicator Agent support for the AWS Redshift database has been enhanced. To take advantage of the enhanced processing requires additional configuration steps which are outlined below. You can however continue to use the previous support for Redshift by skipping this additional configuration.

The enhanced version of the agent uses an S3 bucket as a staging area where replicated data is stored and then subsequently used to populate a redshift table.

The agent uses AWS tools to efficiently populate a replicated table to the database from the S3 bucket. You must create the S3 bucket during setup by using AWS tools. Once created you'll then provide the bucket name and security credentials which will enable access to the S3 bucket during configuration of the agent.

Furthermore, the following entries must be included within each <task> element in which Redshift support is being configured.

The <handler> directive must specify 'RedshiftSQLSubscriber' as the handler. For example:

<handler>com.perspectium.replicator.sql.RedshiftSQLSubscriber</handler>

In addition, the following directives must also be placed within the <task> definition with the proper values. These additional directives are used to specify the location or region that hosts your redshift database and S3 bucket.

The <region> directive specifies the AWS region. Example:

<region>us-west-1</region>

The <access_key> and <secret_access_key> directives must have valid AWS access credentials in order to securely access your S3 bucket.

<access_key>PLACE_YOUR_KEY_HERE</access_key>
<secret_access_key>PLACE_YOUR_SECRET_KEY_HERE</secret_access_key>

The <s3_bucket> directive is used to define the name of the S3 bucket you created to hold replicated data.

<s3_bucket>your_bucket_name</s3_bucket>

Additionally, in order to optimize throughput you should enable batch processing of row deletes by including the following directive within your <task> definition:

<batch_delete/>

Redshift is offered as a premium service, please contact our support team support@perspectium.com to inquire about this service.

2017/06/06 15:41 · Billy Wong

SQL Subscriber Handler

The Perspectium SQL Subscriber Handler is capable of communication with any Relational Database Management System (RDBMS) which support the JDBC 2.0 specification. Click here to view a list of supported database types.

The Perspectium SQL subscriber is configured to specify the following:

  • The IP address or fully qualified domain name of the Database Server
  • The Port which the database server is listening on
  • A user which has been configured to access and perform the required database operations
  • The password associated with the configured database user
  • The name of the database which will contain the replicated tables
  • Optionally, the SID if it's an Oracle database and a SID is required
Permissions

After installation has been performed and the Replicator Agent is started, the agent establishes a connection to the database server using the credentials configured as outlined above. This task must be completed successfully before the agent will proceed with subsequent processing. Once the connection has been established, the agent will attempt to create the configured database. This step requires that the configured user has the proper permissions to create a new database. If this is not the case then an error will occur. As an alternative, the required database can be created by something or someone other than the agent. When this approach is taken you add the configuration directive

<skip_database_creation/> 

to the configuration and the agent will not attempt to create the database since this tell the agent that the database already exists.

Once replicated data is available, the agent will determine if the table associated with the replicated data exists, and if it's not it will create it. This means that the configured user must have the permissions required to create tables within the defined database.

Once the required table has been created the agent will perform an insert in order to populate the table with the replicated row. This means that the agent must have the permissions to perform an insert into the table.

When you share out data to be consumed by the Replicator Agent, you'll typically specify whether the data is being shared in bulk or dynamically. When the data is shared in bulk and based on how the agent has been configured, it will typically perform a query in order to determine if the row already exists in the database. The agent must therefore have permission to query the database and the associated tables. If the agent determines that the row already exist then it performs an update, otherwise it will insert the row. The agent must have permission to perform both these actions.

Additionally, the agent wants to ensure that all data is replicated. It's possible that the structure of the source table has changed. For example, an additional column is added to the table or the size of a column is increased. The agent performs the analysis required to make this determination and by default will alter the table in order to align it with the source table. Currently, Perspectium supports adding a column, increasing the size of a column to it's maximum and if baseed upon the database and the type of column altering the data type. The agent must therefore have the permissions to alter the table. It is possible to constrain the agent from altering tables by including the

<skip_alter/> 

directive within the SQL Subscribers

<task> 

definition.

Lastly, in order to support the deletion of rows from a table, the database user must also have permission to delete a table row.

Subscribers

Subscriber handlers exist to obtain a message from a queue within the message bus, obtain the message and do something with it. For example, the MySQL subscriber provided by Perspectium, processes the message by converting it into a valid MySQL database statement and then inserting it into a MySQL table.

Subscriber handlers are configured to know about where the information they’re interested in obtaining is located. The information is located within a queue inside and instance of the Perspectium message bus and you must configure access to the message bus and the target queue. You start the configuration of a subscriber using the <subscribe> element directly following agent directives. A subscriber essentially is subscribing to a <task> that it monitors for work to be performed. How to find and interact with the queue is defined within the <task> tag.

<agent>
    <subscribe>
     <!-- Subscriber configuration directives -->
         <task>
	    <task_name>myTask</task_name>
	    <schedule>* * * * *</schedule>
	    <handler>javaClassHandler</handler>
	    <database_type>mysql</database_type>
	    <decryption_key>shared pass phrase for decrypting</decryption_key>
         </task>
    </subscribe>
</agent>

Subscribing to tables

The following is only applicable for versions older than 3.2.1.

This section lets you know how to control which tables your subscriber is interested in and which ones it’s not. In some cases a common Message Bus Queue will be used to Share out multiple table changes. The following configuration directives allow you very fine grained control over which tables will be included for processing and which will be excluded.

It’s important to note that the approach used is referred to as lazy, which is to say by default the agent wants to ignore or not perform the work defined in the Message Bus Queue. That being the case you must include those tables that you’re interested in Subscribing to. In most cases it will be all items in the queue and all actions such as insert, update and delete associated with those items. In this case it’s easiest to leverage the wild card ‘*’ which means everything.

Let’s start with an example:

<tables>
    <includes>
        <include>*.insert</include>
        <include>*.delete</include>
        <include>*.update</include>
    </includes>
    <excludes>
        <exclude>cmdb_ci_servers.insert</exclude>
    </excludes>
</tables>

This example says to process every table using the wild card character and does so for each action available. This configuration then states that if the table happens to be cmdb_ci_servers and the action is an insert to ignore or not process it.

Consider the following:

<tables>
    <includes>
       <include>*.delete</include>
       <include>*.update</include>
    </includes>
    <excludes>
        <exclude>cmdb_ci_servers.*</exclude>
    </excludes>
</tables>

This configuration will not process any tables that have an insert associated with them. Recall the laziness of the approach; if it’s not told to do something explicitly it will not process the entry. This configuration also states to ignore or perform no action on the cmdb_ci_servers table.

Consider the following:

<tables>
    <includes>
        <include>cmdb_ci_servers.insert</include>
    </includes>
</tables>

This configuration requests that only inserts of the table cmdb_ci_servers be processed, everything else will be ignored.

Handling of long table and column names

v3.9.0

Since Oracle only permits a string of 30 characters by default, a table or a column name that has over 30 characters will be truncated upon replication. The algorithm used for the truncation is the first 15 characters of the name will be displayed, followed by a “_” and then the last 14 of the table name. The “_” denotes the truncated values between the first 15 and the last 14 characters.

For example: When replicating the table “Workflow Estimated Runtime Configuration” (41 characters total) to Oracle database, the replicated name will be “u_workflow_esti__configuration”.

Please Note: When replicating a table or column that have similar names, meaning if the first 15 and the last 14 of the name are the same, only one of the two records will be replicated since after truncation, both of the names will be the same.

The following is only applicable for versions NEWER than 3.2.1.

In the newer versions of the SQLSubscriber handler, all tables and actions are enabled by default. This means that for most cases you won't have to modify the configuration at all or be worried about it.

Perspectium recommends you contact support if you are interested in replacing the default configuration.

Sharer Agent

Scheduled Sharer Agent

This section describes the configuration requirements and options for leveraging the Perspectium SQL Sharer Replicator Agent to replicate database tables on an scheduled basis.

With the SQL Scheduled Sharer agent, the user can customize a time frame and the frequency of when the messages are shared from the agent.

Current <task> directives in agent.xml specifically for the SQL Scheduled 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.SQLSharer</handler> The name of the SQL scheduled sharer handler class. Required
table name <table_name>ticket</table_name> The “table name” value for the table the agent is sharing.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 agent.xml for the “name” value for messages shared by the agent. For example, when the scheduled shared agent shares records from the ticket table and the value here is “.bulk”, message will be sent with the name value of “ticket.bulk”.Required
schedule <schedule>0 0 * * *</schedule> To specify this agent will be sharing on scheduled basis based on the configured value.Required

The above schedule example <schedule>0 0 * * *</schedule> will perform a share task from the SQL agent daily at 12:00am. For more details on scheduling examples, please visit Scheduling.

Sample agent.xml Configuration Files

To setup the SQL scheduled sharer agent, you will have specify in agent.xml the server it will send messages to and also specify a queue where the agent will send the messages to. Please contact support@perspectium.com with any questions on your Perspectium server and/or queue.

Below is a sample agent.xml for sharing messages:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
  <agent> 
 
    <max_writes_per_connect>10</max_writes_per_connect>
    <polling_interval>15</polling_interval>
    <message_connection user="ACME" password="XXXX">amqps://acme.perspecium.net/</message_connection>
 
    <share>
      <task>
        <task_name>sql_sharer</task_name>
        <handler>com.perspectium.replicator.sql.sharer.SQLSharer</handler>       
        <message_connection user="ACME" password="XXXX" queue="psp.out.servicenow.instance_name">amqps://acme.perspecium.net/</message_connection>
	<encryption_key>The cow jumped over the moon</encryption_key>
 
        <table_name>ticket</table_name>
	<topic>replicator</topic>
	<type>agent</type>
	<name>.bulk</name>
	<key>will be overridden</key>
 
	<schedule>0 0 * * *</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_params>autoReconnect=true</database_params>
        <database>psp_repl</database>
        </task>
    </share>
 
  </agent>
</config>

When the SQL scheduled sharer agent is started, the agent will use the configuration as specified in the <task> directive in agent.xml and it will query the database configured and share out records in messages to the Perspectium server configured in <message_connection> within the defined <schedule> interval configured by the user.

Share by LastUpdate Agent

v3.10.0

This section describes the configuration requirements and options for leveraging the Perspectium SQL Sharer Replicator Agent to replicate database rows that have been modified since the last run interval based on a datetime column value in the table.

The timestamp of the last run interval is saved in the <AGENT_HOME>/bin/sharedState directory. The file name is formatted <task_name>_<table_name>.txt

Below is a sample agent.xml for sharing messages:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
  <agent> 
 
    <polling_interval>30</polling_interval>
 
    <share>
      <task>
        <task_name>sql_last_update_sharer</task_name>
        <handler>com.perspectium.replicator.sql.sharer.SQLShareByLastUpdateQuery</handler>
 
        <message_connection user="ACME" password="XXXX" queue="psp.out.servicenow.instance_name">amqps://acme.perspecium.net/</message_connection>
        <encryption_key>The cow jumped over the moon</encryption_key>
 
        <table_name>ticket</table_name>
        <topic>replicator</topic>
        <type>agent</type>
        <name>.bulk</name>
        <key>will be overridden</key>
 
        <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_params>autoReconnect=true</database_params>
        <database>psp_repl</database>
 
        <!-- The <datetime_field> element is the datetime column name to use as 
             the last updated by query value.  If not specified, the default is sys_updated_on
        -->
        <datetime_field>timestamp_column</datetime_field>
 
        <!-- The <last_modified_timezone> element is used to specify the timezone used
             to match the timestamp column.  It should be the same timezone used by 
             your timestamp column.
        -->
        <last_modified_timezone>GMT</last_modified_timezone>
 
        <!-- the <where> element is an optional filter criteria that can be applied in
             in addition to the last interval filter
             You are responsible for properly escaping column names that are SQL reserved words.
        <where>`assignment_group` = 'Network'</where>
        -->
 
      </task>
    </share>     
  </agent>
</config>    

For troubleshooting with this feature see here.

On-Demand Sharer Agent

v3.3.0

This section describes the configuration requirements and options for leveraging the Perspectium SQL Sharer Replicator Agent to replicate database tables on an on-demand basis.

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 agentRequired
type <type>agent</type> The “type” value for messages shared by the agentRequired
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 for the “name” value for messages shared by the agent. For example, if the SQL command message specifies to share records from the ticket table and the value here is “.bulk”, messages will be sent with the name value of “ticket.bulk”.Required
schedule <schedule>ondemand</schedule> To specify this agent will be sharing on an on-demand basis.Required
Sample config.xml and agent.xml Configuration Files

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@perspectium.com with any questions on your Perspectium server and/or queue.

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

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_params>autoReconnect=true</database_params>
                <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 SQL Command Messages

Once 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@perspectium.com to set up sending SQL command messages to a queue.

The message should contain the following values:

Directive Example Use Default
attributes queue=psp.out.servicenow.instance_name\ntable_name=ticket Specify the queue to share messages to and the table name to use in the “name” value of shared messages.Required
topic command This 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_name A “key” for this message (such as the instance name or agent name) where this message is coming from.Required
name sql_command_sharer This should always be “sql_command_sharer”.Required
value select * from ticket The 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).

2015/12/14 18:49 · Billy Wong
sql_replicator.txt · Last modified: 2018/09/29 10:35 by paul