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


Add a new idea Subscribe
Status Not under consideration
Workspace Db2 for z/OS
Created by Guest
Created on Nov 23, 2022

RELATED IDEAS

Please do not make stabilizing dynamic query again after a REBIND with APPLCOMPAT a normal behaviour

Hello,

We're DB2z 12 FL508. This is a SAP/R3 application on 2-way data sharing.

We stablized access path of some dynamic SQLs and they worked very well. Note that the packages were at APPLCOMPAT V12R1M500 when we did this.

Lately, the packages were rebound with APPLCOMPAT V12R1M508. We noticed that DB2 stopped using the stabilized access paths thus causing many timeouts on transactions executing those SQLs. We were advised by IBM support that this is a normal behaviour:

"To answer the customer's question about why we don't match when APPLCOMPAT changes, it is normal behavior. We do this because something can change with a different APPLCOMPAT level. The customer should use APPLCOMPAT to ensure there is no behavior change which can cause application problems.

Advancing function level does not affect cache match. It's really about what the package is bound with. If APPLCOMPAT is not specified on the BIND, Db2 for z/OS will pick it up from the zparm. As long as they do not REBIND with a new APPLCOMPAT or change their zparm, then there should be no problem.

So, when a dynamic SQL statement is cached/stabilized, a specific behavior is locked in. Changing APPLCOMPAT can change that behavior, so requires a new cached/stabilized entry."

Why it is considered a normal behaviour for DB2 "not to match" when a rebind to a different applcompat takes place? In my opinion, it makes sense for static but not dynamic SQLs. For us, I see this behaviour a problem. As I mentioned, because DB2 did not use the stablilized access path, we had many timeouts and a severe slowdown of the application then the LPAR.

Is there any plan to introduce a bind parameter or a ZPARM to give us the flexibility to choose whether DB2 should match or not match?


Regards

Needed By Not sure -- Just thought it was cool
  • Admin
    Janet Figone
    Reply
    |     Jan 11, 2023

    Dear Mai, Thank you for submitting this enhancement request. Our Db2 for z/OS development team reviewed it and determined it does not align with our product structure. They also provided this feedback for you:

    Since Db2 11, the APPLCOMPAT value is used as a dynamic cached statement matching criteria. This is because Db2 11 introduced the schema SYSIBMADM as part of the default SCHEMA PATH special register which is used to search for user defined functions referenced in a dynamic query. Db2 needs to ensure the same behavior is observed until the application can accept a new behavior change, so APPLCOMPAT is the way to specify that. For example SELECT MYUDF(C1) FROM T1 could choose USER.MYUDF with APPLCOMPAT(V10R1) and SYSIBMADM.MYUDF with APPLCOMPAT(V11R1). The resolved and chosen schema is saved in the cached and stabilized query so when you changed APPLCOMPAT, the saved UDF may not be the right one to execute so we need to go thru full PREPARE.

    APPLCOMPAT continues to be the way to control SQL or application behavior changes and protect the customers from exposure to the unwanted changes until the applications are ready to handle them. As for how to avoid the timeout on full PREPARE, perhaps you can expose the queries gradually after REBIND with changed APPLCOMPAT.

    Consequently, this idea is marked as Not under consideration.

    We do hope that you will continue to submit enhancement requests for our evaluation and consideration to include in the Db2 for z/OS product.

    Sincerely,

    The Db2 for z/OS Team

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.