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,
Post an idea
Upvote ideas that matter most to you
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
Great idea!!!
Very useful quick performance fix possible
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;
Good initiative and I'm already looking at an opportunity of utilizing this idea for our account, good one.
Much needed one on DB2 environment. Very good idea and initiative. Kudos!!
Good one. way to go.
Very good , Thank you
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.
Good initiative appreciated.
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.
Excellent DB2 Performance enhancing uility .. Great Work Raghav
Would be useful tool for analyzing performance issue.
This is very good idea. This will safe a lot of time for DBA.
Great idea Buddy. It will really help the DBAs a lot.
It would be a handy tool to have
Excellent initiative.. We can try to integrate with BF. Very useful idea :)
Very Good initiative, It will reduce more pain in finding the performance issues cost wise to fix the queries.. which will save more time.
Well done, good initiative
Very interesting approach !
Good useful tool