Stored Procedure
The following figure shows a sample information screen for a stored procedure.
Stored Procedure Information Screen
The following fields appear if you are displaying information for a stored procedure:
Parameter Information
When you select this option, the Parameter Information screen appears (Parameter Information Screen).
Run Time Information
When you select this option, the Run Time Options window appears (The following figure).
Run Time Options Window
Remarks/Comment On
When you select this option, the Comment On screen appears (The following figure).
Comments On Window
Createdby
Primary creator ID of person who created the stored procedure.
Origin
Origin of the routine; E for stored procedure.
External Name
Eight character module name.
Owner
Authid of the owner of the stored procedure.
Null Call
The CALLED ON NOT NULL INPUT option of a stored procedure.
N | Stored procedure is not called if any parameter has a NULL value. |
Y | Stored procedure is called if any parameter has a NULL value. |
blank | Stored procedure is a function, but not an external function. |
Collection ID
Name of the collection ID that contains the stored procedure.
Program Type
Either M(ain) or S(ub).
M | Run the stored procedure as a main routine. |
S | Run the stored procedure as a subroutine. |
Fenced
Indicates that this routine runs separately from the Db2 address space. The values that appear are defaults and are currently the only allowable OS/390 values.
Specific Name
Specific name of the stored procedure.
Result Sets
A synonym for Dynamic Result Sets. A number between 0 and 32767.
DBINFO
Default is N. If Y, then an additional argument is passed when the stored procedure is invoked. The argument may contain information such as the application run-time authorization ID or schema name.
Commit on Return
Default is N. If Y, then Db2 commits the transaction immediately on return from the stored procedure.
Parm Count
Number of parameters for the stored procedure.
SQL Data
Default is MODIFIES. Indicates whether the stored procedure can execute any SQL statements, and if so, what type.
N(o SQL) | Stored procedure cannot run any SQL statements. |
M(odifies) | Stored procedure can run any SQL statement except statements not supported in any stored procedure. |
R(eads) | Stored procedure cannot run SQL statements that modify data. |
C(ontains) | Stored procedure cannot run any SQL statements that read or modify data. |
Deterministic
Default is N. If Y, then the stored procedure returns the same result from successive calls that have identical input arguments.
ASUTIME
Default is NO LIMIT. Specifies the total amount of processor time (in CPU service units) that a single run of the stored procedure can use.
Parm Style
Default is DB2SQL. The linkage convention used to send parameters to the stored procedure.
D(B2SQL) | Sends the parameters on the CALL statement to the stored procedure, along with a null indicator for each parameter on the CALL statement, the SQLSTATE to return to Db2, the qualified and specific name of the stored procedure, and the SQL diagnostic string to return to Db2. |
G(eneral) | Sends only the parameters on the CALL statement to the stored procedure. |
N(general with nulls) | Sends the parameters on the CALL statement to the stored procedure, along with an additional argument containing a record of null indicators for each of the parameters on the CALL statement related to null parameter values. |
Stay Resident
Default is N. If Y, then the stored procedure load module remains resident in member after the stored procedure ends.
Special Register
Describes how special register values are inherited.
I(nherit) | The values of special registers are inherited based on rules listed in the table for characteristics of special registers (see Db2 SQL Reference Manual) |
D(efault) | The values of special registers are initialized to the default values. |
LOBcolumns
The number of LOB columns found in the parameter list.
Security
Default is DB2. Specifies how the stored procedure works with external security products.
D(B2) | No special external security environment needed. If the stored procedure accesses resources protected by an external security environment, then access is done using the authorization ID associated with the stored procedure address space. If NO WLM ENVIRONMENT is set, then SECURITY DB2 is the only valid value. |
U(ser) | An external security environment should be established for the stored procedure. If the stored procedure accesses resources protected by an external security environment, then access is done using the authorization ID of the user who invoked the stored procedure. |
C(definer) | An external security environment should be established for the stored procedure. If the stored procedure accesses resources protected by an external security environment, then access is done using the authorization ID of the owner of the stored procedure. |
Createdts
Timestamp when stored procedure was created (yy-mm-dd-hh.mm.ss.tttttt).
Routineid
Internal identifier of the stored procedure.
Alteredts
For a stored procedure, time when the latest ALTER PROCEDURE statement was applied. If no ALTER PROCEDURE statement was applied, this field has the same value as CREATEDTS.
WLM Environment
Identifies the MVS workload manager environment in which to run the stored procedure. To use a WLM environment, you must have authority for that environment.
WLM Env Nested
For nested stored procedure calls, indicates whether the address space of the calling stored procedure is used to run the nested stored procedure:
N | The nested stored procedure does not run in the specified WLM environment if the calling stored procedure is not running in the specified WLM environment. WLM ENVIRONMENT name was specified. |
Y | The nested stored procedure runs in the same WLM environment as the calling stored procedure. WLM ENVIRONMENT(name,*) was specified. |
blank | WLM_ENVIRONMENT is blank. |
Parameter Information
To get the source type for a distinct type, use the UDT command (see UDT).
Parameter Information Screen
PARAMETER NAME
Name of the parameter.
TYPE SCHEMA
Schema of the data type of the parameter.
TYPE NAME
Name of the data type of the parameter.
RT (Row Type)
I(n) | The parameter is an input parameter to the stored procedure. |
O(ut) | The parameter is an output parameter that is returned by the stored procedure. |
IO | The parameter is both an input and output parameter for the stored procedure. |
LENGTH
Length of the data type.
SS (Scale)
Scale of the data type.
ST (Subtype)
If the data type is a distinct type, this is the subtype of the distinct type, which is based on the subtype of its source type:
B | Subtype is FOR BIT DATA. |
S | Subtype is FOR SBCS DATA. |
M | Subtype is FOR MIXED DATA. |
blank | The source type is not a character type. |
ENC (Encoding Scheme)
Encoding scheme of the parameter:
A | ASCII |
E | EBCDIC |
U | UNICODE |
blank | The source type is not a character, graphic, or datetime type. |
AS LOC
Locator.
Y | A locator to a value is to be passed. |
N | The actual value is to be passed. |
TABLE COLNO
For table parameters, the column number of the table. For all other, the value is blank.