Creating external SQL stored procedures

This article covers the basics of using Db2 Developer Extension to create a simple external SQL stored procedure.

Important: The use of external SQL stored procedures is deprecated in Db2 for z/OS. If possible for your environment, consider using native SQL stored procedures or external Java stored procedures instead.

Before you begin

Before you can run an external SQL stored procedure on Db2 for z/OS, you need to set up your environment accordingly. For instructions, see Setting up support for external SQL procedures.

Creating a basic external SQL stored procedure with code snippets

  1. Open a new .spsql file, and start typing CREATE PROCEDURE. You’ll only need to type a few letters before you see CREATE PROCEDURE in the list of available code snippets.

  2. Select the CREATE PROCEDURE statement (SQL - external) snippet to populate your file with the basic CREATE PROCEDURE structure (in the following example, the procedure name has already been changed to MYPROCEDURE):


The snippet includes some of the more commonly used parameters and an example SELECT statement. The SELECT statement is the part of the stored procedure that gets executed when the stored procedure is called. To see all of the options that you can define in an external SQL stored procedure, open the link that is included at the top of the snippet.

What’s next