Setting ODBC properties
Summarize
Summary of Setting ODBC properties
This documentation explains how to customize and optimize connectivity and query behavior for the ServiceNow ODBC driver by configuring various properties. These settings enhance performance, troubleshooting capabilities, and compatibility based on your specific integration needs.
Show less
ODBC Administrator Properties
Configured via the ODBC Data Source Administrator or the Custom Properties field in the login dialog, these properties control key aspects of the ODBC connection:
- BatchSize: Sets number of records fetched per request (default 2000). Adjust to balance memory use and performance.
- url: The ServiceNow instance URL to connect to (default is https://demo.service-now.com).
- EnablePassThrough: Enables optimized aggregate function processing via direct web service calls (enabled by default).
- debug and gzip: Enable debug mode for HTTP traffic visibility (disable gzip compression when debugging).
- timeout: Socket inactivity timeout in seconds (default 175).
- retries: Number of retry attempts on socket timeout errors (default 0).
- mode: Query parsing mode for complex where clauses; OR (default) for compatibility or AND for efficiency.
- EnableDBSchema: Retrieves database schema for table listing in reporting tools (enabled by default, can be disabled to improve initial query performance).
- ExtendedSchemaCache: Controls caching of database schema across connections to reduce repeated schema requests (disabled by default).
- LegacyDurationTimeZone: Returns timer/duration fields in display timezone for legacy integration compatibility (disabled by default).
Multiple properties can be combined in the connection string separated by semicolons.
ODBC Management Console Properties
Accessible via the Windows Start menu under ServiceNow ODBC > Management Console, these properties provide administrative control over JVM settings, default instance URLs, disk cache sizes, and timeout values:
- ServiceJVMOptions: Configure JVM parameters like heap size (-Xmx) to optimize driver performance.
- DataSourceIPProperties: Sets the global default instance URL for all ODBC connections.
- ServiceSQLDiskCacheMaxSize: Defines maximum disk cache size to prevent cache file size errors (default 200).
- Timeout: Socket inactivity timeout, adjustable to prevent socket timeout errors.
- ServiceJVMLocation: Specifies the Java Runtime Environment location used by the driver; set during installation.
Service JVM Options
Additional JVM options can be specified to control logging:
- -DLOGFILENAME: Path for ODBC log files.
- -DLOGLEVEL: Log verbosity level (e.g., INFO, TRACE, ERROR).
Instance Properties
Administrators can configure instance-level limits on data retrieval to control query results:
- glide.db.max.aggregates: Maximum rows returned by aggregate functions (default 100,000).
- glide.db.maxviewrecords: Maximum rows returned by database views (default 10,001).
Practical Implications for ServiceNow Customers
By configuring these ODBC properties, ServiceNow customers can tailor their ODBC connections for optimal performance, efficient data retrieval, and enhanced troubleshooting. Adjusting batch sizes and enabling pass-through mode can accelerate queries, while schema caching and disabling unnecessary schema retrieval improves responsiveness in reporting tools. Debugging options assist in diagnosing connectivity issues, and JVM tuning helps maintain driver stability under load. Instance property limits ensure queries do not overwhelm the system.
The following properties customize connectivity and optimize the query behavior of the ODBC driver.
ODBC administrator properties
| Property Name | Description | Default |
|---|---|---|
| BatchSize | During fetching of results from the instance, this batch size configures the number of records to fetch for every request. Typically, the default is an optimal number for normal sized rows. If an error occurs during fetching of records that indicates this value should be lowered, you can modify it to optimize memory usage versus performance. | 2000 |
| url | This is the ServiceNow instance URL or endpoint. It should indicate the URL to the ServiceNow instance you want to connect to. | https://demo.service-now.com |
| EnablePassThrough | During processing of aggregate functions, enabling pass through mode allows directly calling Aggregate Web Service for optimized and speedy response. Whenever possible, this mode should be left enabled. | true |
| debug | By default, debugging messages are not produced. Set debug to true when you operate the ODBC driver from the ISQL console window to write all HTTP-related network communication traffic to the console window. When using this option, set gzip to false so that data is not compressed. Otherwise, the data is unreadable. | false |
| gzip | By default, data sent over the network is compressed. Set gzip to false when using the debug parameter to write network communication to the ISQL console so that data is not compressed. | true |
| timeout | Specifies the socket inactivity timeout value in seconds. | 175 |
| retries | Number of times to retry the failing request in the event of a socket timeout error. | 0 |
| mode | The query mode used to parse complex where clauses. You can configure the
ODBC driver query mode to use either AND or OR operators. While the OR operator provides the greatest compatibility with complex queries, the AND operator is usually more efficient and results in fewer database operations. |
or |
| EnableDBSchema | The ODBC driver issues a database schema request to retrieve table names from the instance. This functionality is enabled by default so reporting applications such as Microsoft Excel can display a list of tables to query from. Disabling this property may improve the performance of the first query sent from a reporting application, especially if the instance has a large number of tables. | true |
| ExtendedSchemaCache | The ODBC driver caches the database schema for each connection. When a new connection is created, the driver clears the database cache and queries the database schema from the instance again. This behavior is beneficial when connecting to different data source, or when modifying the table schema. When querying a single data source with a consistent schema, enable this property to avoid sending unnecessary schema requests, including when EnableDBSchema is true. | false |
| LegacyDurationTimeZone | The ODBC driver returns timer and duration field values in the UTC timezone by default, starting with the 1.0.10 version. When this property is true, the ODBC driver returns timer and duration field values using the display value, as shown in the UI. This property can be used to preserve compatibility with legacy integrations that depend on the display value. See KB0583982 for details about this behavior. | false |
If you need to use more than one of these properties in your connection, concatenate the settings with a semicolon (;) delimiter. For example, the following string sets the URL to a specific instance and changes the batch size to 200 records.
url=https://demo1234.service-now.com;BatchSize=200
ODBC management console properties
| Property name | Description | Default |
|---|---|---|
| ServiceJVMOptions (Services\Service Settings\IP Parameters) |
JVM command line properties and option. For example, to change the maximum Java heap size, modify the -Xmx150m parameter. | -Xms64m -Xmx150m |
| DataSourceIPProperties (OpenAccess SDK 6.0 Manager\<installation location>\Services\ServiceNow_ODBC\Data Source Settings\ServiceNow\IP Parameters) |
Global default of the instance URL for all ODBC connections. For more flexibility, you may also create new DSNs with default URL configurations. | https://demo.service-now.com |
| ServiceSQLDiskCacheMaxSize (Services\Service Settings\SQL Engine Parameters) |
Specifies the maximum size of the disk cache files. Increase this value when
you see Disk Cache file size limit has reached errors. |
200 |
| Timeout (Data Source Settings\ServiceNow\IP Parameters) |
Specifies the socket inactivity timeout value in seconds. Increase this to a
value greater than glide.soap.request_processing_timeout.odbc when you see
GetKeys failed (Socket timeout) errors. |
175 |
| ServiceJVMLocation | Contains the JRE location used by the ODBC driver. | There is no default value for this property. You are prompted to enter the JRE location at the time of installation. |
Service JVM options
| Option | Description | Default |
|---|---|---|
| -DLOG_FILE_NAME | The location of the ODBC log file. This property is available starting with the ODBC driver 1.0.7.1 release. | ${user.home}\AppData\Local\ServiceNow\odbc\logging\odbc.log |
| -DLOG_LEVEL | The logging level used when writing to the ODBC log file. You can specify the logging level using Logback levels, such as TRACE, INFO, or ERROR. This property is available starting with the ODBC driver 1.0.8 release. | INFO |
Instance properties
| Property name | Description | Default |
|---|---|---|
| glide.db.max.aggregates | The maximum number of rows returned by aggregate functions. | 100000 |
| glide.db.max_view_records | The maximum number of rows returned by a database view. | 10001 |