DataPump is a contributed application which can be used to export ServiceNow data to MySQL, Oracle, Microsoft SQL Server or PostgreSQL. This application has two parts:

• A scoped ServiceNow app (x_108443_sndml) which is used to configure and manage export jobs. This app can be downloaded from the ServiceNow Developer Connect Share site. Go to https://developer.servicenow.com/connect.do#!/share/user/content and search for "DataPump". The app must be installed in the ServiceNow instance from which the exports will be sourced.
• A Java application (a.k.a. agent) which runs the exports. The Java app can be downloaded from https://github.com/gflewis/sndml3/releases. The ZIP file includes separate JAR files for MySQL, Oracle, Microsoft SQL Server and PostgreSQL. The Java app can be run from a command line prompt on a Linux or Windows server.

This article describes the steps to configure DataPump.

There are two additional articles in this series:

Create a Daemon User and Grant Roles

After installing the Update Set and downloading the JAR file, the first step will be to create a new ServiceNow user with the User ID "datapump.daemon". This account will be used by the Java agent to communicate with the ServiceNow instance.

• Assign the user a randomly generated password
• Set the user's time zone to GMT
• Grant the role x_108443_sndml.daemon

The daemon user requires "read" access to ServiceNow tables which will be exported. If there are existing roles (such as "itil") which grant this access, then grant those roles to the daemon user. Otherwise, you will need to configure ACLs which grant the necessary read access to the daemon user.

If you are testing in a PDI or sub-production instance, then you can grant "admin" role to the daemon user. However, this is not recommended in production.

Users with x_108443_sndml.admin role can configure and monitor DataPump jobs. However, only a ServiceNow administrator can grant the daemon user permission to read ServiceNow data tables.

Create a Database Agent Record

In your ServiceNow instance, go to DataPump > Agents and click New. Create a new Database Agent record with the name "main".

Create a Connection Profile

The connection profile is a Java properties file that contains credentials for the database and the ServiceNow instance, as well as other parameters that affect processing. The connection profile looks like this:

servicenow.instance=dev00000servicenow.username=datapump.daemonservicenow.password=secretpassworddatamart.url=jdbc:mysql://name-of-database-host/myschemadatamart.username=datapumpdatamart.password=secretpassworddatamart.schema=myschemadatamart.autocreate=truedaemon.scope=x_108443_sndmldaemon.agent=maindaemon.interval=120daemon.threads=3

daemon.continue=false

Since the connection profile contains passwords, it should be in a secure location on your Linux or Windows server.

Please note the following:

• datamart.url format will vary based on whether you are using MySQL, PostgreSQL, Oracle or Microsoft SQL Server. Please refer to the documentation on configuring a JDBC URL based on the type of your database.
• datamart.autocreate causes tables to be automatically created in the target database if the tables do not already exist.
• daemon.agent must match the agent name created in the prior step.
• daemon.interval causes the agent to check for new jobs every 120 seconds.
• daemon.threads is the maximum number of jobs that the agent can process concurrently. Additional jobs will wait until a thread becomes available.
• daemon.continue controls whether or not the agent will continue running after encountering an IO error in communication with ServiceNow or the database. If “true” then it writes an error to the log and goes back to sleep. If “false” then it will immediately abort.

Be sure to verify in advance that the datamart user has the ability to create new tables in the datamart schema.

For additional information on the connection profile refer to https://github.com/gflewis/sndml3/wiki/Connection-Profile.

Run the Java Application

There are two ways to run the Java application:

--scan will cause it to run once and terminate. The "interval" and "continue" properties are ignored.

--daemon will cause it to run in an endless loop until interrupted by a Ctrl-C or a signal. The app will periodically poll ServiceNow for new jobs based on the interval.

You can run the application from the command line using the following command:

java -jar -p --daemon

is the name of the connection profile created above.

is one of the following:

• sndml-3.4.1.mysql.jar - MySQL
• sndml-3.4.1-pg.jar - PostgreSQL
• sndml-3.4.1-ora.jar - Oracle
• sndml-3.4.1-mssql.jar - Microsoft SQL Server

After starting the agent app, view the previously configured Database Agent record in ServiceNow. Confirm that the Last Check-in date has been updated.

Configure a Database Table and a Job

For initial testing, choose a ServiceNow table which has a relatively small number of rows.

1. Go to DataPump > Agents.
2. Open the "main" agent configured above.
3. Click the New button above the Tables related list.
4. Select a Source table.
5. Save the record.
6. Click the New button above the Jobs related list.
7. For Action type select "Insert".
8. Save the record.

Run a Job

During normal operations, jobs are run via schedules. However, for testing or initial loading we can run jobs manually.

1. Open the Job record configured in the prior step.
2. Click Execute Now.
3. Note that a new Job Run record is created. The Status of the Job Run is initially set to "Ready".

When the Java agent next runs, it will detect any Job Runs with a Status of "Ready" and change the Status to "Running". If the Java agent is not already running, then run it now.

Verify the Data

Use any SQL query tool to verify that the table has been created in the designated schema and that it contains data.

Action Types

There are several types of jobs.

Insert

"Insert" is used for initial loading or reloading of SQL tables. It inserts rows into the target table. If a record with the same sys_id already exists in the target table, then a primary key violation will occur and the row will be skipped.

If Truncate is checked, then the SQL table will be truncated prior to the load.

Upsert

"Upsert" is used to load or update SQL tables. If the target record exists (based on sys_id), then it will be updated. Otherwise, it will be inserted.

If Since Last is checked, then only records inserted or updated in ServiceNow since the last run will be processed. The following filter will be used when retrieving records from ServiceNow:

sys_updated_on>=lastrunstart

where lastrunstart is determined from the "Last Run Start" field on the Database Table record.

Sync

"Sync" compares the timestamps (sys_updated_on) in the source and target tables. Based on this comparison it will insert, update or delete target records. If the values of sys_updated_on match, then the record will be skipped.

If a Filter has been configured for the Database Table, the Sync will delete any records which do not match the filter.

Create

"Create" creates a new empty table in the SQL database. If autocreate has been set to true in the connection profile, then this should be unnecessary.

Execute

"Execute" executes an arbitrary SQL statement. This is typically used to run a database stored procedure.