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 Needs more information
Workspace Db2 for z/OS
Created by Guest
Created on May 31, 2017

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.
  • Guest
    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
    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
    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
    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.