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
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.