db2

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;