Whats the plan name for my routine ?

Ever tried to find the package / plan name for your stored procedure or function in DB2 LUW. Its not straight foward was I wish it was .  Here is an sql that will make your life a little easy :

select  deps.bschema SCHEMA,
        procs.routinename PROCEDURE,
        deps.bname PACKAGE,
        plan.last_bind_time BIND_TIME,
        procs.alteredts ALTER_TIME,
        procs.definer definer,
        procs.valid VALID
  from  sysibm.sysdependencies deps,
        sysibm.sysroutines procs,
        sysibm.sysplan plan
 where  deps.dtype = ‘F’
   and  deps.btype = ‘K’
   and  procs.specificname  = deps.dname
   and  procs.routineschema = deps.dschema
   and deps.bname = plan.name
 order  by 1,2,3;

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in db2 and tagged , , , , . Bookmark the permalink.