This Quick Start Guide contains instructions for installing and testing the ServiceNow DataPump. The Quick Start guide uses a composite JAR file, servicenow-sql-pump.jar created using One-JAR and containing embedded copies of the Oracle and MySQL JDBC drivers, Log4J, JDOM and the sqltemplates.xml configuration file.
This Quick Start Guide assumes that you are using Oracle. For testing I recommend Oracle Express Edition which can be downloaded and installed for free. I also recommend Oracle SQL Developer as a great tool for testing JDBC connectivity and submitting ad-hoc SQL.
1) Install and Configure Oracle
You will require an Oracle account with CREATE TABLE role. Verify that your user has create table capability by typing in these two commands:
SQL> create table foo (bar varchar2(10), primary key(bar)); Table created. SQL> drop table foo; Table dropped.
2) Download servicenow-sql-pump.jar
The file servicenow-sql-pump.jar can be downloaded from the project download page.
3) Configure ServiceNow Web Services access
If you are testing this application using a public demo sandbox instance, then you can use the username "admin" with the password "admin". Otherwise you will need to ensure that Web Services are enabled for your instance and you will need to create a role and a user for the application.
- Create a role named "datapump". The role must contain the following roles:
- Grant the datapump role read access to the following tables:
- Ensure that the datapump role has read access to any tables you intend to replicate. Be sure to check for field level Access Controls which could interfere with operation.
- Create a ServiceNow user named "soap.datapump". The password will be specified in the datapump.properties file.
5) Create datapump.properties
Create a text file named datapump.properties with seven lines as follows. Provide your ServiceNow and Oracle connection parameters. The "oracle2" dialect from sqltemplates.xml is recommended since it is the most thoroughly tested.
# ServiceNow connection parameters servicenow.url=https://demo001.service-now.com/ servicenow.username=admin servicenow.password=admin # Database connection parameters pumper.sql.url=jdbc:oracle:thin:@localhost:1521:xe pumper.sql.username=scott pumper.sql.password=tiger # Dialect controls which section of sqlconfig.xml will be used pumper.sql.dialect=oracle2
6) Create a simple script
Create a file named load_location.txt with a single line as follows:
7) Execute the DataPump
java -jar servicenow-sql-pump.jar -p datapump.properties -f load_location.txt
At this point the application should create a table named cmn_location in your Oracle database, and copy into it the contents of the ServiceNow cmn_location table. If you run the application a second time it will detect that the Oracle table already exists. It will match field names between the ServiceNow and Oracle tables, and update or insert records using sys_id.
If you have gotten this far successfully, then proceed to the Scripts to read about more loading options.