IBM Data & AI

 Welcome to the IBM Data & AI Ideas Portal for Clients! 

We welcome and appreciate your feedback on IBM Data & AI Products to help make them even better than they are today!
Before you submit an idea, please perform a search first as a similar idea may have already been reported in the portal.  If a related idea is not yet listed, please create a new idea and include with it a description which includes expected behavior as well as why having this feature would improve the service and how it would address your use case.
IBM Employees:
Clients:
  • Our team welcomes any feedback  and suggestions you have for improving our offerings / products!  This forum allows us to connect your offering / product improvement ideas with IBM product and engineering teams.
  • If you have not registered on this portal please click on the following link and register.  To complete registration you will need to open the email you will receive from Aha to confirm your identity. http://ibm.biz/IBM-Data-and-AI-Portal-Register
Additional Information:
  • The shorter URL for this site is: https://ibm.biz/IBM-Data-and-AI-Ideas
  • 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
  • Please do not use the Ideas Portal for reporting bugs - we ask that you report bugs or issues with the product by contacting IBM support.

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
  • Will not implement
Why is it useful?
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
Customer Name Michelin
Submitting Organization
Submitter Tags
  • Attach files
  • Marcelo Ripoll commented
    August 29, 2019 15:37

    very good initIative

  • Senthil T commented
    August 30, 2019 14:24

    Great Initiative.

  • Jonel Sanchez commented
    August 31, 2019 05:19

    If this gets implemented, I'd be among to first use  it.   trial and error on optimizing DB environments will be reduced with a precision specific diagnostic.  Its about time we have
    this functionality

  • Neena Gupta commented
    September 01, 2019 06:21

    Thank you for this innovative and useful idea

  • Jyothilakshmi S commented
    September 01, 2019 22:53

    Good one..All the best

  • Ravi K Duvvuri commented
    September 02, 2019 07:30

    A real Good Idea which can cater to other DB technologies as well.  Truly Innovative.

  • Rathis Ramanathan commented
    September 02, 2019 08:12

    Good Idea !!!

  • Leszek Choniawko commented
    September 02, 2019 09:47

    Good idea to have it on place within built-in tools rather than using self-created scripts which cover repeatable tasks used by DBA's.

  • Vikas V commented
    September 02, 2019 10:10

    Major breakthrough for the DB2 performance . 

    Excellent analysis!

     

  • Marcin Smetek commented
    September 02, 2019 10:59

    Good useful tool 

  • jacques protat commented
    September 02, 2019 14:20

    Very interesting approach !

  • Guest commented
    September 02, 2019 17:23

    Well done, good initiative

     

  • veeresh Babu commented
    September 03, 2019 08:55

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

  • Srinivasan govindaraj commented
    September 03, 2019 09:31

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

  • Vasanth Prabhakar commented
    September 03, 2019 10:28

    It would be a handy tool to have

  • Arijit Biswas commented
    September 03, 2019 11:34

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

  • Hamid Qurbani commented
    September 03, 2019 13:33

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

  • Pramod S commented
    September 03, 2019 14:02

    Would be useful tool for analyzing performance issue.

  • Nasreen Dawood commented
    September 03, 2019 18:24

    Excellent DB2 Performance enhancing uility .. Great Work Raghav

  • Robert Indrigo commented
    September 03, 2019 18:47

    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.

  • vara v commented
    September 04, 2019 01:09

    Good initiative appreciated. 

  • Paul Bird commented
    September 04, 2019 02:18

    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.

  • Dharmendrakumar Patel commented
    September 04, 2019 02:32

    Very good , Thank you 

  • Chanrdran Janakiraman commented
    September 04, 2019 05:42

    Good one. way to go.

  • Vadivel Sivamani commented
    September 04, 2019 05:48

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

  • Sanju Punjabi commented
    September 04, 2019 11:24

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

  • Robert Indrigo commented
    September 04, 2019 12:08

    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;

  • Ravi Narayanaswamy commented
    September 04, 2019 12:10

    Very useful quick performance fix possible

  • Manoj Kumar commented
    September 05, 2019 15:59

    Great idea!!!

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