Integration


This is an IBM Automation portal for Integration products. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:

Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas

  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.


Add a new idea Subscribe
Status Not under consideration
Workspace App Connect
Created by Guest
Created on Jun 27, 2019

RELATED IDEAS

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.

Idea priority High
RFE ID 134190
RFE URL
RFE Product IBM App Connect Enterprise (formerly IBM Integration Bus)
  • Admin
    Ben Thompson
    Reply
    |     Mar 11, 2023

    Idea Review. As part of our policy for regularly reassessing aged ideas we have recently discussed this enhancement request again. Unfortunately on this occasion with the low interest from the community it has been decided that we won't be taking this idea further forward as a business priority. Having said this, we appreciate the use case and the degree of awkwardness of the current Timeout node based workaround, and we note that this could be solved more easily in the user's domain if we were to implement the suggestion in https://integration-development.ideas.ibm.com/ideas/APPC-I-689 which we intend to keep open and continue to monitor for interest, as this has wider applicability to a large number of potential use cases.

  • Guest
    Reply
    |     Oct 20, 2020

    RFE Review. Thank you for taking the time to raise this RFE and apologies for the length of time it has been in Submitted status. In particular thank you for the very clear way the request has been laid out. We very much agree that this enhancement suggestion would be for the good of the product. Status of the RFE is updated to Uncommitted Candidate.

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.