Developing SQL Stored Procedures

DB2 implements SQL stored procedures as external C programs. The DB2 precompiler can take an SQL procedure as input and write out a C source module. An SQL procedure is created by using one of the following three methods: DB2 Development Center stored procedure, directly invoking DSNTPSMP, or using JCL or CLIST to prepare.

When an SQL procedure is built using the DB2 Development Center, all the "code" is developed on the client workstation, and the completed procedure is passed to the DSNTPSMP stored procedure. DSNTPSMP is the z/OS SQL Procedure Processor, a REXX-stored procedure, and it is also fully customizable. This processor invokes the following steps:


SQL precompile


C precompile


C compile






Procedure definition



Once the procedure is built in this fashion, it is immediately executable. When using the other two methods for developing an SQL procedure, they bypass the DB2 Development Center and either directly invoke DSNTPSMP or execute these steps under manual control.

DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson © 2008-2017.
If you may any questions please contact us: