| The DB2 UDB for z/OS V8 Application Programming and SQL Guide explains in detail how to build SQL procedures in DB2 for zSeries. In this section, the choices you have available are explained. Note that you may need to contact your system programmer or DB2 for zSeries DBA to have him or her set up the environment required to build SQL procedures, because several levels of authorizations are involved in this process. In DB2 for zSeries, a C/C++ compiler is required to build SQL procedures. Make sure these compilers are enabled via a parmlib member or dynamic enablement. The Build Process Building a SQL procedure involves three tasks: Convert the SQL procedure source statements into a C language program with embedded SQL. This is performed by the SQL precompiler. Create an executable load module and a DB2 package from the C language program by: Precompiling the C language program to generate a DBRM and a modified C language program Binding the DBRM to generate a DB2 package. (Refer to the DB2 UDB for z/OS Command Reference Version 8 for the available BIND options.)
Define the SQL procedure to DB2 by executing the CREATE PROCEDURE statement. If you use the SQL procedure processor or the IBM DB2 Development Center to build a SQL procedure, this task is performed for you.
Methods to Build a DB2 for zSeries SQL Procedure There are three methods available for building an SQL procedure: IBM DB2 Development Center. Building DB2 for zSeries SQL procedures using the Development Center is similar to building SQL procedures for LUW. Behind the scenes in the case of zSeries, however, the Development Center invokes the SQL procedure processor DSNTPSMP to perform the build at the DB2 for zSeries server. Refer to Appendix D, "Using the DB2 Development Center," for more details. DB2 for zSeries SQL procedure processor (DSNTPSMP). The SQL procedure processor, DSNTPSMP, is a REXX-stored procedure that performs the three build process steps described in the previous section. As with any other stored procedure, it can be invoked using the SQL CALL statement. As mentioned earlier, the SQL procedure processor is also invoked from the Development Center. Note Before you can run DSNTPSMP, the following needs to be set up: Install DB2 UDB for z/OS REXX Language Support. Create a program that uses the SQL CALL statement. Set up a WLM environment in which to run DSNTPSMP.
The first and third tasks are commonly performed by your system's programmer or DBA.
JCL. With this method, you manually have to code a JCL job using the three steps described earlier in the build process section. Your JCL job will need to do the following:
Preprocess the CREATE PROCEDURE statement by executing program DSNHPC, with the HOST(SQL) option. This process converts the SQL procedure source statements into a C language program. Precompile the C language source program that was generated in Step 1. Compile and link-edit the modified C source statements that were produced in Step 1. Ensure that the compiler options include the option NOSEQ. Bind the DBRM that was produced in Step 1 into a package. Define the SQL procedure to DB2 (puts the SQL procedure definition in the DB2 for zSeries Catalog) by executing the CREATE PROCEDURE statement for the SQL procedure.
As you can see, whereas in DB2 for LUW a CREATE PROCEDURE statement issued from the CLP is enough to build your SQL procedure, for DB2 for zSeries this is not the case. The recommended method to build your SQL procedure is to use the Development Center. Note Connecting to a DB2 for zSeries subsystem from a CLP client and issuing the CREATE PROCEDURE statement will give you syntax errors. To build a DB2 for zSeries SQL procedure from the CLP, you need to invoke the DSNTPSMP SQL procedure processor.
|
