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 (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

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 (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.

IBM Employees should enter Ideas at https://ideas.ibm.com


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
  • Admin
    Janet Figone
    Reply
    |
    Jul 23, 2024

    Hello Norbert, I am checking with development and will post an update when I receive their feedback.

    Sincerely,

    The Db2 z team

  • Guest
    Reply
    |
    Jul 23, 2024

    Hello,



    I regularly, at least annually, check the status of my ideas. There are more than 20 votes for this idea. So it seems to be relevant for many customers. Even under V13 there is probably no solution to this. What is IBM planning to improve the problem described?

  • Admin
    Janet Figone
    Reply
    |
    Feb 1, 2023

    Hello Norbert, Thank you for your inquiries, and our apologies for the late response.

    Db2 for z/OS development evaluated all of the enhancement requests affecting this area of the Db2 for z/OS product, to set prioritization. Unfortunately, they do not have anything in plan for the near future to improve this particular aspect of dynamic plan stability, although, they do acknowledge this is an area for improvement that we will revisit in the future.

    Sincerely,

    The Db2 for z/OS Team

  • Guest
    Reply
    |
    Sep 5, 2022

    Hello,


    are there any changes / plannings in V13 that will improve the feature dynamic plan stability in general or that specifically improve the situation with SELECT * SQL Statements?


    kind regards


    Norbert Wolf

    Datev eG


  • Guest
    Reply
    |
    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
    Reply
    |
    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
    Reply
    |
    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
    Reply
    |
    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
    Reply
    |
    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
    Reply
    |
    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
    Reply
    |
    May 14, 2021

    Hello,


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


    kind regards


    Norbert Wolf


    Datev eG

  • Guest
    Reply
    |
    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
    Reply
    |
    Feb 17, 2021

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

  • Guest
    Reply
    |
    Feb 15, 2021

    Hello,


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

  • Guest
    Reply
    |
    Dec 29, 2020

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