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.
Database Support
The following are a list of database the temporal agent supports:
- Oracle
- MySQL
- MSSQL
- SAP Hana 2.0
- PostGresSQL
How to set up temporal data in your agent
Prerequisites:
- You will need to install a DataSync Agent and create a ServiceNow dynamic share with an update trigger or create a ServiceNow bulk share.
To set up a temporal data in a local 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. 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:
<?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>
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. <max_datetime>2030-12-31 00:00:00</max_datetime> Replace the date and time stamp with any value you would like to assign for custom configuration.
| |
| <archive_datetime/> | Customize your temporal database table's archive date and time stamp in the psp_valid_to column. <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. | |
| <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. <column type="from">psp_valid_from</column> <column type="to">psp_valid_to</column>
|
| <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. <column primary_key="false">psp_from</column> |
| <timestamp_difference> | Customize a time period between temporal entries by adding <timestamp_difference> inside <temporal>. <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.
| |
| <skip_duplicates/> | Skip inserting duplicate entries by adding directive. <skip_duplicates/> inside <temporal>.
<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. <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.
<temporal> <update_on_bulk/> </temporal> |
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.Save the changes you made to your agent.xml file and close the file.
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.
