Version 4.2.5
 —  Database Management System Interfaces  —

NDB - Procedure Maintenance

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.


Invoking Procedure Maintenance

Start of instruction set To invoke the Procedure Maintenance function

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.

Top of page

Insert a Stored Procedure

Start of instruction set To insert a new stored procedure into the catalog

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.

Start of instruction set To define a parameter list

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.

Top of page

Modify a Stored Procedure

Start of instruction set To modify a stored procedure in the catalog

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

Start of instruction set To display a list of stored procedures

  1. Proceed as above but do not enter a procedure name nor use the asterisks "*" notation.

    The list of stored procedures will be displayed.

  2. From this list select a specific stored procedure with the line command "MO".

    The stored procedure is then displayed for modification.

  3. Proceed as described in section Insert a Stored Procedure above.

Top of page

Insert Data Areas

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/   !
     +-------------------------------------------------------------------+

Top of page

Save PARMLIST as Natural Object

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.

Top of page

List Stored Procedures

Start of instruction set To display a list of stored procedures

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.

Start of instruction set To display a part of the procedures

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

Top of page

Delete a Stored Procedure

Start of instruction set To delete a stored procedure from the catalog:

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.

Top of page