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.
To change the data types in the databases.xml, complete the following steps:
In the databases.xml file, open the file in a text-editing application and locate the configurations for your database type <database_type><your database></database_type>.
Change the data types to your configuration inside the <mapping> tag.
For example, if you have an Oracle database and you want to use CLOB instead of NCLOB, change
<mapping sn_type="2011" db_type="2011">NCLOB</mapping>
to
<mapping sn_type="2011" db_type="2011">CLOB</mapping>
Save the databases.xml file and put the file within the conf directory that was created upon installation of your DataSync Agent.
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.
To enable upsert to your database, 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) 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:
<?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>
After configuring your agent.xml file to enable upsert to your database, start running your DataSync Agent again.