Online Documentation for SQL Manager for DB2

Procedures


A Procedure is a set of procedural constructs and embedded SQL statements that is stored in the database and can be called by name. Stored procedures allow an application program to be run in two parts. One part runs on the client and the other on the server. This allows one call to produce several accesses to the database.

 

 

Creating Procedures

 

To create a new procedure:

Hint: To create a new procedure, you can also right-click the Procedures node of the DB Explorer tree and select the New Procedure... context menu item.

 

To create a new procedure with the same properties as one of existing procedures has:

Alternatively, you can right-click a procedure in the DB Explorer tree and select the Duplicate Procedure <procedure_name>... context menu item.

 

Duplicate Object Wizard allows you to select the database to create a new procedure in, and to edit the result SQL statement for creating the procedure.

 

 

Editing Procedures

 

To edit an existing procedure:

  • select the procedure for editing in the DB Explorer tree (type the first letters of the procedure name for quick search);
  • right-click the object and select the Edit Procedure <procedure_name>... context menu item, or simply double-click the procedure;
  • edit procedure definition using the appropriate tabs of Procedure Editor.

 

Executing Procedures

 

To execute a procedure:

  • select the procedure to execute in the DB Explorer tree (type the first letters of the procedure name for quick search);
  • right-click the object and select the Edit Procedure <procedure_name>... context menu item, or simply double-click the procedure;
  • execute the procedure using the Execute Procedure Navigation bar item of Procedure Editor.

 

Dropping Procedures

 

To drop a procedure:

  • select the procedure to drop in the DB Explorer tree;
  • right-click the object and select the Drop Procedure <procedure_name>... context menu item;
  • confirm dropping in the dialog window.

 

Note: If more convenient, you can also use the following shortcuts:

Ctrl+N to create a new procedure;

Ctrl+O to edit the selected procedure;

Shift+Del to drop the object from the database.