Creating native SQL procedures

A native SQL procedure is a procedure whose body is written entirely in SQL and is created by issuing a single SQL statement, CREATE PROCEDURE.

Before you begin

Before you create a native SQL procedure, Configure Db2 for running stored procedures and user-defined functions during installation or Configure Db2 for running stored procedures and user-defined functions during migration if the native SQL procedure satisfies at least one of the following conditions:

The native SQL procedure calls at least one external stored procedure, external SQL procedure, or user-defined function.
The native SQL procedure is defined with ALLOW DEBUG MODE or DISALLOW DEBUG MODE. If you specify DISABLE DEBUG MODE, you do not need to set up the stored procedure environment.

About this task

A native SQL procedure is a procedure whose body is written entirely in SQL. The body is written in the SQL procedural language (SQL PL). A native SQL procedure is created by issuing a single SQL statement, CREATE PROCEDURE. Native SQL procedures do not require any other program preparation, such as precompiling, compiling, or link-editing source code. Native SQL procedures are executed as SQL statements that are bound in a Db2 package. Native SQL procedures do not have an associated external application program. Native SQL procedures are more fully supported, easier to maintain, and typically perform better than external SQL procedures, which are deprecated.

Procedure

To create a native SQL procedure, perform one of the following actions:

Use a tool such as Db2 Developer Extension to specify the source statements for the SQL procedure and deploy the SQL procedure to Db2. For more information, see IBM Db2 for z/OS Developer Extension for Visual Studio Code.
Use IBM Data Studio to specify the source statements for the SQL procedure and deploy the SQL procedure to Db2.
IBM Data Studio also allows you to create copies of the procedure package as needed and to deploy the procedure to remote servers.
Manually deploy the native SQL procedure by completing the following steps:
1. Issue the CREATE PROCEDURE statement:
  - Include a procedure body written entirely in the SQL procedural language (SQL PL). For more information about what you can do within the procedure body, see SQL-routine-body in CREATE PROCEDURE statement (SQL - native procedure), SQL-control-statement in SQL procedural language (SQL PL), and the following information:
  - Do not include the FENCED or EXTERNAL keywords, which specify the creation of an external SQL procedures, which are deprecated.
  - You can specify the AUTONOMOUS keyword to enable the procedure to commit without committing the work of the calling application. Autonomous procedures cannot see uncommitted changes of the calling application, and they cannot call other autonomous procedures.
  When you issue this CREATE PROCEDURE statement, the first version of this procedure is defined to Db2, and a package is implicitly bound with the options that you specify on the CREATE PROCEDURE statement.
2. If the native SQL procedure contains one or more of the following statements or references, make copies of the native SQL procedure package, as needed:
  - CONNECT
  - SET CURRENT PACKAGESET
  - SET CURRENT PACKAGE PATH
  - A table reference with a three-part name that refers to a location other than the current server or refers to an alias that resolves to such a name.
3. If you plan to call the native SQL procedure at another Db2 server, deploy the procedure to another Db2 for z/OS server. You can customize the bind options at the same time.
4. Authorize the appropriate users to call the stored procedure.

What to do next

After you create a native SQL procedure, you can create additional versions of the procedure as needed. For more information, see Creating new versions of native SQL procedures.