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> | db2snowflake | | <database_server> | URL/IP address for your | DB2 Snowflake database. | If your database is installed on your local machine, set this value to localhost.<database_port> | | Port number for your DB2 database | <database_user> | Username used to access your | DB2 Snowflake database | | <database_password> | Password used to access your | DB2 Snowflake database | | <database_parms> | characterEncoding=UTF-8 | To specify the schema, warehouse and role to access your Snowflake database schema=<SCHEMA_NAME>&warehouse=<WAREHOUSE_NAME>&role=<ROLE>
For example: schema=PUBLIC&warehouse=COMPUTE_WH&role=ACCOUNTADMIN would be used if your schema name is PUBLIC, your warehouse name is COMPUTE_WH and your role is ACCOUNTADMIN | | <database> | Name of your Snowflake database | | <database_port> | (Optional) Port number to access your Snowflake database | | <enable_snowflake> | To enable the DataSync Agent for Snowflake. By default, the Agent is not enabled for Snowflake because of the aforementioned performance limitations with standard SQL operations. |
|
| | <files_directory> | To specify a local directory where the Agent is running and has access to so it can save its temporary CSV files. These files are used push to Snowflake staging tables in order to do the COPY command for bulk loading data from the local file system. | | <file_max_size> | To specify how large the temporary files will be before they are pushed to Snowflake. To optimize performance, records should be accumulated in the temporary files before they are pushed so we're not continuously pushing files to Snowflake which can affect performance.  NOTE:
- The size can be in KB, MB, or GB, i.e. 50KB, 250MB, 1GB. Make sure to have NO space in between the number and the unit.
- The minimum value for this directive is 25KB. If you input a value less than 25KB, the value will be set to 25KB.
- Conversely, the maximum value for this directive is 10GB. If you input a value greater than 10GB, the value will be set to 10GB.
| | <uppercase_columns> | (Optional) To specify if the columns should be saved in uppercase lettering in the Snowflake database. Since ServiceNow's columns are lowercase, this will convert them to uppercase for querying and saving content into Snowflake (i.e. sys_id becomes SYS_ID). It is recommended to enable this configuration since Snowflake by default uses uppercase lettering. | | <flush_interval> | (Optional) To specify how often the DataSync Agent will flush its temporary files and push to Snowflake if they have not accumulated to the size specified in <file_max_size>. Values are entered as whole numbers to represent minutes, such as 10 to represent 10 minutes. The default value is 5 for 5 minutes if this configuration is not specified. This way, we don't wait too long to push changes if there are no more records to be shared. For example, if you share 100 records and your <file_max_size> configuration is 400KB and then 80 records fit within the 400KB, those 80 records will be pushed but the remaining 20 records will stay in the next temporary file until it reaches 400KB. This flush interval ensure they get pushed in case there isn't any more sharing done for now, in which case we wouldn't reach 400KB again for some time. | | <delete_files_interval> | (Optional) To specify how old the temporary files should be before they are deleted. To ensure your temporary files don't take up too much space on the file system while still keeping them for any troubleshooting purposes, we only delete the files once they get older (last modified) beyond a certain day/time related to the current date and time. The value is used each time the Agent runs to flush its temporary files (using the above <flush_interval> configuration), checking this configuration for any temporary files older than the value specified here related to the current time. For example, if the value is 12H (for 12 hours), when the Agent runs its flush, it will check for any temporary files older than 12 hours from now and delete those files. NOTE: - The value can be entered in hours (H) and days (D) i.e. 12H (for 12 hours), 3D (for 3 days). Make sure to have NO space in between the number and the unit.
- The default value is 24 hours if this configuration is not specified.
|
Your agent.xml file should look similar to the example shown below: | Code Block |
|---|
| <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
<agent>
<share>
</share>
<subscribe>
<task>
<task_name>test_db2snowflake_subscribe</task_name>
<message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" user="exampleuser">amqps://example.perspectium.net</message_connection>
<instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" user="example.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>db2<type>snowflake</database_type>
<database_server>localhost<server>servername.snowflakecomputing.com</database_server>
<database_port>50000</database_port>
<database_user>exampleuser</database_user>
<database_password>examplepassword</database_password>
<database_params>characterEncoding=UTF-8<_params>schema=PUBLIC&warehouse=COMPUTE_WH&role=ACCOUNTADMIN</database_params>
<database>SAMPLE</database>
<enable_snowflake/>
<uppercase_columns/>
<files_directory>/usr/local/datasyncagent/tempfiles</files_directory>
<file_max_size>500KB</file_max_size>
<flush_interval>3</flush_interval>
<delete_files_interval>12H</delete_files_interval>
</task>
</subscribe>
<max_reads_per_connect>4000</max_reads_per_connect>
<polling_interval>30</polling_interval>
</agent>
</config>
|
| 
First, you will need to Install the DataSync Agent.