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,
Post an idea
Upvote ideas that matter most to you
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
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
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
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
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!
Hello Norbert, We have placed this idea in Future Consideration. Our New App functional area team will be meeting to discuss this idea.
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
Hello,
was there any answer from the "new technical lead" in this area?
kind regards
Norbert Wolf
Datev eG
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.
Hello,
was there any answer from the technical lead in this area?
kind regards
Norbert Wolf
Datev eG
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
Hello, I've asked the technical lead in this area for an update.
Hello,
are there any news for the current status of that idea?
Thank you very much for the fast processing of the idea!