DataPump is a contributed application which can be used to export ServiceNow data to MySQL, Oracle, Microsoft SQL Server or PostgreSQL. The introductory article, Exporting to MySQL, Oracle or SQL Server with DataPump, explains how to install DataPump and how to create and execute DataPump jobs. This article addresses how you can schedule those jobs to be automatically run after they have been created and tested.
Creating Schedules
DataPump jobs can be grouped together in Schedules, and automatically activated by the ServiceNow scheduler. The steps are as follows:
- Create a Schedule by going to DataPump > Schedules, and clicking New.
- Create Jobs by going to DataPump > Tables, opening a table, and clicking the New button above the Jobs related list.
- The Job must be saved before it can be added to a Schedule. To add a Job to a Schedule, edit the Schedule field on the Job form.
- To test a Schedule, open the Schedule form and click the Execute Now button.
If a Job is part of a Schedule, then the Order field on the Job form becomes important. Jobs within a Schedule are processed in order, based on the Order field. If multiple Jobs have the same Order number, then they may run concurrently, subject to the number of available threads. (The number of threads is configured in the connection profile.) Jobs with a higher order number will remain in a "Scheduled" state until Jobs with a lower Order number complete.
This screenshot shows a schedule with three jobs. The table sys_user_grmember will be exported after the other two jobs complete.
All Jobs within a Schedule will have the same "start time", regardless of when they actually start running. The Java agent will only export records that were inserted before the "start time". "Start time" is based on when the Job Run record was created, not when the Status was changed to "Running". All Job Run records in a Schedule Run are created at the same time, therefore the application will not export records inserted after the start of another job in the same schedule.
Running Scheduled Jobs
Once a Job Run record is created with a state of "Ready", it must be be detected by the Java agent. There are several techniques for managing this. One technique is to use cron or Windows Task Scheduler to execute the Java agent, and to synchronize the times with your ServiceNow schedules. For example, if you know that your ServiceNow schedules are set to run at the top of the hour, then create a cron or Windows Task Scheduler job which runs a couple of minutes later.
The SNDML JAR file contains an embedded Log4J2 Rolling File Appender configuration which can be helpful if you are using cron or Windows Task Scheduler. The name of this configuration file is log4j2-daemon.xml, and it requires two system properties:
- sndml.logFolder - the directory where log files are written
- sndml.logPrefix - a prefix which will be prepended to the log file name
Use this command to run the Java agent redirecting all output to the log directory:
java -Dlog4j2.configurationFile=log4j2-daemon.xml ‑Dsndml.logFolder=<path_to_log_directory> ‑Dsndml.logPrefix=<name_of_agent> -jar <path_to_jar> -p <path_to_connection_profile> --scan
Note that a "-D" prefix is used when passing system properties to Java, and that system properties are case sensitive.
For Linux, use this crontab entry will run the agent at 2, 17, 32 and 47 minutes past the hour:
02,17,32,47 * * * * java -Dlog4j2.configurationFile=log4j2-daemon.xml -Dsndml.logFolder=<log_directory> ‑Dsndml.logPrefix=datapump-cron -jar <jar_file> -p <connection_profile> --scan >/dev/null 2>&1
Monitoring Jobs
The DataPump application has the ability to automatically open an Incident ticket if an error occurs while processing a job. This is controlled by setting the "Job fail template" field on the Agent form.
A sample template named "DataPump Job Fail Template" is installed by the Update Set. You can modify or copy this template. Typically you will want to make a copy of the template which sets Incident fields appropriate to your environment such as Assignment group, Category, Service, Impact and/or Urgency.
When the Incident is created, the following values can be inserted into the Short description and/or Description:
- {0} - sys_id of Job Run
- {1} - number of Job Run
- {2} - Name of Job
- {3} - Error message
Testing Job Failure Behavior
An east way to test job failure behavior is to set Min Rows or Max Rows on the Job form to an unreasonable value. This Job is sure to fail since sys_user_grmember contains more than 10 rows.
This is how the failure appears on the Job Run form.
Clearing a Job Failure
The "Execute Now" UI Action on the Job form be unavailable if a Job is currently running or has failed. Use the "Clear Last Failure" UI Action to clear the failure so that the job can be re-run.
Version 1.1 of DataPump does not prevent a Schedule from running a job that is already running or has failed.
