SQL procedures
An SQL procedure is a stored procedure that contains only SQL statements.
The source code for these procedures (the SQL statements) is specified in CREATE PROCEDURE statement. The part of the CREATE PROCEDURE statement that contains SQL statements is called the procedure body.
Types of SQL procedures
- Native SQL procedures
- The procedure body is written exclusively in SQL statements, including SQL procedural language (SQL PL) statements. The procedure body is contained and specified in the procedure definition along with various attributes of the procedure. A package is generated for a native SQL procedure. It contains the procedure body, including control statements. It might sometimes also include statements generated by Db2. Each time that the procedure is invoked, the package executes one or more times.
All SQL procedures that are created with a CREATE PROCEDURE statement that does not specify the FENCED or EXTERNAL options are native SQL procedures. More capabilities are supported for native SQL procedures, they usually perform better than external SQL procedures, and no associated C program is generated for them.
For more information, see Creating native SQL procedures.
- External SQL procedures (deprecated)
- The procedure body is written exclusively in SQL statements, including SQL procedural language (SQL PL) statements. The procedure body is specified in the procedure definition along with various attributes of the procedure. A C program and an associated package are generated for an external SQL procedure. It contains the procedure body, including control statements. It might sometimes also include statements generated by Db2.Each time that the procedure is invoked, the package executes one or more times.
Native SQL procedures are more fully supported, easier to maintain, and typically perform better than external SQL procedures, which are deprecated.
For more information, see Creating external SQL procedures (deprecated).
Native SQL procedures
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.
Native SQL procedures have the following advantages:
- You can create them in one step.
- They do not run in a WLM environment.
- They might be eligible for zIIP redirect if they are invoked remotely through a DRDA client.
- They usually perform better than external SQL procedures.
- They support more capabilities, such as nested compound statements, than external SQL procedures.
- Db2 can manage multiple versions of these procedures for you.
- You can specify that the SQL procedure commits autonomously, without committing the work of the calling application.
All SQL procedures that are created without the FENCED or EXTERNAL options in the CREATE PROCEDURE statement are native SQL procedures.
External SQL procedures (deprecated)
External SQL procedures are deprecated and not as fully supported as native SQL procedures. For best results, create native SQL procedures instead. For more information, see Creating native SQL procedures and Migrating an external SQL procedure to a native SQL procedure.All SQL procedures that were created prior to DB2® 9 are external SQL procedures. Starting in Version DB2 9, you can create an external SQL procedure by specifying FENCED or EXTERNAL in the CREATE PROCEDURE statement.