How to map stored procedure and user defined function (UDF) names to packages

Answer

The query you can use to determine the stored procedure names differs depending on your version of DB2 UDB.

For DB2 UDB Version 8.1, use the query:
select pkgschema,pkgname,procname, boundby, definer from syscat.packages a, syscat.procedures b where a.pkgname = substr(implementation,1,8)

Sample output:

PKGSCHEMA PKGNAME PROCNAME
---------- --------- -----------
DB2INST1 P9520507 SP_INSERT


For DB2 UDB Version 8.2, 9.1, 9.5, 9.7, 10.1, 10.5 or higher, use this query. Assuming you are looking for DB2INST1.SP_INSERT:
select r.routineschema, r.routinename, rd.bname as packagename from syscat.routines r, syscat.routinedep rd where r.specificname=rd.specificname and r.routineschema=rd.routineschema and rd.btype='K' and r.routineschema = 'DB2INST1' and r.routinename = 'SP_INSERT'

Sample output:

ROUTINESCHEMA ROUTINENAME PACKAGENAME
------------- ----------- -----------
DB2INST1 SP_INSERT P9520507


For 9.7 and higher, routinemodulename can also be included in case the package belongs to a module:
select r.routineschema, r.routinename, r.routinemodulename, rd.bname as packagename from syscat.routines r, syscat.routinedep rd where r.specificname=rd.specificname and r.routineschema=rd.routineschema and rd.btype='K' and r.routineschema = 'DB2INST1' and r.routinename = 'SP_INSERT'


Notes: Packages get created only for compiled routines.
Basically:
- Module routines are always compiled;
- PL/SQL routines are always compiled;
- A SQL routine is compiled if its body consists of a non-atomic compound statement (BEGIN or BEGIN NOT ATOMIC). A SQL routine whose body consists of an atomic compound statement (BEGIN ATOMIC) or a single RETURN statement are inlined.
Inlined SQL functions do not have packages because they are implemented by expanding the function body into the query graph wherever the function is used.