You can configure your Perspectium DataSync Agent to share data from ServiceNow or Salesforce to an Oracle database by changing some additional configurations in your agent.xml file.
Prerequisites
First, you will need to install the DataSync Agent and make sure that your agent.xml file is configured correctly.
You will also need to create a ServiceNow dynamic share/bulk share.
Make sure to stop running your DataSync Agent before making any Agent configuration changes.
Finally, you will need to create a database in Oracle that will store data replicated via the DataSync Agent.
NOTE: The Agent can partitioning and table compression as long as there is enough storage in the tablespace and the table is created with the same primary keys as the Agent would create it (i.e. sys_id and any temporal fields if applicable). For any questions, contact support@perspectium.com.
NOTE: The Agent requires six open cursors per ServiceNow table to be replicated as it uses prepared statements to ensure the most efficient processing possible. For each operation per table (insert, update, delete, query metadata) we use a prepared statement which is reused whenever the next similar operation needs to be executed against the same table. Each of these prepared statements uses a cursor. If we didn't use an open cursor, every transaction would be very inefficient because the Agent would need to re-establish the client/server connection and query the structure each time.
Procedure
To set up your DataSync Agent to share application data to an Oracle database, follow these steps:
Access your agent.xml configuration file
Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.
Update database directives to point to Oracle
Open your agent.xml file in a text editing application. Then, locate the <task> directive(s) within your <subscribe> directive, and update the following database-related directives:
| Directive | Value to enter |
|---|---|
| <database_type> | oracle |
| <database_server> | URL for your Oracle database |
| <database_port> | 1521 |
| <database_user> | Username used to access your Oracle database |
| <database_password> | Password used to access your Oracle database |
| <database_sid> | System Identifier (SID) used to access your Oracle database |
NOTE: A TNS connect string can also be used to connect to your Oracle database. To configure your Agent in this way, enter the TNS connect string as the value for the <database> directive and delete the <database_port> and <database_sid> directives.
NOTE: If you are using Oracle 12.2+ with the 12.2 JDBC driver, you can set the table and column names to support Oracle 12.2's larger 128 bytes limit (prior to 12.2, the limit was 30 bytes meaning table and column names could only be up to 30 standard characters). To do so, follow these steps:
- Request the databases.xml file for your DataSync Agent by contacting Perspectium Support.
- Once you've received the databases.xml file, open the file in a text-editing application and locate the configurations for <database_type>oracle</database_type>.
- Within the parent <database> directive, locate the <identifier_max_length> directive and replace the value of 30 with preferred value up to 128. Do not enter any values greater than 128 as it will result in Oracle errors when trying to create tables and columns.
- Save the databases.xml file and put the file within the conf directory that was created upon installation of your DataSync Agent.
- Verify you have the ojdbc8-12.2.0.1.jar in the jars directory that was created upon installation of your DataSync Agent. This should be included by default when the Agent was installed. However if it isn't, download the Oracle 12.2 JDBC driver jar, specifically the ojdbc8.jar. This jar will be named with the version number such as ojdbc8-12.2.0.1.jar. Replace the ojdbc8.jar within the jars directory.
Add the <skip_database_creation/> directive
Locate the <task> directive(s) in your <subscribe> directive, and nest the following directive:
| Directive | Description |
|---|---|
| <skip_database_creation/> | Bypasses the dynamic creation of the replicated database
|
Your agent.xml file should look similar to the example shown below:
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
<agent>
<share/>
<subscribe>
<task instances="1">
<task_name>OracleAgent_subscribe</task_name>
<message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.oracletest" use_basic_consume="false" user="exampleuser">https://example.perspectium.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 and the sun</decryption_key>
<database_type>oracle</database_type>
<database_server>localhost</database_server>
<database_port>1521</database_port>
<database_user>system</database_user>
<database_password>encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4=</database_password>
<database_sid>orcl</database_sid>
<skip_database_creation/>
<database>psp_repl</database>
</task>
</subscribe>
<max_reads_per_connect>4000</max_reads_per_connect>
<polling_interval>5</polling_interval>
<skip_message_set_processing/>
</agent>
</config>
Save your agent.xml file
Save the changes you made to your agent.xml file and close the file.
Run your DataSync Agent
After configuring your agent.xml file to support replication to your Oracle database, start running your DataSync Agent again.