Configuration

The Perspectium SQL Subscriber Handler uses the newest version of each of the associated JDBC driver. In a nutshell, the Perspectium SQL subscriber is configured to specify the following:

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 based 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.

Message batching directives (optional)

To improve the performance for your DataSync Agent, you can optionally add some batching directives that specify what constitutes a batch of messages that will trigger message processing.

To enable these functions, nest one or many of the following directives within the agent.xml's <agent> directive:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
   <agent>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
      <message_batch_size>100</message_batch_size>
      <share/>
      <subscribe>
         <task instances="1">
            <task_name>datasyncforservicenow_to_mysql_subscribe</task_name>
            <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.testqueue" user="admin" use_basic_consume="false">https://testperspectium.net</message_connection>
            <instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4=" user="test.user">https://myinstance.service-now.com</instance_connection>
            <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
            <decryption_key>The cow jumped over the moon</decryption_key>
            <database_type>mysql</database_type>
            <database_server>localhost</database_server>
            <database_port>3306</database_port>
            <database_user>root</database_user>
            <database_password>encrypted:vlOtU71yu8N/EFIJH85SSMoilKLTeJHQrNZPJ7c5tFU=</database_password>
            <database_parms>autoReconnect=true & characterEncoding=UTF-8 & useSSL=false</database_parms>
            <database_column_max_size>251</database_column_max_size>
            <database_static_column_size/>
            <database>psp_repl</database>
            <byte_padding>4</byte_padding>
            <column_sort>alpha</column_sort>
         </task>
      </subscribe>
   </agent>
</config>

Share & subscribe

DataSync Agents can perform two main functions, share and subscribe. To enable these functions, nest one or many of the following directives within the agent.xml's <agent> directive:

Hover here to see where these directives are located in the agent.xml

Task & handler

The <task> directive defines a specific <share> or <subscribe> function that your DataSync Agent should perform. Multiple <task> directives can be nested within the <share> and <subscribe> directives, and individual configurations can be defined for each <task> by nesting further configuration directives (described in the following sections) within each <task>.

Hover here to see where these directives are located in the agent.xml

Task Configurations

Under the <task> directive are configurations related to the task your DataSync Agent is performing such as saving data to a database.  These different configurations are as follows: 

Message, instance, and schema connection

The <message_connection> and <instance_connection> directives define how your DataSync Agent connects to the Perspectium Mesh and ServiceNow instance, respectively. For your DataSync Agent to connect to your Salesforce organization, you will need to include the <message_connection> and <schema_connection> directives.

Encryption key & decryption key

The <encryption_key> directive is used with <share> tasks to encrypt data that is being shared from a static data source to an application. The <decryption_key> directive is used with <subscribe> tasks to decrypt data that was encrypted by a sharing application. The value for the <encryption_key> directive should match the subscribing application's decryption key, and the value for the <decryption_key> direction should match the sharing application's encryption key.

Other Task Configurations

Database

There are several directives related to your static data source, or database, that the DataSync Agent connects to. 

Other Configurations

Data Guarantee

These directives configure how the DataSync agent works with Data Guarantee.

Example basic agent.xml configuration

The example below shows some of the basic configurations that must be set for a Perspectium DataSync Agent. For additional DataSync Agent configuration settings, see the Similar topics list below or browse the topics under DataSync Agent.

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
   <agent>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
      <share/>
      <subscribe>
         <task instances="1">
            <task_name>datasyncforservicenow_to_mysql_subscribe</task_name>
            <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.testqueue" user="admin" use_basic_consume="false">https://testperspectium.net</message_connection>
            <instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4=" user="test.user">https://myinstance.service-now.com</instance_connection>
            <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
            <decryption_key>The cow jumped over the moon</decryption_key>
            <database_type>mysql</database_type>
            <database_server>localhost</database_server>
            <database_port>3306</database_port>
            <database_user>root</database_user>
            <database_password>encrypted:vlOtU71yu8N/EFIJH85SSMoilKLTeJHQrNZPJ7c5tFU=</database_password>
            <database_parms>autoReconnect=true & characterEncoding=UTF-8 & useSSL=false</database_parms>
            <database_column_max_size>251</database_column_max_size>
			<database_static_column_size/>
            <database>psp_repl</database>
			<byte_padding>4</byte_padding>
			<column_sort>alpha</column_sort> 
         </task>
      </subscribe>
   </agent>
</config>

Subscribing to tables

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.

Consider 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

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.

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.