Creating a stored procedure

You can also create a stored procedure by selecting the Stored Procedure Builder pop-up menu choice from the Procedure folder. This opens the Stored Procedure Builder program.

Authorities and privileges

To create a stored procedure:

1. Open the Create Procedure notebook.
   
   
2. Specify general properties for the procedure on the Properties page.
   
   
3. Specify language options and parameters on the Parameters page.
   
   
4. Click Add to open the Add Parameter window so that you can define at least one parameter. The Parameters area shows the procedure's parameters. If no parameters are defined, then this area is blank.
   
   
5. Click OK.
   

Specifying general properties

1. In the Procedure name field, type the name of the procedure. The procedure name can be up to 18 characters long and must not duplicate an existing stored procedure at the current server.
2. Optional: In the Authorization ID field, type a DB2 user name. This setting is used to control remote access. When a CALL statement invokes the stored procedure remotely, the system compares this setting to the calling authorization ID. The authorization ID can be up to 8 characters long. If you leave this field blank a default value is used.
3. Optional: In the LUNAME field, type the LUNAME of a system. The LUNAME can be up to 8 characters long. Values in the LUNAME field determine which clients can access stored procedures:
   • If the LUNAME setting contains the local DB2 system's LUNAME, the associated row in SYSIBM.SYSPROCEDURES applies to local applications that issue the SQL CALL statement.
   • If the LUNAME column contains the LUNAME of a remote client, the associated row in SYSIBM.SYSPROCEDURES applies to SQL CALL statements received from that remote client.If LUNAME is blank, the values in this row apply to all systems, including the local DB2 system and clients connected through TCP/IP or SNA.
     
   Tip: To ease migration to future releases of DB2, leave this field blank.
   
4. In the Member name field, type the name of the MVS load module for DB2 to use when running a stored procedure. The name can be up to 8 characters long; it is required.
   
5. Optional: Define the linkage convention that DB2 uses to pass parameters to the stored procedure by selecting a radio button under Linkage convention:

   Simple

   Use this setting if input parameters cannot be null.

   Simple with nulls

   Use this setting if an indicator array is passed to the stored procedure (and where null input parameters are allowed).

6. Optional: In the Collection ID field, type the name of the package collection to use when the stored procedure is executed.

   The collection ID can be up to 18 characters long. Leaving this field blank indicates that the package collection is the same as the package collection of the program that issued the SQL CALL statement.
   

7. Optional: In the Service units field, type the number of service units permitted for any single invocation of the stored procedure.

   This field accepts non-negative integers; the default value is zero. Zero indicates that there is no limit on the service units. If you set this field to a value other than zero and a stored procedure uses more service units than allowed by the setting, DB2 cancels the stored procedure.
   

8. Optional: In the Result sets returned field, type the maximum number of query result sets that can be returned by this stored procedure.

   This field accepts the small integer data type; the default value is zero. A value of zero indicates that no query results set is returned.
   

9. Optional: In the WLM environment name field, type the name of the address space where the stored procedure will run.

   The WLM environment name can be up to 18 characters long. The default value is blank, which indicates that the procedure will run in the stored procedure's address space established by DB2.
   

10. Optional: Select a program type to specify whether this stored procedure runs as a main routine or a subroutine.
    
11. If you want the load module to remain resident in memory after the stored procedure ends, select the Yes check box under Stay resident at exit.
12. If you want to require a special RACF environment to control access to non-SQL resources, select the RACF required check box.

    Tip: If a stored procedure accesses only SQL objects, you can use the default. Stored procedures that access non-DB2 resource, such as IMS or CICS transactions, MVS/APPC conversations, or VSAM files, might need to use RACF to manage access to non-SQL resources. If you select RACF required, then DB2 sets up a separate RACF environment each time the stored procedure is invoked.

    Important: If the WLM environment setting is blank then the stored procedure runs in the stored procedures address space established by DB2, and the user ID of the stored procedures address space is used to access non-SQL resources.

13. If you want the unit of work to be committed immediately upon the successful return from this stored procedure, select the Commit on return check box. (A non-negative SQL code signifies a successful return.)

Specifying language options and parameters

1. From the Language list, select the programming language used to create the stored procedure. You can choose ASSEMBLE, PL1, COBOL, or C. Language type SQL is available only if you create the stored procedure from the Stored Procedure Builder program. (Select Stored Procedure Builder pop-up menu choice from the Procedures folder in the Control Center.)
   
   
2. Optional: If you don't want to use default the language environment run-time options, type options in the Run-time options field. You can type up to 256 characters. Example:
   

   MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)

   
   
   

Changing parameter properties

1. Select the parameter that you want to change.
2. Click the Change push button.
3. Use the Change Parameter window to change the following properties:Parameter type
   Parameter characteristics
   Parameter name
   

Removing parameters

1. Select the parameter that you want to change.
2. Click the Remove push button.

Moving parameters

To change a parameter's ordinal position in the parameter list:

1. Select the parameter in the Parameters list.
2. Change its position: click the Move up push button or the Move down push button.