For optional advance configurations for your DataSync Agent, contact Perspectium Support if you have any questions.
First, you will need to install a DataSync Agent configured to your use case.
You will also need to create a ServiceNow dynamic share with an update trigger or create a ServiceNow bulk share.
Finally, make sure to stop running your DataSync Agent before making any Agent configuration changes.
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:
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.
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.
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.
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.
- Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.
- Open your agent.xml file in a text editing application. Then, locate the <task> directive(s) and nest the <enable_upsert/> directive.
Save the changes you've made to your agent.xml and close the file. Your agent.xml should look similar to the example shown below:
- Run your agent again.
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.
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).
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).
WARNING! If you have already configured an integration with a DataSync Agent and have been saving records in a database, but you would now like to enable or disable temporal replication, update the value within the agent.xml's <database> directive to a new database. Otherwise records will not be processed properly.
The following are a list of database the temporal agent supports:
- Oracle
- MySQL
- MSSQL
- SAP Hana 2.0
- PostGresSQL
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.
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:
- Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.
- 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.
Save the changes you've made to your agent.xml and close the file.
- Run the agent again.