CLOB Configuration for DataSync Agent

By default, the DataSync Agent's maximum column size is 251 characters for character string type columns such as NVARCHAR.  When the Agent sees a ServiceNow column defined with more than 251 characters, it will create the column as a CLOB type in the database the Agent is connected to. Columns are created with this length and type to ensure all content is preserved from ServiceNow. 

Since ServiceNow uses their own translations to map column types they define for “fields” to the column types of the backend database that powers their platform, these fields don’t always have the exact same length as what’s shown in the ServiceNow UI. When a field is created with less than 251 characters in ServiceNow, that field is considered a single line string which maps to an NVARCHAR type with the same number of characters in the ServiceNow platform's backend database (generally MySQL).  If it’s more than 251 characters, that field would be mapped and created as a CLOB type in their backend database. 

As a result, when you define a field in ServiceNow with a type of “String” and a length of 400 and because this is mapped to a CLOB type in the backend database, you can actually enter a value greater than 400 characters in the field and it will save properly in ServiceNow both in the UI and in background scripts. When the data is sent out from ServiceNow using the Perspectium DataSync application, this field’s data will appear with more than 400 characters. 

If the agent were to create a column in the database with 400 characters, this would result in the data being truncated and loss of content. To prevent this, the Agent creates the field with a larger length and uses a CLOB type that has no length restrictions.

The Agent does provide a way to customize this value to a different one than the default value of 251. To do this, add the following directive to the agent.xml file saved in your Agent's installation directory:

 NOTE: Each Agent-supported database has a maximum value that a text column can be created as.  Any column size greater than the value specified below will be created as a CLOB type.

 NOTE: MySQL also has table row size limits that should be considered before setting this value too high as you may get "Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs" exceptions if you create too many columns as character string types and not CLOB types.

The following is an example of the agent.xml:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
  <agent>
     <max_reads_per_connect>4000</max_reads_per_connect>
     <polling_interval>5</polling_interval>
     <skip_message_set_processing/>
     <share/>
     <subscribe>
        <task instances="1">
           <task_name>test_subscribe</task_name>
           <message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.testqueue" user="admin" use_basic_consume="false">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>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>psp_repl</database>
           <database_column_max_size>4000</database_column_max_size>
        </task>
     </subscribe>
  </agent>
</config>

For more information, see DataSync Agent Configuration or contact Perspectium Support.

Can't find what you're looking for?  

See the FAQ or browse the Perspectium Community Forum.