In today's globalized world it is very likely that multiple applications written in 

multiple languages working on multiple platforms may use the same database 

for multiple purposes. A stored procedure may ensure that this database 

stays consistent and applies to the business rules defined by the owner of the 

database.

Enriching and securing the application. For example, if there is a requirement 

for additional auditing that is beyond the DBMS capabilities, stored 

procedures can provide additional function.

This section gives you a brief introduction to the implementation of SQL/PSM as 

SQL stored procedures in DB2 UDB. The introduction includes the setup of the 

application development environment and SQL procedure fundamentals.

Setting up the environment

To successfully work with DB2 UDB stored procedures, the DB2 Application 

Development Client and a DB2 supported C/C++ compiler are required. Here is a 

list of supported compilers for a UNIX environment: 

AIX: IBM VisualAge  C++ 5.0 

Solaris: Forte C++ Version 5.0 

Linux: GNU/Linux g++

HP UX: HP aC++ Version A.03.31

You may have to configure your C/C++ compiler environment if the default setting 

is not appropriate. Use the 

db2set 

command to configure the DB2 registry:

  DB2_SQLROUTINE_COMPILER_PATH

 and

  DB2_SQLROUTINE_COMPILE_COMMAND

Example 10 3 illustrates the use of the 

db2set

 command to set the compile 

command to a non default compiler.

Example 10 3   Configuring the compiler environment

db2set DB2_SQLROUTINE_COMPILE_COMMAND=cc  fpic   D_REENTRANT \

 I$HOME/sqllib/include SQLROUTINE_FILENAME.c \

 shared  lpthread  o SQLROUTINE_FILENAME  L$HOME/sqllib/lib  ldb2

To ensure your application development environment is set up correctly, you 

may want to run the 

CREATE

 statement shown in Example 10 4 as the most 

simple procedure possibly created.

Example 10 4   Simple CREATE PROCEDURE

db2 =>

db2 => create procedure myfirst begin end

 Chapter 10. Advanced DB2 UDB features 

321