Cache ODBC SQL Resultset Metadata Before Flow Starts

We design and deliver a large number of IIB solutions for our customers. Most of our major customers are developing mission-critical applications that involve access to one or more databases via ODBC.

When using ACEv11 (and IIBv10) with ODBC in a flow, ACEv11 performs the following activities on the first request:

1) connects to the pre-defined data source(s); and

2) retrieves and cache the metadata/data types of the resultset for the SQL queries.

Depending on the number of datasources and SQL queries in the flow, the latency for the first request after an Integration server restart can be very large.
We have experienced latency up to 20 seconds. It has resulted in multiple situations where the first request always fails the response time SLA defined in the sub-1-second range.

The latency is made up of 2 steps,

1) the initial connection to the database - per datasource;

2) the query, retrieval and caching of the metadata/data types of the resultset for the SQL queries.

The second step usually makes up the bulk of the delay.


Optimisation Supported by IIB/ACE (today)
--------------------------
- "Connect before flow starts" feature to address the initial connection latency using a persistent ODBC connection per datasource.


Outstanding Issue (present in all IIB and ACE runtimes)
-------------------------
- Unable to "Cache SQL resultset(s) metadata before flow starts" to address the bulk of the latency experienced with using ODBC.


Current workaround
----------------------------
All affected flows to include a separate branch to "touch/prime" all the stored procedures. In a multi-instance scenario, all threads must be "blocked" for the cache to complete before the first request. The timer node is utilised to auto-"prime" the flows on IIB/ACE restart.


Proposed Enhancement
----------------------
1) include an option to cache the SQL query Resultset(s) by introducing a new checkbox below the "Connect before the flow starts" checkbox.

2) When the option is selected, the IIB/ACE runtime performs the query and caching, by scanning the ESQLs - ie Compute and Database nodes with SQL queries, after a restart.

Note: SQL queries include stored procedure calls.