ODBC behavior

  • Release version: Yokohama
  • Updated January 30, 2025
  • 3 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of ODBC behavior

    The ODBC driver for ServiceNow allows querying instance databases from various client applications. It supports aggregate functions, handles date/time values considering time zone differences, and provides access to display values for enhanced querying. Understanding its behavior helps optimize data retrieval and maintain accuracy across different environments.

    Show full answer Show less

    Key Features

    • Aggregate Functions: The driver supports COUNT, SUM, MIN, MAX, and AVG. To improve performance of aggregate queries, activate the Aggregate web service plugin.
    • Date and Time Values: The ODBC driver returns date/time values in the local time zone of the client application, which may differ from the instance time zone. This requires querying with awareness of both time zones to ensure accurate filtering. Duration and timer fields are returned in UTC starting from version 1.0.10.
    • Display Values: For Choice, Reference, Duration, and Timer columns, additional columns prefixed with dv provide display values (e.g., dvcallerid). These can be used in SELECT statements, filter conditions, and aggregate queries, enabling optimized queries that reduce the need for additional lookups.
    • Accessing Table and Column Names: Users can query internal ODBC tables (oatables and oacolumns) to list accessible tables and columns based on their read ACLs, facilitating exploration of the schema available through ODBC.
    • Routing Queries to Read Replica: SELECT queries that are CPU-intensive can be routed to read replica databases to reduce load on the primary database. This requires configuration by enabling the ODBC category in Secondary DB settings.
    • Field Length Configuration: The field length in SQL queries is limited by the ServiceNow dictionary entry. This maximum length can be increased to prevent data truncation in query results.

    Practical Implications for ServiceNow Customers

    • When using ODBC queries involving aggregate functions, enabling the Aggregate web service plugin enhances performance.
    • Be mindful of time zone differences between the instance and the client machine to ensure correct date/time filtering; adjust query times accordingly.
    • Use display value columns (dv prefixed) to simplify queries and improve efficiency by avoiding extra joins or lookups for reference and choice fields.
    • Leverage the internal oatables and oacolumns tables to discover accessible tables and columns based on user permissions, aiding in query building.
    • Configure query routing to Read Replica to optimize performance for heavy SELECT queries without impacting the primary database.
    • Adjust field length limits in dictionary entries when necessary to ensure complete data retrieval without truncation.

    After testing the ODBC driver, you can use it to query your instance database from a variety of client applications.

    ODBC aggregate functions

    The ODBC driver attempts to download data and apply aggregate functions locally. The ODBC driver supports the following aggregate functions.

    • COUNT
    • SUM
    • MIN
    • MAX
    • AVG

    Activate the Aggregate web service plugin to improve the performance of aggregate queries through the ODBC driver.

    ODBC date and time values

    The instance and the machine on which the ODBC driver is installed may use two different time zones. Date and time values returned by the ODBC driver are in the local time zone of the application using the driver, not the ServiceNow instance time zone.

    Ensure that you query in accurate time zones for both the instance and the machine that hosts the ODBC driver. GlideRecord performs filtering based on the instance time zone, and the ODBC client is filtered based on the Windows time zone.

    For example, an instance is in Central Standard Time (CST), and the ODBC driver is installed on a machine that is in Pacific Standard Time (PST). An incident is created on the instance at 2014-05-20 10:00:00, and the time that the incident was created is displayed in the UI as 10:00:00 for users in both time zones. However, in order to successfully query this incident by creation date and time, a user on the machine in PST must query 2014-05-20 08:00:00 instead of 2014-05-20 10:00:00.

    Duration and timer type fields are returned using the UTC time zone, starting with ODBC version 1.0.10. See KB0583982 for details about this change.

    ODBC display values

    Some examples of how to use and work with ODBC display values are shown below.

    • Display values in Choice and Reference columns:

      When querying a column of type Choice, Reference, Duration, or Timer, an additional column with the prefix dv_ is available that contains the display value. For example, you can select dv_caller_id to return the sys_user.name display value of the reference field from an incident record without making another request to the sys_user table.

      Figure 1. Return the display value
      Return the display value
    • Display values in filter conditions:

      Display values can also be used in a filter condition. The ODBC driver optimizes the query condition and processes the filter on the server, for example, querying on the display value of sys_user for the caller_id field of an incident by using the dv_caller_id field name.

      Figure 2. Display values in filter conditions
      Display values in filter conditions
    • Display values in aggregate queries:

      Aggregate queries can also take advantage of display values if you specify them in the group by or where clause, for example, grouping on the caller_id field of an incident, as well as specifying a filter for it. The query is optimized by passing through to the server.

      Figure 3. Display values in aggregate queries
      Display values in aggregate queries

    Querying table and column names

    You can get a list of accessible tables and columns based on the read ACLs for the querying user.

    • The following query returns the names of all tables for which the querying user has read access:

      select * from oa_tables;

    • After you know the name of the table you want to query, you can query the names of all columns for which the user has read access. The querying user must have read access for both the table and the columns.

      select * from oa_columns where table_name=‘table_name’;

    Note:
    The oa_tables and oa_columns tables are internal ODBC tables. These tables are accessible only via the ODBC driver.

    Routing ODBC calls to Read Replica

    Query routing is done to take the load off the primary database for SELECT queries that take numerous DB CPU cycles on the primary DB. For more information, see Introduction to ServiceNow Read Replica Databases.

    For routing ODBC calls to Read Replica, go to All > Secondary Database > Secondary DB Categories and set odbc to true.