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