As described below, this function does not apply to all DB2 versions:
From DB2 UDB, the handling of stored procedures in DB2 has changed, and stored procedures can no longer be maintained with the Procedure Maintenance function. Instead, for maintaining stored procedures, DB2 provides the new statements CREATE PROCEDURE and ALTER PROCEDURE as described in the relevant DB2 literature by IBM.
This section covers the following topics:
See also Natural Stored Procedures in the section Statements and System Variables.
To invoke the Procedure Maintenance function
Enter function code "P" on the Natural Tools for DB2 Main Menu.
The Procedure Maintenance menu is displayed:
11:34:09 ***** NATURAL TOOLS FOR DB2 ***** 2006-05-24
Procedure Maintenance
Code Function
I Insert New Procedure
D Delete Procedure
M Modify Procedure
L List Procedures
? Help
. Exit
Code .. _ Procedure Name .. __________________
Authid .......... ________
Luname .......... ________
Command ===>
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--
Help Exit Canc |
The following functions are available:
| Code | Description |
|---|---|
I |
Inserts a new DB2 stored procedure into the SYSIBM.SYSPROCEDURES table. |
D |
Deletes one or several DB2 stored procedure(s). |
M |
Modifies a DB2 stored procedure. |
L |
Lists all DB2 stored procedures or a part of them. |
The following parameters can be specified:
| Parameter | Description |
|---|---|
Procedure Name |
Specifies the name of the procedure. |
Authid |
Specifies the authorization ID. |
Luname |
Specifies the LU name. |
To insert a new stored procedure into the catalog
Enter function code "I" on the Procedure Maintenance menu.
The following screen is displayed:
14:53:28 ***** NATURAL TOOLS FOR DB2 ***** 2006-05-24
- Insert Procedure -
PROCEDURE = DBA ______________ LOADMOD = ________
AUTHID = ________ LINKAGE = _ (N,blank)
LUNAME = ________ COLLID = __________________
ASUTIME = __________ STAYRESIDENT = _ (Y,blank)
IBMREQD = _ (Y/N) RESULT_SETS = _____
PGM_TYPE = _ (M/S) EXTERNAL_SECURITY = _ (Y/N)
WLM_ENV = __________________ COMMIT_ON_RETURN = _ (Y/N)
LANGUAGE = ________ (ASSEMBLER,PLI,COBOL,C)
RUNOPTS = ' ___________________________________________________
___________________________________________________
___________________________________________________
___________________________________________________
__________________________________________________'
PARMLIST: - define PARMLIST: N (Y/N)
Command ===>
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
Help Error Exit Exec Canc |
Here, you can specify the fields of the new stored procedure. The fields PROCEDURE, LOADMOD, IBMREQD and LANGUAGE are mandatory.
To define a parameter list
Enter "y" in the field "Define PARMLIST".
You can then enter the parameter fields in a separate screen.
The first column in the parameter screen is used for editing purposes, possible commands are "I" and "D" for insertion and deletion. In the second column, the name of the parameter can be entered. The third column is mandatory and specifies the type of the parameter. Entering "?" displays a list of possible types from which one type may be selected. In the next column, a length can be entered. The Natural for DB2 checks whether the specified type and length value fit together. The next columns define the subtype and the in/out value.
When all parameters are specified, press ENTER to return to the Insert Procedure screen.
To actually insert the stored procedure and add it to the catalog, press PF4. If pressing PF4 results in an error from DB2, press PF2 to display the error information.
To modify a stored procedure in the catalog
Enter function code "M" in the Procedure Maintenance menu together with a procedure name as unique identifier.
The following screen is displayed:
14:55:57 ***** NATURAL TOOLS FOR DB2 ***** 2006-05-24
- Modify Procedure -
PROCEDURE = DBAPROC___________ LOADMOD = DBAMOD__
AUTHID = ________ LINKAGE = _ (N,blank)
LUNAME = ________ COLLID = __________________
ASUTIME = __________ STAYRESIDENT = _ (Y,blank)
IBMREQD = Y (Y/N) RESULT_SETS = _____
PGM_TYPE = _ (M/S) EXTERNAL_SECURITY = _ (Y/N)
WLM_ENV = __________________ COMMIT_ON_RETURN = _ (Y/N)
LANGUAGE = C_______ (ASSEMBLER,PLI,COBOL,C)
RUNOPTS = ' ___________________________________________________
___________________________________________________
___________________________________________________
___________________________________________________
__________________________________________________ '
PARMLIST: X display PARMLIST: N (Y/N)
Command ===>
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
Help Error Exit Exec Canc |
To display a list of stored procedures
Proceed as above but do not enter a procedure name nor use the asterisks "*" notation.
The list of stored procedures will be displayed.
From this list select a specific stored procedure with the line command "MO".
The stored procedure is then displayed for modification.
Proceed as described in section Insert a Stored Procedure above.
Fields of data areas can be inserted automatically to SYSPROCEDURES.
Proceed as described in the section Insert a Stored Procedure or Modify a Stored Procedure .
14:57:16 ***** NATURAL TOOLS FOR DB2 ***** 2006-05-24
+-------------------------------------------------------------------+
! PARMLIST = !
! !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! --------------------------------------------------------------- !
! cmd parm-name parm-type length subtype inout !
! !
! I/ optional mandatory blank/ blank/ !
! D SBCS/ IN/ !
! I=insert MIXED/ OUT/ !
+-------------------------------------------------------------------+
Command ===>
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
Exit Parml Data - + Canc |
In the PARMLIST screen (above,) press PF5/Data to display the following window.
14:57:16 ***** NATURAL TOOLS FOR DB2 ***** 2006-05-24
+-------------------------------------------------------------------+
! PARMLIST = !
! !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ _______________ ( _____ ) FOR _____ DATA _____ !
! _ ________ __________ +--------------------------------+ ___ !
! _ ________ __________ ! ! ___ !
! _ ________ __________ ! Library : ________ ! ___ !
! _ ________ __________ ! Member : ________ ! ___ !
! _ ________ __________ ! ! ___ !
! _ ________ __________ ! ! ___ !
! _ ________ __________ ! Enter Library and LDA/PDA name ! ___ !
! _ ________ __________ ! and press enter to insert ! ___ !
! ------------------------ ! press PF3 to cancel ! --- !
! cmd parm-name parm-type +--------------------------------+ out !
! !
! I/ optional mandatory blank/ blank/ !
! D SBCS/ IN/ !
! I=insert MIXED/ OUT/ !
+-------------------------------------------------------------------+
Command ===>
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
Exit Parml Data - + Canc |
Enter the library name and the name of the LDA or PDA to be inserted into the PARMLIST definition of the stored procedure. A maximum of 100 parameters can be inserted.
The parameter STCB will be generated automatically at the top of the PARMLIST.
The individual field names of the LDA or PDA are truncated to the first 8 characters in the PARMLIST parmnames (DB2 restriction).
14:56:53 Page 2 2006-05-24
+-------------------------------------------------------------------+
! PARMLIST = !
- ! !
! _ STCB____ VARCHAR________ ( 794__ ) FOR _____ DATA _____ !
! _ NAME____ CHAR___________ ( 18___ ) FOR _____ DATA _____ !
! _ CREATOR_ CHAR___________ ( 8____ ) FOR _____ DATA _____ !
! _ TYPE____ CHAR___________ ( 1____ ) FOR _____ DATA _____ !
! _ DBNAME__ CHAR___________ ( 8____ ) FOR _____ DATA _____ !
! _ TSNAME__ CHAR___________ ( 8____ ) FOR _____ DATA _____ !
! _ DBID____ SMALLINT_______ ( _____ ) FOR _____ DATA _____ !
! _ OBID____ SMALLINT_______ ( _____ ) FOR _____ DATA _____ !
! _ COLCOUNT SMALLINT_______ ( _____ ) FOR _____ DATA _____ !
! _ EDPROC__ CHAR___________ ( 8____ ) FOR _____ DATA _____ !
! --------------------------------------------------------------- !
! cmd parm-name parm-type length subtype inout !
! !
! I/ optional mandatory blank/ blank/ !
! D SBCS/ IN/ !
! I=insert MIXED/ OUT/ !
+-------------------------------------------------------------------+ |
You can store the field definitions of the PARMLIST in a Natural program which you can stow and modify. (It is not necessary to enter the field definitions manually.)
To do so, press PF4/Parml.
The field definitions are converted to a Natural object. The Natural object is saved on FUSER in the user library you specified.
The Natural object can be used as input for the LDA editor after the stow.
To display a list of stored procedures
Enter function code "L" in the Procedure Maintenance menu.
The following screen is displayed:
11:17:05 ***** NATURAL TOOLS FOR DB2 ***** 2006-05-24
PROCEDURE PROC*.SAG.* S 01 Row 0 of 3 Columns 041075
====> Scroll ===> PAGE
PROCEDURE AUTHID LUNAME LOADMOD L COLLID LANGU
** ******************************* top of data*******************************
__ PROC1 SAG MODULE1 ASSEM
__ PROC2 SAG MODULE2
__ PROC3 SAG MODULE3
** ***************************** bottom of data******************************
Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
Help Exit Rfind - + < > Canc |
The screen contains a list of all stored procedures in the catalog.
To display a part of the procedures
use the asterisks "*" notation.
The following line commands are available:
| Command | Description |
|---|---|
LI |
Display the stored procedure in detail |
MO |
Modify the stored procedure |
DE |
Delete the stored procedure |
To delete a stored procedure from the catalog:
Enter function code "D" in the Procedure Maintenance menu together with a procedure name as unique identifier.
The procedure is then deleted.
If you do not enter a procedure name or if you use the "*" notation, a screen with a list of procedures appears, where you can enter the line command "DE" to delete a procedure.
