IBM Data and AI Ideas Portal for Clients

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

If you have not registered on this portal please register at http://ibm.biz/IBM-Data-and-AI-Portal-Register . To complete registration you will need to open the email you will receive from Aha to confirm your identity.

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 offering manager 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

Getting the Accessplans at the Routine level for the Database LUW

Db2 performance issues in one of the major area to look for, Whenever there is performance issue in the database, one of the major point we look is for the queries how they are behaving in the database.

Db2 LUW provides lot of options to get the access plans at the query level like db2expln,db2exfmt,dsmtop,db2pd etc.

Say if a customer is saying he is facing slowness in the system then we will drill down to the queries that are using CPU,I/O, scans it does etc.

Bunch of SQL's will be embedded in the routine and they get precompiled in the package cache.

My point here is upto now there is no utility in db2 LUW which does the task of getting the accessplans for the queries inside the routine.

Example: If a routine say Fare_calculation() has around 300 SQL's inside it, then getting cost for each sql is time consuming process,

So it is good idea to have a utility like say db2routineplan which gets the accessplans at the routine level

for all the routines inside the database and also at the individual routine level.

 

Benefits of the idea

===============

1) It will save the time of the dba or developer who is looking at the cost of the queries at the routinelevel

2) it will help in diagnosing the routine performance and slowness of it

3) It helps in the performance area of the individual sqls.

 

Current work around

================

 

1) Get all the packages list at the routine leve for the database

2) Store the package names and stored procedure in  a file

3) In a loop operate db2expln for those packages which inturn gets the accessplans at the routine level

  • Guest
  • Aug 29 2019
  • Not Under Consideration
Who would benefit from this IDEA? Db2 Developers, DBA and even the cusomer would benefit a lot in saving time to get the performance issue at the database level
How should it work?

1) How the db2 utility should work is at both the database level and routine level

Example : db2routineplan -d dbname (To get acessplans for all the routines at the database level)

                       db2routineplan -d dbname -r routinename(To get accessplans at the routinelevel)

 

2) The Work around method that is used as of now is

 

1) Get all the packages list at the routine leve for the database

2) Store the package names and stored procedure in  a file

3) In a loop operate db2expln for those packages which inturn gets the accessplans at the routine level

 

   

Idea Priority Medium
Priority Justification We can use the turn around approach as of now
Client Name Michelin
  • Guest commented
    5 Sep, 2019 03:59pm

    Great idea!!!

  • Guest commented
    4 Sep, 2019 12:10pm

    Very useful quick performance fix possible

  • Guest commented
    4 Sep, 2019 12:08pm

    It's quite easy to explain all routines, or a subset, I still do not understand why you would want to do that, but here's a simple Db2 script that will explain all routines that match a pattern:

    connect to mydb;

    create or replace procedure explainRoutine( schema varchar(128), name varchar(128))

    language sql

    begin atomic

    DECLARE explain_schema VARCHAR(128) default NULL; --

    DECLARE requestor VARCHAR(128); --

    DECLARE explain_time timestamp; --

    DECLARE source_name VARCHAR(128); --

    DECLARE source_schema VARCHAR(128); --

    DECLARE source_version VARCHAR(64); --

    DECLARE sectno int; --

    DECLARE continue handler for sqlstate value '55075'

    BEGIN

    end; --

    for v as cur1 cursor for

    select R.ROUTINESCHEMA, R.ROUTINENAME, R.SPECIFICNAME, P.PKGSCHEMA, P.PKGNAME, P.TOTAL_SECT from SYSCAT.ROUTINEDEP D, SYSCAT.ROUTINES R, SYSCAT.PACKAGES P

    where rtrim(R.routineschema) like schema

    and R.routinename like name

    and D.BTYPE='K'

    and D.routinename=R.specificname

    and D.BSCHEMA=P.PKGSCHEMA

    and D.BNAME=P.PKGNAME

    and R.ROUTINESCHEMA not like 'SYS%'

    do

    set sectno=1; --

    call dbms_output.put_line( '"' || V.ROUTINESCHEMA || '"."' || V.ROUTINENAME || '" ("' || V.SPECIFICNAME || '"): "' || V.PKGNAME || '" sections: ' || V.TOTAL_SECT); --

    while (sectno <= v.total_sect) do

    call EXPLAIN_FROM_CATALOG(V.PKGSCHEMA, V.PKGNAME, '', sectno, explain_schema, requestor, explain_time, source_name, source_schema, source_version); --

    set sectno=sectno + 1; --

    end while; --

    end for; --

    end;

    call sysproc.sysinstallobjects('EXPLAIN','C',NULL,CURRENT USER);

    delete from explain_instance;

    set serveroutput on;

    call explainRoutine('%','%');

    select count(*) from explain_instance;

    connect reset;

    !db2exfmt -d mydb -1 -w % -g -o exp.out;

  • Guest commented
    4 Sep, 2019 11:24am

    Good initiative and I'm already looking at an opportunity of utilizing this idea for our account, good one.

  • Guest commented
    4 Sep, 2019 05:48am

    Much needed one on DB2 environment.  Very good idea and initiative.  Kudos!!

  • Guest commented
    4 Sep, 2019 05:42am

    Good one. way to go.

  • Guest commented
    4 Sep, 2019 02:32am

    Very good , Thank you 

  • Guest commented
    4 Sep, 2019 02:18am

    we have already tried to address this scenario (as I understand it) in a number of ways. For example, we have introduced MON_GET_ROUTINE to help identify which routines are expensive, MON_GET_ROUTINE_EXEC_LIST to find out which statements in those routines are expensive, and once you know the statement executable ID, you can use the appropriate EXPLAIN_FROM_* routine to extract the access plan from the section of the statement (as outlined in the technote mentioned by Robert).

    It is not clear to me what the shortcomings is of the provided solution and why a (very) different solution is being asked for. The only scenario I can imagine is if you chose to get the explain data for all the SQL rather than just the one of interest (which seems inefficient).

    If you have specific scenarios where there are gaps in the current capabilities, that would help us better understand why the requested approach is desired.

    Thanks.

  • Guest commented
    4 Sep, 2019 01:09am

    Good initiative appreciated. 

  • Guest commented
    3 Sep, 2019 06:47pm

    It doesn't makes sense to get an access plan for a routine. An access plan is for a query. A routine is either inlined, in which case it does not have its own access plan, it is incorporated into the invoking query. If the routine is not inlined, it will have one or more queries embedded in it. And we already have tools to explain those embedded queries. For example. for a compiled SQL routine, you can use SET_ROUTINE_OPTS to enable explain when you create the routine. Alternatively, you can use EXPLAIN_FROM_CATALOG to explain the queries embedded within a routine.

    Please see this tech note:
    https://www-01.ibm.com/support/docview.wss?uid=swg21279292

    Explaining all the queries in all the routines in the database would not be a good idea, it would probably be too huge to be useful.

  • Guest commented
    3 Sep, 2019 06:24pm

    Excellent DB2 Performance enhancing uility .. Great Work Raghav

  • Guest commented
    3 Sep, 2019 02:02pm

    Would be useful tool for analyzing performance issue.

  • Guest commented
    3 Sep, 2019 01:33pm

    This is very good idea. This will safe a lot of time for DBA.

  • Guest commented
    3 Sep, 2019 11:34am

    Great idea Buddy. It will really help the DBAs a lot.

  • Guest commented
    3 Sep, 2019 10:28am

    It would be a handy tool to have

  • Guest commented
    3 Sep, 2019 09:31am

    Excellent initiative.. We can try to integrate with BF. Very useful idea :)

  • Guest commented
    3 Sep, 2019 08:55am

    Very Good initiative, It will reduce more pain in finding  the performance issues cost wise to fix the queries.. which will save more time.

  • Guest commented
    2 Sep, 2019 05:23pm

    Well done, good initiative

     

  • Guest commented
    2 Sep, 2019 02:20pm

    Very interesting approach !

  • Guest commented
    2 Sep, 2019 10:59am

    Good useful tool 

  • Load older comments

IBM's success depends on gathering feedback from customers like yourself. Aha Ideas Portal is the third party tool through which IBM Offering Managers gather feedback from customers such as yourself.
IBM is a global organization with business processes, management structures, technical systems and service provider networks that cross borders. As such, the information collected through Aha Ideas Portal (Customer Name, Customer Email Address) will be stored by them in the United States, and handled only as per IBM's instructions and policies. Your data (Name and Email Address) will NOT be shared with other IBM customers.
In order to safeguard your information in Aha, do not leave your workstation unattended while using this application, log off after using it, and print only if necessary. If you need to make a hardcopy, remember to pick up the print-out immediately, keep it under lock, and destroy it immediately when no longer needed.
NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "anonymous@euprivacy.out" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions