ServiceNow App
DataSync Agent
The DataSync SQL Agent leverages the default connection commit strategy of the JDBC driver for which Oracle is auto commit. The agent does not explicitly decide when to perform a commit. The JDBC driver makes this decision.
The Agent retrieves a message from the message store in the order they were published, performs the required processing such as decryption, validation etc., possibly determines the type of SQL operation required (such as update or insert), and then issues the request to the database. The Agent then determines the response and does any further processing required. Once completed, the Agent will fetch the next message from the message store in the queue.
NOTE: You can configure either multiple tasks to run against a single queue or you can configure multiple instances of a single task to run against a single queue. This is done primarily when throughput of the Agent is an issue. Both of these configurations introduce more than a single consumer of the queue and so the order in which the database transaction occurs could be different than the order of the messages within the message store due to scheduling of the task or thread.
- If you find that your DataSync Agent is running slowly due to a large number of SQL statements being processed, you may want to configure the Agent to perform SQL statements in batches.
NOTE: This feature should only be used when you send messages of the same table into one queue. If your queue has messages from different tables (including sys_audit, sys_journal_field, sys_attachment and sys_attachment_doc records), do not enable this feature as it will cause errors in saving records. To properly use this feature, separate your dynamic and bulk shares to save each type of table record to a different queue.
- To do this, open the agent.xml file that was created upon installation of your Agent in a text editing application. Within the <task> directive of the agent.xml file, nest the following directives:
| Directive | Description |
|---|---|
| <batch_update/> | Self closing tag that configures your Agent to batch SQL UPDATE statements |
| <batch_insert/> | Self closing tag that configures your Agent to batch SQL INSERT statements |
| <max_batch_size> | Number of SQL statements that will trigger a batch SQL statement execution. A larger suggested value is 200.
|
- In your agent.xml, change the <polling_interval> to a lower number
- In your agent.xml, increase the number of <tasks>
- NOTE: Increasing the number of task will require more memory and connections to the database. We do not recommend setting tasks to more than 10.
- In your agent.xml, change <message_connection> attributes so use_basic_consume=false
- In ServiceNow, change your bulk share configuration to Insert Only=true
Since Oracle only permits a string of 30 characters by default, a table or a column name that has over 30 characters will be truncated upon replication. The first 15 characters of the name will be displayed, followed by a “_” and then the last 14 of the table name. The “_” denotes the truncated values between the first 15 and the last 14 characters.
For example, when replicating the table “Workflow Estimated Runtime Configuration” (41 characters total) to Oracle database, the replicated name will be “u_workflow_esti__configuration”.
NOTE: When replicating a table or column that have similar names, meaning if the first 15 and the last 14 of the name are the same, only one of the two records will be replicated since after truncation, both of the names will be the same.