Creating stored procedures

In Db2 Admin Tool, you can create any of the stored procedure types that are supported by Db2 for z/OS®.

Before you begin

Ensure that the Manage Stored Procedures (ADB2ZP) panel is open. For instructions on how to navigate to this panel, see Stored procedures.

Procedure

To create a stored procedure:

  1. On the Manage Stored Procedures (ADB2ZP) panel, specify option 2, and press Enter.
  2. On the Create Procedure (ADB26CO) panel, specify values for the required fields, and press Enter:
    Figure 1. Create Procedure (ADB26CO) panel
    Start of change
     
 ADB26CO n -------------------- DC1A Create Procedure -------------------- 16:57
 Command ===>                                                                   
                                                                                
 CREATE PROCEDURE                                                               
   
  Use CREATE OR REPLACE . . Y               (Yes/No)
                                                                             
  Schema  . . . . . . . . . SMITHJR  >      (Default is SMITHJR)                 
  Name  . . . . . . . . . . PJNEWSP             > (? to look up)                
                                                                                
  (                                                                             
  Number of parameters  . . 1               (0-255)                             
  )                                                                             
                                                                                
                                                                                
  LANGUAGE  . . . . . . . . SQL             (ASSEMBLE,C,PLI,COBOL,REXX,JAVA,SQL)
                                                                                
  Native SP . . . . . . . . Y               (Yes/No)                            
    VERSION   . . . . . . .                     > (optional, default is V1)
    End of change
    Note: Start of changeThe Use CREATE OR REPLACE field is displayed only if you are running Db2 12 and the current APPLCOMPAT value is 507 or later.End of change
    Tips:
    • To create a native stored procedure, specify SQL in the LANGUAGE field and Y in the Native SP field.
    • To create an external stored procedure, specify the language of the procedure body in the LANGUAGE field.
    • To create an external SQL procedure (which is deprecated in Db2 for z/OS), specify SQL in the LANGUAGE field and N in the Native SP field.
    • To cancel the process of creating a stored procedure, press End.
  3. If the Create Stored Procedure Parameters (ADB26COU) panel is displayed, specify values for the required fields to define the first parameter, and press Enter. Repeat this step until all parameters are defined.

    This panel is displayed if you specified a number greater than zero (0) for the number of stored procedure parameters (the Number of parameters field on the Create Procedure (ADB26CO) panel). This panel is displayed for each parameter that you need to define.

    Figure 2. Create Stored Procedure Parameters (ADB26COU) panel
    ADB26COU  ----------- DC1A Create Stored Procedure Parameters ----------- 19:05
Command ===>                                                                   
                                                                               
                                                                   More:     + 
CREATE OR REPLACE PROCEDURE "PJNEWSP" ..                                                  
 (parameter number 1)                                                          
                                                                               
 Parm type  . . . .                        (IN, OUT, or INOUT)                 
 Parm name  . . . .                     >  (Parameter name)                    
                                                                               
 For a non table like parameter specify:                                       
                                                                               
 Data type  . . . .                        (Built-in only)                     
 Length . . . . . .                        (1 if DBCLOB with units indicator G)
 Scale  . . . . . .                        (used only w/DECIMAL and TIMESTAMP) 
 WITH TIME ZONE . .                        (Yes/No - for TIMESTAMP only)       
                                                                               
 FOR ? DATA . . . .                        (BIT, SBCS, or MIXED)               
 CCSID  . . . . . .                        (ASCII, EBCDIC, or UNICODE)         
 AS LOCATOR . . . .                        (Yes/No - for non-SQL only)
  4. On the Create Stored Procedure Options (ADB26COV) panel, enter values for the relevant options, and press Enter.

    The format of the Create Stored Procedure Options (ADB26COV) panel varies depending on the language and type of stored procedure, the version of Db2, and the current APPLCOMPAT value.

    Figure 3. Example of the Create Stored Procedure Options (ADB26COV) panel
    Start of change
     ADB26COV  ------------- DC1A Create Stored Procedure Options ------------ 19:25
 Command ===>                                                                   
                                                                                
                                                                    More:     + 
 CREATE OR REPLACE PROCEDURE "PJNEWSP" ..                                        
   ( IN "ID" INTEGER) ..                                                         
    
      
  PARAMETER CCSID  . . . .             (ASCII, EBCDIC, or UNICODE) 
                                                                    
  SPECIFIC . . . . . . . . PJNEWSP             >                                    
  RESULT SETS  . . . . . .             (Maximum number of result sets. 0-32767) 
  DETERMINISTIC  . . . . .             (Yes/No)                                 
  CALLED ON NULL . . . . .             (Yes)                                    
  SQL DATA . . . . . . . .             (C - Contain, R - Read, M - Mod )        
                                                                              
  DEBUG WLM ENVIR  . . . .                     > (Debug WLM environment name)   
                                                                              
  DEBUG MODE . . . . . . .             (Disallow, Allow, Disable)               
  ASUTIME LIMIT  . . . . .             (CPU service units or 0 for no limit)
    End of change
  5. Follow the remaining prompts to create the stored procedure.
    The prompts vary depending on the type of procedure that you are creating and your Db2 Admin Tool settings. For example, if you are creating a native SQL procedure or an external SQL procedure, you are prompted to enter the procedure body. Also, if Change Management is enabled or the statement execution prompt is turned on, additional prompts are displayed.
    When you complete all of the prompts, Db2 Admin Tool issues the SQL CREATE PROCEDURE statement with the parameters that you specify.