IBM Data and AI

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

We welcome and appreciate your feedback on IBM Data and 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

  • Avatar32.5fb70cce7410889e661286fd7f1897de Guest
  • Aug 29 2019
  • Will not implement
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
  • Attach files
  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    5 Sep, 2019 03:59pm

    Great idea!!!

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    4 Sep, 2019 12:10pm

    Very useful quick performance fix possible

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    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;

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    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.

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    4 Sep, 2019 05:48am

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

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    4 Sep, 2019 05:42am

    Good one. way to go.

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    4 Sep, 2019 02:32am

    Very good , Thank you 

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    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.

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    4 Sep, 2019 01:09am

    Good initiative appreciated. 

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    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.

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    3 Sep, 2019 06:24pm

    Excellent DB2 Performance enhancing uility .. Great Work Raghav

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    3 Sep, 2019 02:02pm

    Would be useful tool for analyzing performance issue.

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    3 Sep, 2019 01:33pm

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

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    3 Sep, 2019 11:34am

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

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    3 Sep, 2019 10:28am

    It would be a handy tool to have

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    3 Sep, 2019 09:31am

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

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    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.

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    2 Sep, 2019 05:23pm

    Well done, good initiative

     

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    2 Sep, 2019 02:20pm

    Very interesting approach !

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    2 Sep, 2019 10:59am

    Good useful tool 

  • Load older comments

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