JDBC Data Source: Batch Size Does Not Limit Source Query Row Retrieval, and Interaction with Use Last Run Datetime and Pagination Fields - Support and Troubleshooting

JDBC Data Source: Batch Size Does Not Limit Source Query Row Retrieval, and Interaction with Use Last Run Datetime and Pagination Fields Issue .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } When using a JDBC-type Data Source, the Batch Size field on the Data Source record and the related system properties (glide.import_set_load_usebatch and glide.import_set_load_batch_size) do not limit the number of rows retrieved from the external source database. The JDBC probe executes a full SELECT query regardless of the configured Batch Size value.

Additionally, the Support Pagination, Limit, and Offset fields visible on the Data Source form have no effect on JDBC-type data sources, and combining a SQL LIMIT clause with Use Last Run Datetime = true produces an invalid SQL query.

Symptoms .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } A JDBC Data Source configured with Batch Size = 1 (or any other value) still imports all rows from the source table when "Load All Records" is clicked. The MID Server log shows the JDBC probe executing a full SELECT * query with no LIMIT clause, despite the Batch Size being set. Import operations on large source tables fail with timeout errors because the entire table is retrieved in a single query. Setting Support Pagination = true with Limit and Offset values on a JDBC Data Source has no effect on the SQL query or the number of rows retrieved. Adding a LIMIT clause to the SQL Statement field while Use Last Run Datetime is enabled results in a SQL syntax error because the system appends the WHERE condition after the LIMIT. Facts .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Batch Size / glide.import_set_load_batch_size: These settings control how records are batched during the import load processing (i.e., how many rows are committed to the staging table per transaction batch). They do not modify or limit the SQL query sent by the JDBC probe to the external database. Use Last Run Datetime: When enabled, the system automatically appends a WHERE >= ' ' clause to the end of the SQL statement. This is an open-ended lower bound with no upper bound. The appended clause is always placed at the very end of the query, after any existing ORDER BY, GROUP BY, or LIMIT clauses, which can result in invalid SQL. Support Pagination / Limit / Offset: These fields are documented under the REST (Integration Hub) type data source fields section of the product documentation only. Although these fields may appear on the Data Source form regardless of the selected type, they are not applicable to JDBC-type data sources and have no effect on the JDBC probe behavior. Load All Records: This related link is the full-import path by design. It triggers the JDBC probe to retrieve the complete result set based on the configured query. Release .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } All supported ServiceNow releases. Verified on Zurich Patch 6 Hot Fix 1.

Cause .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } The Batch Size field on a JDBC Data Source controls import-side batching behavior (transaction commit batch size), not the SQL query row retrieval. The JDBC probe generates and executes the source query independently of the Batch Size configuration. When the Data Source is configured with Query = "All Rows from Table", the probe issues a full SELECT * against the source table.

The Support Pagination, Limit, and Offset fields are designed for REST (Integration Hub) type data sources and are not honored by the JDBC probe execution path.

The Use Last Run Datetime feature appends a WHERE clause to the very end of the SQL statement provided. This appended condition is an open-ended lower bound (>=) with no upper bound, meaning it retrieves all rows from the specified date to the present. If the SQL statement already contains a LIMIT, ORDER BY, or GROUP BY clause, the appended WHERE condition is placed after those clauses, producing syntactically invalid SQL. For example:

Original SQL Statement: SELECT * FROM schema.table ORDER BY insert_date ASC LIMIT 10

Resulting query generated by the platform: SELECT * FROM schema.table ORDER BY insert_date ASC LIMIT 10 WHERE insert_date >= '2025-03-25 09:53:55'

This is invalid SQL because the WHERE clause cannot appear after LIMIT.

Additionally, because Use Last Run Datetime only applies a lower bound, it cannot be used to create bounded date windows for chunked loading of historical data. Setting the Last Run Datetime to an old date still returns all rows from that date to the present, which can cause the same timeout on large tables.

Resolution .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } To manage data volume on JDBC Data Source imports from large source tables, use the following two-phase approach:

Phase 1 — Historical Backfill (Use Last Run Datetime = false):

For the initial historical data load, keep Use Last Run Datetime = false and use bounded date range queries directly in the SQL Statement field with both a lower and upper bound. For example:

SELECT * FROM schema.table WHERE insert_date >= '2000-01-01 00:00:00' AND insert_date

Manually adjust the date window in the SQL Statement for each run, progressively moving through the historical data in manageable chunks until the full table has been loaded. This approach avoids timeouts by controlling the exact volume of data retrieved per execution and prevents duplicate data loading by using non-overlapping date windows.

Phase 2 — Ongoing Delta Imports (Use Last Run Datetime = true):

Once all historical data has been loaded:

Switch the Data Source to use Use Last Run Datetime = true. Set the SQL Statement back to a simple SELECT without LIMIT, ORDER BY, or WHERE clauses. Set the Last Run Datetime field to the upper bound of the last historical chunk from Phase 1. From this point forward, each scheduled run will automatically fetch only rows newer than the last successful import timestamp, keeping the data in sync incrementally.

Additional Notes:

SQL-Level LIMIT/OFFSET: If Use Last Run Datetime is not enabled, you can use a custom SQL Statement with an explicit LIMIT clause to restrict the number of rows retrieved per execution. Do not combine SQL-level LIMIT with Use Last Run Datetime = true, as this produces invalid SQL (see Cause section above).

Pagination Fields: The Support Pagination, Limit, and Offset fields on the Data Source form are applicable only to REST (Integration Hub) type data sources. Enabling these fields on a JDBC Data Source will have no effect on the query or data retrieval. Refer to the JDBC type data source documentation for the list of fields supported for JDBC data sources.

Related Links .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } Create a JDBC type data source (Zurich): https://www.servicenow.com/docs/r/zurich/integrate-applications/system-import-sets/create-jdbc-type-data-source-d92117e59989.html Data source fields (Zurich) — REST (Integration Hub) type: https://www.servicenow.com/docs/r/zurich/integrate-applications/system-import-sets/r_DataSourceFileTypeFields-d92117e60211.html Use Last Run Datetime for a JDBC type data source (Community): https://www.servicenow.com/community/developer-articles/use-last-run-datetime-for-a-jdbc-type-data-source/ta-p/2313070 KB0778973 — Unable to decrypt parameter via MID Server: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0778973 KB0856090 — SQL command not properly ended when using ORDER BY and Use Last Run Datetime: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0856090