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

Technote (FAQ)


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.

Document information


More support for:

DB2 for Linux, UNIX and Windows
Application Programming - SQL Routines (SQL stored procedures)

Software version:

9.5, 9.7, 10.1, 10.5

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1237940

Modified date:

2008-08-19

Translate my page

Content navigation