How to map stored procedure names to packages

Technote (FAQ)


Question

This document provides information on how to find the stored procedure 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 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:

PCKGSCHEMA 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 the query:
select procschema, procname, bname as packagename from
syscat.procedures, syscat.routinedep where
syscat.procedures.specificname=syscat.routinedep.routinename


Sample output:

PROCSCHEMA PROCNAME PACKAGENAME
---------- --------- -----------
DB2INST1 SP_INSERT P9520507


For User Defined Functions, the similar query below can be used:
select funcschema, funcname, bname as packagename from
syscat.functions, syscat.routinedep where
syscat.functions.specificname=syscat.routinedep.routinename

For DB2 UDB Version 10.x and higher, the following can also be used:

db2 "values routine_package('DB2INST1','SP_INSERT')"

1
---------------------------
DB2INST1.P9520507

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.

Rate this page:

(0 users)Average rating

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, Linux pSeries, Linux zSeries, Solaris, Windows

Reference #:

1237940

Modified date:

2008-08-19

Translate my page

Machine Translation

Content navigation