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 May 31, 2017

RELATED IDEAS

Improve performance of EXPLAIN

With early versions of DB2 the performance of EXPLAIN was not relevant. Nowadays, where DBAs do thousands of EXPLAINs, the performance and turnaround is getting critical. I think there is room for better performance and throughput of mass EXPLAINs. Especially when all EXPLAIN tables exist or, even worse, the virtual index tables. This is becoming a real bottleneck in regression testing.
  • Admin
    Janet Figone
    Reply
    |     Oct 27, 2021

    Dear Roy,

    Thank you for submitting this enhancement request. While we view all the enhancement requests we receive as valuable and we would like to implement all of them, in an effort to have maximum transparency we are closing the ones that don’t currently appear on our two year product roadmap. This is not to say that this enhancement may not still be implemented at some point in the future as we do evaluate our requirements on the basis of customer demand and technical impact, to deliver maximum value to our customers.

    We appreciate your input to the Db2 for z/OS development team. And we hope that you will continue to submit enhancement suggestions for improvements as customer feedback is a key component to shaping the future direction of Db2 for z/OS.


    Sincerely,

    Db2 for z/OS Team

  • Guest
    Reply
    |     Sep 17, 2021

    Hi!

    Naturally I am not using EXPLAIN STATEMENT as that does not do a "proper" explain! What I do is:

    • Snap the entire DSC using IFCID 316 and IFCID 317.

    • For every SQL in the DSC build an SQL statement and execute a dynamic EXPLAIN thus populating all relevant EXPLAIN tables.

    Doing this with all the EXPLAIN tables present, and 263,000 statements, does take a while but, as I mentioned, I am not sure if it is the INSERTs or the CPU of the EXPLAIN that is the "overhead". I discussed this a few years ago with Terry Purcell and he mentioned that there could be a small performance gain but he was unsure about how much...

    Cheers

    Roy Boxwell

  • Admin
    Jane Chan
    Reply
    |     Sep 17, 2021

    Hi Roy,

    The scenario you described about inserting into 17 tables for all (263,000) statements is not possible in one unit of operation. So our SME really doesn't see this big lengthy operation happening in today's Db2. There is a hidden zparm that by default Db2 only populates 4 EXPLAIN tables for EXPLAIN STATEMENT. User may change the zparm to obtain output from more than those 4 tables, maybe for diagnosing some specific queries, but not for all. Secondly, EXPLAIN STATEMENT can only be specified with a statement ID. Our SME has not seen user EXPLAIN all statements from Statement Dyanmic Cache in one operation. Do you have an example command/instruction?

    Thank you.

  • Guest
    Reply
    |     Sep 14, 2021

    Hi, It is the INSERT in all 17 of the explain tables, if you explain 263,000 SQLs (This is a "normal" number of Dynamic SQLs extracted from one of the Prod datasharing systems) it takes a while to do the EXPLAIN. Whether this is a problem of the physical INSERT or the cpu performance of the Explain I do not know...

    Roy

  • Admin
    Janet Figone
    Reply
    |     Sep 13, 2021

    Hello Roy, Our Db2 for z/OS SME reviewing this requirement indicated the requirement did not specify what the specific pain points are, whether it is write, read or delete EXPLAIN tables. Write is embeded in query bindtime process, while read and delete are stand alone processes. Can you please provide more information so the review can continue?

    Thank you.

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.