You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »


For optional advance configurations for your DataSync Agent, contact Perspectium Support if you have any questions.

Prerequisites

(warning) First, you will need to install a DataSync Agent configured to your use case.

(warning) You will also need to create a ServiceNow dynamic share with an update trigger or create a ServiceNow bulk share.

(warning) Finally, make sure to stop running your DataSync Agent before making any Agent configuration changes.

What's on this page?



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: 

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
    <agent>
        <subscribe>
            <task>
                <task_name>example_subscribe</task_name>
                <message_connection password="encrypt:XXXX" user="XXXX" queue="..." >amqp://example.perspectium.net</message_connection>
                <instance_connection password="encrypt:XXXX" user="XXXX">http://example.service-now.com</instance_connection>
                <handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
                <decryption_key>The cow jumped over the moon</decryption_key>
                <database_type>sqlserver</database_type>
                <database_server>MyAddress</database_server>
                <database_port>1433</database_port>
                <database_user>XXXX</database_user>
                <database_password>XXXX</database_password>
                <database_parms></database_parms>
                <database>psp_repl</database>
 
                <uppercase_columns/>
 
            </task>            
        </subscribe>
        <max_reads_per_connect>2000</max_reads_per_connect>
        <polling_interval>5</polling_interval>    
    </agent>
</config>

The default (none of these directives) simply uses the original case of the column. If you happen to specify both, uppercase will be used.

(info) NOTE: This directive affects column names only. It does not affect table names. 


↑ Go to top of page



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.

(warning)  Before you begin, request the databases.xml file for your DataSync Agent by contacting Perspectium Support.

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.


↑ Go to top of page



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.

ServiceNow Field TypeDatabase Field Type
address

NVARCHAR

auto_increment

BIGINT

auto_number

NVARCHAR

boolean

BIT

catalog_preview

NVARCHAR

char

NVARCHAR

choice

NVARCHAR

collection

NVARCHAR

color

NVARCHAR

color_display

NVARCHAR

composite_name

NVARCHAR

compressed

NVARCHAR

condition_string

NVARCHAR

conditions

NVARCHAR

counter

NVARCHAR

currency

FLOAT

date

DATE

datetime

DATETIME2

day_of_week

BIGINT

days_of_week

NVARCHAR

decimal

FLOAT

default

NVARCHAR

document_id

NVARCHAR

documentation_fieldNVARCHAR
domain_idNVARCHAR
domain_number

FLOAT

domain_path

NVARCHAR

double

FLOAT

due_date

DATETIME2

email

NVARCHAR

email_script

NVARCHAR

external_names

NVARCHAR

field_list

NVARCHAR

field_name

NVARCHAR

float

FLOAT

glide_action_list

NVARCHAR

glide_date

DATE

glide_date_time

DATETIME2

glide_duration

DATETIME2

glide_encrypted

NVARCHAR

glide_list

NVARCHAR

glide_percise_time

NVARCHAR

glide_time

DATETIME2

glide_var

NVARCHAR

GUIDNVARCHAR
html

NVARCHAR

html_script

NVARCHAR

id

NVARCHAR

image

NVARCHAR

index_name

NVARCHAR

int

NVARCHAR

integer

BIGINT

integer_date

BIGINT

integer_time

BIGINT

internal_type

NVARCHAR

ip_address

NVARCHAR

journal

NVARCHAR

journal_input

NVARCHAR

journal_list

NVARCHAR

long

NVARCHAR

mask_code

NVARCHAR

metric_absolute

FLOAT

metric_counter

FLOAT

metric_derive

FLOAT

metric_gauge

FLOAT

mid_config

NVARCHAR

month_of_yearBIGINT
multi_small

NVARCHAR

multi_two_lines

NVARCHAR

name_values

NVARCHAR

nl_task_init

BIGINT

number

BIGINT

numeric

BIGINT

order_index

BIGINT

password2

NVARCHAR

password

NVARCHAR

percent_complete

FLOAT

phone

NVARCHAR

ph_number

NVARCHAR

phone_number

NVARCHAR

phone_number_e164

NVARCHAR

picklist

NVARCHAR

price

FLOAT

radio

NVARCHAR

↑ Go to top of page



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.

Enabling upsert is only available for SAP Hana.

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.

↑ Go to top of page