Skip to Main Content
IBM Data and AI Ideas Portal for Customers

This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. 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 (

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

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea

Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal ( - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal ( - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM. - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Employees should enter Ideas at

118 VOTE
Status Future consideration
Workspace Db2 for z/OS
Created by Guest
Created on Jun 4, 2021

Resolve a restriction that CACHE DYN STABILITY function can not use with CONCENTRATE WITH LITERALS option.

Customer environment

Db2 V12 <- DRDA -> Db2connect/JDBC(IBM Data Server Driver for JDBC and SQLJ type 2)

Server side.(JDBC Type2 is used)

Windows server:

- WebSphere BASE 9.0.0 (Windows2016)

- Db2client / IBM Data Server Runtime Client V11.1 Mod2 FixPack2

- JDBC 4.0 driver / IBM Data Server Driver for JDBC and SQLJ 4.23.42

- DB2 Connect Unlimited Edition for System z V11.1 Mod 2 Fix Pack 2

WAS------------------------- --------------------- ----------- --------

| Db2client-JDBC driver|-|DB2 CONNECT | --/-- |DB2 s/OS|---|IDAA|

------------------------------ --------------------- ----------- --------

  Requester Server

Customer is very sensitive about accesspath changes and want to implement V12's new function:Dynamic SQL plan stability.

Their JDBC driver use the CONCENTRATE WITH LITERALS to improve a performance since they upgraded Db2 to v9.

The definition is as follows;

Common IBM Data Server Driver for JDBC and SQLJ properties for Db2 servers



The IBM Data Server Driver for JDBC and SQLJ uses the data source's statement concentrator functionality.

On the other hand,during a study of implementation of Dynamic SQL plan stability,we found there are some restriction .

Db2 V12's command manual has the following explanation.


The Db2 command START DYNQUERYCAPTURE stabilizes access paths for qualified cached dynamic queries. This command can also optionally start monitoring of cached dynamic queries that qualify for a scope but have not met the specified execution threshold for stabilization.

All statements in the dynamic statement cache are qualified for capture, with the following exceptions:

• Queries that were prepared with the REOPT(AUTO) bind option

• Queries that were prepared with the CONCENTRATE STATEMENT WITH LITERALS bind option

• Queries that were transformed because they reference system temporal, application temporal, or archived transparency tables and one or more of the following settings uses a non-default value:



– GET_ARCHIVE global variable

Abbreviation: -STA DYNQUERY

Since customer's JDBC driver will not change this CONCENTRATOR_WITH_LITERALS(2) option,we can not use this Db2 V12's new function:DYNQUERYCAPTURE at moment..

We want to resolve this restriction as soon as possible because of the following reason.

1)We have a performance problem of dynamic SQL after migrating Db2 V12 FL503(&z15),because Db2 V12 choose different(bad) accesspath compare with V11's one(Its investigation is still continued in TS005587226).

2)As a workaround of 1), we were advised to use OPTHINT, then it appeared OPTHINT is unable to work well along with CONCENTRATE WITH LITERALS option so that APAR:PH37458 has been opened to fix this issue.(TS005696694).

But even if PH37458 will be available soon,to implement OPTHINT,we have to change an application SQL coding(add QUERYNO),and server side setting also need to change(allow the "SET CURRENT OPTIMIZATION" option).

Customer does not want these user-program related changes.

If we can use a stabilized dynamic SQL statements function,it is a best workaround for both 1) and 2). We really want to stabilize specific access path for a specific SQL under Db2 v12.

Needed by Date Dec 25, 2021
  • Guest
    May 9, 2023

    Hello Janet, it is ok to change this request to public.

    Customer is moving to Db2 13 on this year end. We hope customer's request will come true soon.

  • Guest
    Apr 20, 2023

    That's ok for me to make this Aha idea to be public.

  • Admin
    Janet Figone
    Apr 20, 2023

    Dear Tsuchiya-san,

    One of our Db2 for z/OS engineers would like to know if we may make this Aha! Idea public, such that external uses (outside of IBM) are able to vote for it. Is it ok with you if we change the visibility of this idea to public?

    Thank you for your kind consideration.

    Janet - Db2 for z/OS Aha! Administrator