For optional advance configurations for your DataSync Agent, contact Perspectium Support if you have any questions.

Agent Column Case Configuration

The DataSync Agent supports forcing of the column name to a configured case. This configuration must be done prior to the Agent creating the table. Two directives are available for inclusion within the agent.xml configuration file. The chosen directive must be placed within the <task> tag.

You would use either of these directives in your agent.xml: 

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="encrypt:XXXX" user="XXXX" queue="..." >amqp://example.perspectium.net</message_connection>
                <instance_connection password="encrypt:XXXX" user="XXXX">http://example.service-now.com</instance_connection>
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>The cow jumped over the moon</decryption_key>
                <database_type>sqlserver</database_type>
                <database_server>MyAddress</database_server>
                <database_port>1433</database_port>
                <database_user>XXXX</database_user>
                <database_password>XXXX</database_password>
                <database_parms></database_parms>
                <database>psp_repl</database>
 
                <uppercase_columns/>
 
            </task>            
        </subscribe>
        <max_reads_per_connect>2000</max_reads_per_connect>
        <polling_interval>5</polling_interval>    
    </agent>
</config>

The default (none of these directives) simply uses the original case of the column. If you happen to specify both, uppercase will be used.

 NOTE: This directive affects column names only. It does not affect table names. 

↑ Go to top of page

Change data type

To ensure multibyte or foreign characters are saved properly for string column types, the Agent creates column types that can store unicode string data such as NVARCHAR (MySQL, MS SQL Server, etc). You can use other data types other than N type fields by editing the databases.xml.

  Before you begin, request the databases.xml file for your DataSync Agent by contacting Perspectium Support.

<mapping sn_type="2011" db_type="2011">NCLOB</mapping> 

<mapping sn_type="2011" db_type="2011">CLOB</mapping>

↑ Go to top of page

DataSync Agent field type mappings

To ensure data is formatted correctly, the DataSync agent will map ServiceNow field types to Database field types. Consult the table below to see how ServiceNow field types are mapped for supported databases.

↑ Go to top of page

Enable upsert to a database

For .bulk messages, the DataSync Agent will query for a record to see if it exists and if so, update that record.  Otherwise if the record doesn't exist, an insert will occur. However for databases that support it, the Agent can perform an upsert action where updating or inserting is done in one action to help improve performance on large tables that have hundreds of millions or billions of records. To enable this, you will need to use the <enable_upsert/> directive.

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
   <agent>
	<truncate_utf8/>
      <share/>
      <subscribe>
         <task instances="1">
            <task_name>test_hana2_subscribe</task_name>
            <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.testqueue" user="admin">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>hana</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>characterEncoding=UTF-8 & useSSL=false</database_parms>
            <database_column_max_size>251</database_column_max_size>
            <database>psp_repl</database>
            <skip_alter/>
			<enable_upsert/>
         </task>
      </subscribe>
      <max_reads_per_connect>4000</max_reads_per_connect>
      <polling_interval>5</polling_interval>
      <skip_message_set_processing/>
   </agent>
</config>

↑ Go to top of page

Excluding column DataSync Agent configuration

The DataSync Agent supports excluding columns from replication. Configuring columns to be excluded should be done prior to the Agent creating the table, which contains the excluded column for clarity. However, it can be done after with no ill affect. That column will no longer be populated.

You can configure multiple columns to be excluded. To set this up you will have one <exclude_columns>…</exclude_columns> set of tags. Within the tag, you will define each column to be excluded by individual <exclude_column>…</exclude_column> tags. This will be placed within the <subscribe> element located in the agent.xml configuration file.

 NOTE: The preferred approach for excluding columns from being replicated is to leverage capabilities of the producer, such as Perspectium Views, which provides the ability to define which columns should be replicated.

In the example below, the necessary configuration is added to exclude the columns:

• fx_price

• sys_tags

• sys_translated_text

Currently, when a column is excluded, it is applied to every table which may contain the column.

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection>amqps://example.perspectium.net</message_connection>
                .
                .
                .
            </task>
  
            <exclude_columns>
                <exclude_column>sys_translated_text</exclude_column>
                <exclude_column>fx_price</exclude_column>
                <exclude_column>sys_tags</exclude_column>
            </exclude_columns>
  
        </subscribe>
  
        <max_reads_per_connect>4000</max_reads_per_connect>
        <polling_interval>5</polling_interval>
    </agent>
</config>

↑ Go to top of page

Set up a Highly Available Agent

Perspectium DataSync Agents support an “exclusive consumer” mode that provides a Highly Available Agent solution while maintaining the order of messages in a queue. This active-passive Highly Available approach will exclusively allow only one DataSync Agent to actively read messages from a queue. Other DataSync Agents that passively try to connect to the queue will only be given access to the queue if another DataSync Agent is no longer connected.

DataSync Agents use the queue in the message connection and locks that queue for exclusive use. If multiple tasks are made using the same message connection, only one subscriber task will be created and used. When running another DataSync Agent, a warning will be logged in the perspectium.log file indicating that the queue is in exclusive use and that consumer wait time will be increased (default is to increase to 30 seconds). Once the first running DataSync Agent is stopped or fails, the backup DataSync Agent will start consuming messages when the queue becomes available and the consumer wait time will be reset.

 NOTE: The Highly Available Agent is only available when your DataSync Agent is set to connect to the Perspectium Mesh via the AMQP/AMQPS protocol (HTTP/HTTPS is not supported).

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
    <config>
     <agent>

        <max_reads_per_connect>2000</max_reads_per_connect>
       	<polling_interval>15</polling_interval>

        <subscribe>
            <task>
                <task_name>ticket_table_replication</task_name>
                <polling_interval>30</polling_interval>

                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>The cow jumped over the moon</decryption_key>

                <message_connection queue="psp.out.replicator.example" password="encrypted:iTOCp4deP7rJNgFkf2AEMA==" user="exampleuser"  use_basic_consume="true" exclusive="true" >amqp://acme.perspectium.net</message_connection>
                <instance_connection user="example.user" password="examplepassword">https://myinstance.service-now.com</instance_connection>
                <consumer_sleep_time>50000</consumer_sleep_time>
                <database_type>mysql</database_type>
                <database_server>localhost</database_server>
                <database_port>3306</database_port>
                <database_user>root</database_user>
                <database_password>encrypt:mypassword</database_password>
                <database_params/>
                <database>mydatabase</database>
                <skip_database_creation/>
            </task>
      </subscribe>

   </agent>
  </config>

↑ Go to top of page

Set up temporal data

To compile timestamped "snapshots" of your ServiceNow data, you can configure your DataSync Agent with the <temporal> directive. <temporal> will allow you to indicate times when your record's data is/was valid from and when the data is/was valid to.

 NOTE: To set up a temporal data, the table you are syncing data to must not contain any records (i.e., Temporal data cannot be captured for tables that are already being synced to a database with a DataSync Agent).

The following are a list of database the temporal agent supports: 

• Oracle
• MySQL
• MSSQL
• SAP Hana 2.0
• PostGresSQL

Expand
title To set up a temporal data in a local database, click here and follow these steps...
UI Steps size small UI Step Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent. UI Step Open your agent.xml file in a text editing application. To use the default configurations for syncing temporal data (columns will be named psp_valid_from and psp_valid_to, the maximum psp_valid_to value will be 9999-01-01 01:01:01, and the archived psp_valid_to value will be 8888-01-01 01:01:01), add the <temporal/> self-closing tag anywhere within the <task> tag. An example of this agent.xml is shown below. Otherwise, to create custom configurations for your temporal database, see Step #3. Example agent.xml for default temporal data configurations: Code Block language xml theme Eclipse <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?> <config> <agent> <share/> <subscribe> <task instances="1"> <task_name>test_subscribe</task_name> <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.test" use_basic_consume="true" user="admin">amqps://test.perspectium.net</message_connection> <instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4=" user="admin">https://mycompany.service-now.com</instance_connection> <handler>com.perspectium.replicator.sql.SQLSubscriber</handler> <decryption_key>This is my decryption key for testing</decryption_key> <database_type>mysql</database_type> <database_server>localhost</database_server> <database_port>3306</database_port> <database_user>testuser</database_user> <database_password>testpassword</database_password> <database_parms>characterEncoding=UTF-8 & useSSL=false</database_parms> <database_column_max_size>251</database_column_max_size> <database>psp_repl</database> <temporal> <columns> <column column_type="93" column_size="32" type="from" primary_key="false">psp_from</column> <column column_type="93" column_size="32" type="to">psp_to</column> </columns> <max_datetime>2030-12-31 00:00:00</max_datetime> <timestamp_difference>5</timestamp_difference> </temporal> <archive_datetime>2050-12-31 00:00:00</archive_datetime> </task> </subscribe> <max_reads_per_connect>4000</max_reads_per_connect> <polling_interval>5</polling_interval> <skip_message_set_processing/> </agent> </config> UI Step Create custom configurations (optional) Directive  Attribute(s) Description <max_datetime> Customize your temporal database table's maximum date and time stamp in the psp_valid_to column.  Code Block language xml <max_datetime>2030-12-31 00:00:00</max_datetime> Replace thedate and time stamp with any value you would like to assign for custom configuration.  NOTE: If records already exist in your temporal database table and then the <maxdatetime> value is changed, any records that were previously timestamped in the psp_valid_to column will not be updated to reflect the newly entered <max_datetime> value. <archive_datetime/> Customize your temporal database table's archive date and time stamp in the psp_valid_to column. Code Block <archive_datetime>2050-12-31 00:00:00</archive_datetime> Replace the date and time stamp with any value you would like to assign for custom configuration. The date and time stamp is applied to temporal rows when the agent consumes .archive messages. This marks the row as “archived” and helps with querying for archived records in the database tables. <disable_archive/> This will disable <archive_datetime/> functionality and skip processing .archive messages <column> type="from" type="to" Customize the column names for the psp_valid_from and psp_valid_to columns by modifying the values within the <column type="from"> and <column type="to"> directives, respectively. Code Block language xml <column type="from">psp_valid_from</column> <column type="to">psp_valid_to</column> Show If special @authenticated You can setup precision fractional seconds for your temporal columns timestamps. In your agent.xml in the temporal tag, add the attribute precision_size. For example, <precision_size="3"> will result in a timestamp similar to "2018-09-08 17:51:04.123" whereas <precision_size="4"> will result in a timestamp similar to "2018-09-08 17:51:04.1234". <column> primary_key=”true” Customize which temporal columns are primary keys by modifying the values within <column primary_key=”true”>. By default, both temporal columns are primary keys. However, they both cannot be non-primary keys, meaning both columns cannot have <column primary_key=”false”>. This is so that it can still insert temporal entries into the database and the database does not complain about duplicate entries. Code Block language xml <column primary_key="false">psp_from</column> <timestamp_difference> Customize a time period between temporal entries by adding <timestamp_difference> inside <temporal>.  Code Block language xml <temporal> <timestamp_difference>1</timestamp_difference> </temporal> The value will be read as seconds, so if you want 1 microsecond, you would use 0.000001. The smallest it can go is nanoseconds. By default, the time period is set to 1 millisecond to keep times between entries in order.  WARNING: It is not recommended to input a large number in <timestamp_difference>. Doing so would cause the timestamped entries to not be in order as it relies on when the record is processed. <skip_duplicates/> Skip inserting duplicate entries by adding directive. <skip_duplicates/> inside <temporal>.   NOTE: This may affect the agent’s performance as it checks with the database and does a comparison every time a message comes in. Code Block language xml <temporal> <skip_duplicates/> </temporal> <skip_duplicates/> check_all_fields="true" Check the fields values if the updated time is the same from the previous share.  Code Block language xml <temporal> <skip_duplicates check_all_fields="true"/> </temporal> <update_on_bulk/> Use to change the temporal behavior with .bulk messages. With this attribute set to true, the Agent will do the following when a .bulk message is received: 1) Check if the sys_updated_on value of the message is different than the latest temporal record in the table. 2) If the value is different, insert a new temporal record. 3) If the value is the same, update the latest temporal record. This is useful for when new fields are added to a table with new default values but the record itself hasn't been changed otherwise. This feature only applies to .bulk messages as .insert and .update messages will always insert new temporal records. The default value is false if this attribute is not specified in which case the Agent will have default temporal behavior and always insert new temporal records.   NOTE: Executing multiple shares for the same table at the same time resulting in multiple messages for the same record may create duplicates or discrepancies. Code Block language xml <temporal> <update_on_bulk/> </temporal> UI Step Save the changes you made to your agent.xml file and close the file. UI Step Log into the database that your DataSync Agent is sharing data to, and confirm that psp_valid_from and psp_valid_to timestamps are being created and that the psp_valid_to timestamp is updated whenever a change is made to the same record. NOTE: By default, the primary keys for the subscribed table your database will be a composite key comprised of the sys_id value, from column value, and to column value. Image Added

↑ Go to top of page

Share Table Schemas to a database

By default, table schemas are created by passing your ServiceNow instance username and password into the <instance_connection> tag of the agent.xml file created upon installation of the DataSync Agent. Alternatively, you can send ServiceNow table schemas directly to a database that your DataSync Agent replicates to.

 NOTE: Table schemas can be shared through bulk share only.

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
  <agent>
    <subscribe>
      <task>
        <task_name>example_subscribe</task_name>
        <message_connection password="encrypted:XXXXX" queue="psp.out.replicator.example" use_basic_consume="true" user="exampleUser">amqps://example.perspectium.net</message_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>exampleHost</database_server>
        <database_port>3306</database_port>
        <database_user>exampleUser</database_user>
        <database_password>encrypted:XXXXX</database_password>
        <database_params>characterEncoding=UTF-8</database_params>
        <database>psp_repl</database>
 
        <use_cache/>
      </task>
    </subscribe>
    <max_reads_per_connect>4000</max_reads_per_connect>
    <polling_interval>5</polling_interval>
    <skip_message_set_processing/>
  </agent>
</config>

↑ Go to top of page

Skip altering of database tables

By default, your Perspectium DataSync Agent will make changes to your database table(s) so that the table(s) in your application match with your database. For example, you DataSync Agent will add database columns and increase the size of database columns to ensure the complete syncing of data. You can, however, turn off this functionality by adding the <skip_alter/> directive within your agent.xml configuration file.

To skip the alteration of database tables for your DataSync integration, follow these steps:

1. Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.
   
   
2. Open your agent.xml file in a text editing application. Then, locate the <task> directive(s) within your <subscribe> directive, and nest the <skip_alter/> directive.
   
   

3. Save the changes you've made to your agent.xml and close the file. 
   

   Expand
   title Click here to see agent.xml example...
   Code Block language xml theme Eclipse <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?> <config> <agent> <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">https://testperspectium.

Monitoring DataSync Agent logs

The DataSync Agent generate log messages in the logs directory that is created upon installation of the agent. To view logs for your agent, open the perspectium.log file within the logs directory. 

Here are some examples of log messages that might appear within your perspectium.log file that indicate potential errors with your DataSync Agent:

2019-07-11 11:39:25.392 ERROR - main - Replicator - (stderr) [Fatal Error] :27:12: The element type "random" must be terminated by the matching end-tag "</random>".

2019-07-11 11:39:25.392 ERROR - main - Replicator - Error parsing null

2019-07-11 11:41:37.659 ERROR - main - Replicator - message_connection configuration directive is missing!

↑ Go to top of page

Set up a Highly Available Agent

Perspectium DataSync Agents support an “exclusive consumer” mode that provides a Highly Available Agent solution while maintaining the order of messages in a queue. This active-passive Highly Available approach will exclusively allow only one DataSync Agent to actively read messages from a queue. Other DataSync Agents that passively try to connect to the queue will only be given access to the queue if another DataSync Agent is no longer connected.

DataSync Agents use the queue in the message connection and locks that queue for exclusive use. If multiple tasks are made using the same message connection, only one subscriber task will be created and used. When running another DataSync Agent, a warning will be logged in the perspectium.log file indicating that the queue is in exclusive use and that consumer wait time will be increased (default is to increase to 30 seconds). Once the first running DataSync Agent is stopped or fails, the backup DataSync Agent will start consuming messages when the queue becomes available and the consumer wait time will be reset.

 NOTE: The Highly Available Agent is only available when your DataSync Agent is set to connect to the Perspectium Mesh via the AMQP/AMQPS protocol (HTTP/HTTPS is not supported).

Save the changes you've made to your agent.xml and close the file. An example agent.xml configuration for a Highly Available DataSync Agent is shown below:

1. net</message_connection>

1. <instance_connection

1. password="

1. encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4="

1. user="

1. test.user">https://myinstance.service-now.com</instance_connection> <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>

1. <decryption_key>The cow jumped over the moon</decryption_key> <database_type>mysql</database_type>

1. <database_server>localhost</database_server>

1. <database_port>3306</database_port>

1. <database_user>root</database_user>

1. <database_

1. password>encrypted:vlOtU71yu8N/EFIJH85SSMoilKLTeJHQrNZPJ7c5tFU=</database_password> <database_parms>characterEncoding=UTF-8 & useSSL=false</database_parms> <database_column_max_

1. size>251</database_column_max_size> <database>psp_repl</database> <skip_alter/>

1. </

1. task> </subscribe>

1. <max_reads_per_connect>4000</max_reads_per_connect>

1. <polling_interval>5</polling_interval>

1. <skip_message_set_processing/> </agent>

1. </config>

1. Run the agent again. 

↑ Go to top of page