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


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:

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

Help IBM prioritize your ideas and requests

The IBM team may need your help to refine the ideas so they may ask for more information or feedback. The product management team will then decide if they can begin working on your idea. If they can start during the next development cycle, they will put the idea on the priority list. Each team at IBM works on a different schedule, where some ideas can be implemented right away, others may be placed on a different schedule.

Receive notification on the decision

Some ideas can be implemented at IBM, while others may not fit within the development plans for the product. In either case, the team will let you know as soon as possible. In some cases, we may be able to find alternatives for ideas which cannot be implemented in a reasonable time.

Additional Information

To view our roadmaps: http://ibm.biz/Data-and-AI-Roadmaps

Reminder: This is not the place to submit defects or support needs, please use normal support channel for these cases

IBM Employees:

The correct URL for entering your ideas is: https://hybridcloudunit-internal.ideas.aha.io


Status Not under consideration
Workspace Db2 AI for z/OS
Created by Guest
Created on Dec 11, 2020

Make Dynamic Plan Stability really stable

We use dynamic sql now in nearly all of our applications. It is essential for our shop that the access paths for dynamic sql are as stable as possible. So we will use the new V12 feature "dynamic plan stability". If you look into the manuals and if you make your own tests you will find one big difference beween the mechanism dynamic plan stability and the plan management feature for static sql. If you do a simple alter table add column it has no "negative stability effect" for all dependent packages with static sql. You don't need a REBIND ! Unfortunately stabilized dynamic sql will be invalidated by a simple alter table add column. That is a big difference and we would expect the same behaviour for stabilized dynamic sql as it is for static sql. Please keep stabilized dynamic sql stable in this situation!

Needed by Date Mar 31, 2021
  • Guest
    Apr 22, 2022

    Hello,

    I can agree to all descriptions regarding 'SELECT *' SQL-Statements. But all other dynamic SQL statements should stay stabilized in the situation of an ALTER TABLE ADD COLUMN. Is there anything done for this situation in V13?


    kind regards


    Norbert Wolf

    Datev eG



  • Admin
    Janet Figone
    Aug 24, 2021

    Hello Norbert,

    Thank you for submitting this Aha Idea. We absolutely understand the desire for dynamic SQL stability to achieve similar stability of access paths that exist with static packages.

    For ALTER TABLE ADD COLUMN, you are correct that in most cases we do not invalidate dependent static packages, but we do invalidate dynamic SQL statements, including those that are stabilized. The reason for this discrepancy is due to the semantics of how a SELECT * statement behaves.

    Consider a static package that contains a SELECT * statement. The application has been coded to expect the existing number of columns returned from the table. These columns are "hardened" at BIND/REBIND time. When a new column is added to the table, we do not invalidate the package, and the package can still execute successfully, but the SELECT * statement would only return the old columns and not the new column. We only mark SYSPACKAGE.VALID = 'A' for the package to indicate that an alteration was done, and if it's desired for the static package to "pick up" the alteration, a REBIND should be done. Most likely, application code changes would also be needed to accommodate the SELECT * returning more columns than before. Once the REBIND of the package is done, the SELECT * statement would now return all columns, including the newly added one.

    For dynamic SQL, our perspective is that a SELECT * should always return the current number of columns in the table. If we do not invalidate dependent dynamic SQL, then the next time the dynamic SQL is executed, the output of the SELECT * statement would change depending on whether the SELECT * was in the cache or stabilized, etc.:

    SELECT * is in cache

    SELECT * is stabilized

    SELECT * output

    Notes

    No

    No

    all columns

    SELECT * has to go through prepare, which will see all columns currently in the table, including the newly added one.

    Yes

    No

    old columns

    SELECT * has already been prepared and is in the cache, so we use what's in the cache to execute, and it is only aware of the old columns.

    Yes

    Yes

    old columns

    SELECT * has already been prepared and is in the cache, so we use what's in the cache to execute, and it is only aware of the old columns.

    No

    Yes

    old columns

    SELECT * is not in the cache, so the stabilized statement is loaded for execution, and it is only aware of the old columns.



    We think that most end users of dynamic SQL would likely not be aware of whether the statement being executed is cached or stabilized to know which SELECT * behavior to expect. Hence, while we very much want to preserve access path stability, we cannot do so at the expense of incorrect output.

    Ideally, we would want to invalidate only those dynamic SQL that are SELECT *. Unfortunately, there is currently no information stored either in the cache or in the catalog to distinguish between SELECT * and other flavors of SQL statements. This is the reason why we need to invalidate all dependent dynamic SQL.

    Hopefully, that clarifies the reason for the current Db2 invalidation behavior.

    We'd appreciate any feedback or additional insight you might have! Thank you!

  • Admin
    Janet Figone
    Jul 28, 2021

    Hello Norbert, We have placed this idea in Future Consideration. Our New App functional area team will be meeting to discuss this idea.

  • Admin
    Janet Figone
    Jul 19, 2021

    Hello Norbert, I have reminded our engineer that we need to get a response to you. I hope to learn something from the team soon to share with you.


    Thank you for your patience.

    Janet

  • Guest
    Jul 14, 2021

    Hello,


    was there any answer from the "new technical lead" in this area?


    kind regards


    Norbert Wolf


    Datev eG

  • Admin
    Janet Figone
    May 20, 2021

    Hello Norbert, This Aha! Idea will be transferred to another Db2 for z/OS engineer shortly, and I will ask them to review it and provide an update to you.

  • Guest
    May 14, 2021

    Hello,


    was there any answer from the technical lead in this area?


    kind regards


    Norbert Wolf


    Datev eG

  • Guest
    May 14, 2021

    Hello,


    are there any news for the current status of that idea? My management is asking for a current status.


    kind regards


    Norbert Wolf


    Datev eG

  • Admin
    Janet Figone
    Feb 17, 2021

    Hello, I've asked the technical lead in this area for an update.

  • Guest
    Feb 15, 2021

    Hello,


    are there any news for the current status of that idea?

  • Guest
    Dec 29, 2020

    Thank you very much for the fast processing of the idea!