Question & Answer
Question
This document provides information on how to find the stored procedure or user defined function (UDF) name associated with a package. The technique differs between DB2 UDB Version 8.1 to DB2 UDB Version 8.2 (also known as DB2 UDB Version 8.1 FixPak 7) or later, including 9.1, 9.5, 9.7, 10.1 and 10.5
Cause
Changes introduced in DB2® Universal Database™ (DB2 UDB) Version 8.2 allowed SQL stored procedures and UDFs to be created without an existing C compiler. These changes modified some columns in the DB2 UDB system catalogs.
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.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21237940