DB2 Version 10.1 for Linux, UNIX, and Windows

GET_ROUTINE_NAME procedure - get SQL PL object name

The GET_ROUTINE_NAME procedure returns the name of an SQL PL object given the object's LIB_ID.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the routine
DATAACCESS authority
SQLADM authority
DBADM authority

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the procedure is automatically created.

Syntax


>>-GET_ROUTINE_NAME--(--lib_id--,--type--,--schema--,----------->

>--module--,--name--,--specific_name--)------------------------><

The schema is SYSPROC.

Routine parameters

lib_id

An input argument of type INTEGER that specifies the LIB_ID corresponding to a compiled SQL PL routine or trigger. This is the value of the SQLERRD(4) field in the SQLCA for a runtime SQL PL error.

type

An output argument of type CHAR(1) that specifies the type of object that raised the error, using one of the following values:

'P' for a procedure
'F' for a function
'T' for a trigger

schema

An output argument of type VARCHAR(128) that specifies the schema to which the object belongs.

module

An output argument of type VARCHAR(128) that specifies the module to which the object belongs. NULL if the object is a trigger or is not part of a module.

name

An output argument of type VARCHAR(128) that specifies the name of the object.

specific_name

An output argument of type VARCHAR(128) that specifies the specific name of the object. NULL if the object is a trigger.

Usage notes

When the SQLCA structure contains an error raised during the execution of an SQL PL routine or trigger, the SQLCAID field has the value 'SQLCAM' and SQLERRM(4) has a numeric value that uniquely identifies the object that raised the error. This unique identifier corresponds to the LIB_ID column in the SYSCAT.ROUTINES and SYSCAT.TRIGGERS catalog views. The GET_ROUTINE_NAME routine maps a given LIB_ID value to the corresponding object name.
If the value provided in the first argument to SYSPROC.GET_ROUTINE_NAME does not correspond to an SQL PL routine or trigger, the procedure returns NULL in all the output parameters, indicating that the routine or trigger could not be found.

Example

Assume the execution of an application finishes with an error described in the following SQLCA structure:

   SQLCA Information
   
    sqlcaid : SQLCAM    sqlcabc: 136   sqlcode: -801 
    ...
    sqlerrd : (1) 0         (2) 0                (3) 3
              (4) 13152254  (5) 0                (6) 0
   
    ...
    sqlstate: 22012

The presence of SQLCAM in the SQLCAID field indicates that the error occurred during the execution of an SQL PL object. The values of 3 and 13152254 in the SQLERRD(3) and SQLERRD(4) fields indicate that the error originated in the statement at line number 3 of the SQL PL object identified by the value 13152254. To determine what the SQL PL object is, call the SYSPROC.GET_ROUTINE_NAME in the following manner:

   db2 call "GET_ROUTINE_NAME(13152254, ?, ?, ?, ?, ?)"
   
   Value of output parameters
   --------------------------
   Parameter Name  : OBJTYPE
   Parameter Value : P
   
   Parameter Name  : OBJSCHEMA
   Parameter Value : APPDEV
   
   Parameter Name  : OBJMODULE
   Parameter Value : -
   
   Parameter Name  : OBJNAME
   Parameter Value : PROC2
   
   Parameter Name  : OBJSPECIFIC
   Parameter Value : APPDEV_PROC2
   
   Return Status = 0

The values of the OUT parameters from the call to SYSPROC.GET_ROUTINE_NAME indicate that the error was raised from the stored procedure APPDEV.PROC2.