ServiceNow: SOAP Journal

Integration using Web Services

Category Archives: Java

SNDML Debugging

This post contains instructions for obtaining runtime diagnostics from SNDML 2.6.5.

Create a Log4J configuration file

SNDML 2.6.5 is built using Log4J 1.2. Create a file named log4j_debug.properties similar to the following:

# This file contains the Log4J configuration for the ServiceNow Datamart Loader
#
# The names of loggers in the "soap" package will be prefixed with "ServiceNow".
# The names of loggers in the "datamart" package will be prefixed with "Datamart".
#
# Most loggers for the "soap" package are NOT based on the class name.
# Instead, there are two loggers for each table, named as follows:
#   ServiceNow.Request.<tablename>
#   ServiceNow.Response.<tablename>
# Diagnostic information prior to the SOAP call will be written to the Request logger.
# Diagnostic information after the SOAP call will be written to the Response logger.
# 
# Logger levels are as follows:
# ERROR - Serious error that will result in termination of the program
# WARN  - Truncation or recoverable data conversion errors
# INFO  - One line per SOAP or Database call
# DEBUG - Display XML sent and received
# TRACE - Field level diagnostics (extremely verbose)

log4j.rootLogger=INFO, Console

log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d{HH:mm:ss} [%t] %p %c %m%n

log4j.logger.Datamart=DEBUG
log4j.logger.ServiceNow=DEBUG
log4j.logger.ServiceNow.Request=DEBUG
log4j.logger.ServiceNow.Response=INFO
log4j.logger.ServiceNow.Request.sys_db_object=WARN
log4j.logger.ServiceNow.Response.sys_db_object=WARN
log4j.logger.ServiceNow.Request.sys_dictionary=WARN
log4j.logger.ServiceNow.Response.sys_dictionary=WARN

Note that you can set the logging at different levels for different tables.

For general information on Log4J configuration files refer to http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/PropertyConfigurator.html.

Make the Log4J config file available in the class path

The Log4J configuration needs to be in the class path so that Log4J can load it as a resource. One way to do this is to place the file in the current directory and prepend the current directory (“.”) to the class path. You can specify the class path on the Java command line using the “-cp” option.

For Unix the use a colon for the path separator:

-cp .:sndml-2.6.5-mysql.jar

For Windows use a semicolon for the path separator:

-cp .;sndml-2.6.5-mysql.jar

Set log4j.configuration to the name of the config file

Use the “-D” option to set the system property log4j.configuration to the the name of the file.

-Dlog4j.configuration=log4j_debug.properties

Put it all together

You cannot use the “-jar” option with “-cp”. Instead you must specify the main class on the command line. The main class is servicenow.common.datamart.Loader.

The full Java command looks like this:

java -cp .:sndml-2.6.5-mysql.jar -Dlog4j.configuration=log4j_debug.properties servicenow.common.datamart.Loader -p <propfile> -f <scriptfile>

Where <propfile> is the SNDML properties file and <scriptfile> is the SNDML script file.

Time Zones and Date Formats

There is an inconsistent and somewhat unexpected behavior in how the Web Service API handles date-time fields.  This behavior is documented in the wiki: http://wiki.servicenow.com/index.php?title=Introduction_to_Time-Related_Functionality, and summarized as follows:

  • When reading data using Web Services:  all date and time fields are returned in GMT.
  • For updates, inserts and encoded queries:  date and time fields are assumed to be in local time.

To avoid time-zone problems, follow these two simple rules whenever using the Direct Web Services API.

  1. Create dedicated Web Services accounts in the sys_user table.  Do not use normal accounts which are assigned to real users.
  2. Set the time zone to GMT and the date format to yyyy-MM-dd for all Web Services accounts.

The inconsistent behavior is easily verified using a small perl script.  To reproduce the test, follow these instructions:

  1. Obtain a developer instance at https://developer.servicenow.com.
  2. Create a new user named “soap.test” with password “password”.
  3. Grant “soap.test” the following roles:
    • itil
    • soap_query_update
    • soap_create
  4. This script uses the ServiceNow::SOAP perl module.
  5. Edit the script, changing the instance to match your instance in step 1.
  6. Set the timezone for “soap.test” to anything other than GMT and run the script. Note that the dates do not match.
  7. Set the timezone for “soap.test” to GMT and run the script again.  Note that this time the dates match.
#!/usr/bin/perl
use strict;
use ServiceNow::SOAP;

my $instance = 'devxxxxx';
my $username = 'soap.test';
my $password = 'password';

my $sn = ServiceNow($instance, $username, $password);

# insert a change_request
my $change_request = $sn->table('change_request');

my $start_date = "2015-06-01 12:00:00";

my %result = $change_request->insert(
    short_description => 'Timezone change_request test',
    start_date => $start_date
    );

my $sys_id = $result{sys_id};
my $number = $result{number};

print "inserted number=$number sys_id=$sys_id\n";

# read the record back
my $newrec = $change_request->getRecord($sys_id);

# compare the times
print "expected start_date=", $start_date, "\n";
print "actual   start_date=", $newrec->{start_date}, "\n";

Note: This post from May 2013 was updated in May 2015 to use ServiceNow::SOAP.

Reading Large Tables

There are enhancements in the most recent release of the DataPump to better support loading from large tables, or I should say, unfiltered loading from large tables.  It is okay to frequently load data from large tables as long as your filter restricts the number of records.  However, unfiltered loads from large tables should happen rarely.  If you have a well designed load strategy then you should normally only load those records which have changed since your last load.

Nevertheless, there are times when it is necessary to perform large loads.  The most obvious situation is when a new data mart table is being seeded for the first time.  The DataPump philosophy is that it is okay for these loads to run a long time, because they are one-time (or at least rare) events.  You can kick off the load on a weekend when activity is at an ebb, and check back some hours later to see if it has finished.

ServiceNow limits the number of records that can be retrieved in a single Web Service request to 250.  If you need to read more than 250 records, there are two techniques:

  • getKeys – Use the getKeys method to return a complete list of sys_ids.  Then use repeated getRecords calls, each requesting 250 or fewer records, with an __encoded_query of the form sys_idINvalue,value,…
  • windowing – Use the extended query parameters __first_row and __last_row to fetch the records in chunks.

The advantage of the getKeys technique is that it is faster.  There is an up-front hit because you require the extra call to retrieve the list of keys.  However, the subsequent getRecords calls are much faster.  The disadvantage is that getKeys may encounter a timeout error if there are too many records.  I am not sure what the practical limit is here, but somewhere north of 100,000 rows, there is a risk that the getKeys call may never return.  So the windowing technique appears to be slower, but safer.

A few weeks ago I realized that getKeys can, itself, be windowed.  As a result, the DataPump now supports a new property: servicenow.getkeys.size.  If the property is 0 or omitted, then no getKeys windowing will be performed (i.e. the application will attempt to get all the keys in a single request).  This is the same as the behavior prior to this release.  However, if the property value is greater than zero the application will make repeated calls to getKeys until all keys have been fetched.

For the purpose of performance testing I chose to load a table with 3.1 million rows.  (The table was the cmdb_ci table.)  The property servicenow.chunksize was set to 100, meaning that records would be fetched 100 at a time.  The property servicenow.getkeys.size was set to 10000.  You can get better performance by setting these properties to higher values, but my objective was test the relative performance of using getKeys verses windowing.  The chunksize can be set as high as 250 and servicenow.getkeys.size should probably be set to 20000 or higher for normal operations.

I loaded the table twice.  Actually, I started two jobs at the same time, writing to different target tables but reading the same table.  As expected, the windowing job got off to a good start, and immediately began extracting and loading records at a rate of about 750 per minute.  The getKeys job had to make more than 300 Web Services calls to retrieve the 3.1 million keys at 10,000 a pop.  This took 2 hours and 18 minutes by which time the windowing job had already loaded 90,000 records.  Then the getKeys job took off.  It began extracting and loading records at a rate of about 10,000 per minute.  7 hours and 12 minutes after starting, the getKeys job had loaded all 3.1 million records.  The load rate for the getKeys job remained constant at about 10,000 per minute.  However, the windowing job slowed down as the __first_row parameter grew larger.  By the 7 hour mark it’s load rate had slowed to 400 rows per minute, and it was less than 8% of the way through the file.  The moral of this story is clear:  do not waste your time with windowing.

Post Script: Two weeks later

Since the slow job was running on a non-production instance, I allowed it to continue.  After a week it was 70% complete and the extract rate had dropped to 100 records per minute.  I finally killed the job because I could not bear to watch its tortured progress.  I was amazed that it would continue running for so long without some sort of “expired session” or other HTTP error.  Should I protect the innocent and simply remove the windowing option from the DataPump?  No.  I think not.  That would prevent someone else from reproducing my test results.