Stored Procedures, User Defined Functions and Triggers in IBM DB2 8.2

Introduction

For most of us working with stored procedures, user defined functions and triggers, we have been on the lighter side, from a developer’s point of view.

I shall not be explaining everything, but will delve more into code and only a brief introduction will be given about each, as this is a Level 200 session.

So, for all those developers out there, I have a treat!!! We shall be exploring the wonderful world of Stored Procedures, User Defined Functions and Triggers in greater detail along with sample examples.

Let’s get started!!! We need to have the following system and software requirements in place.

System requirements:

A basic P4 machine with 512 MB RAM, about 20 GB HDD, a pointing device, keyboard and monitor.

Software requirements:

1. Windows 2000 and above; and
2. A personal copy of DB2 UDB (Any edition will do. As I am still developing for test purposes only, I will be using the Personal Edition of DB2 UDB 8.2 for Windows).

We shall now get started on stored procedures.

Stored Procedures

Well, stored procedures are a set of pre-compiled SQL statements that will enable users to run multiple statements in one go. Key features include:

1. It is very fast and can save lot of memory from the database server;
2. You can stored procedures using SQL PL, C++, JAVA, CLR (A new feature included in version 8.2, much before the competition);
3. And SQL procedures are very easy to write and maintain as we shall see in the coming sections;
4. Improved Efficiency; and
5. Code Reuse.

Next, we shall take a look at implementing stored procedures.

In this section, we shall learn how to implement an SQL stored procedure. You must keep in mind the following points while implementing SQL stored procedures.

1. The stored procedure name;
2. The parameters being passed (IN, OUT, INOUT); and
3. The language being implemented, in our case, SQL.

The syntax for creating an SQL stored procedure is as follows:

1. CREATE PROCEDURE SCHEMA. [PROCEDURE NAME] (parameter variable datatype(size))
2. Language SQL
3. P1: BEGIN
n We can enter our SQL statements here -- This is a comment in DB2
4. 4 SELECT * from ANILM.EMPLOYEES;
5. 5 END P1.

Let me explain the above syntax.
Line 1: Tells us how to create a procedure with the various parameters.
Line 2: Specifies the language being used to create the stored procedure.
Line 3: Defines our Procedure Begin statement.
Line 4: Defines our SQL Select Statement, which will get our records from the table.
And finally 
Line 5: Ends the stored procedure.

We can now compile it and run it.

Let us now create a stored procedure that can be used in the SAMPLE Database provided by DB2.

The Employee Sample Stored Procedure
To create a stored procedure in DB2, there are two ways:
1. Via the Command Center; and
2. Via the Application Development Center.

I shall be showing you how to develop a stored procedure using the Development Center.

1.      Click on Start ->IBM DB2 ->Development Tools -> Development Center.

2.      You will now be prompted to create a new Project. Give it a name; DevIQ Project and click on OK.

3.      Next, you will need to add a connection to DB2. To do this, right-click and say Add Connection. Specify your Connection Settings in the Dialog and click OK.

4.      We have established our connection with DB2! We can now create Stored Procedures, User Defined Functions and Triggers within the Development Center.

a.      Through the Editor by typing the code by hand; and

b.      Wizard is the next option. We shall be using the Wizard Option for this article.

6.      Now right-click on Stored Procedures and select Create Stored Procedure by Wizard option.

7.      You will now be presented with a Create Stored Procedure Wizard dialog. Please enter the following parameters.

a.      Name: This will be your stored procedure name. In our case, ANILM.GetEmp (Please replace ANILM with the Schema of your choice).

b.      Definition: On this screen, click on the ellipse of the Statement option. You will be presented with a screen that allows us to create our SQL statement(s), depending on your choice. Choose Generate one SQL statement and click on SQL Assist.

Once you click on SQL Assist, you will be presented with a Query Builder, just like SQL Server or MS Access.

Now, we need to first click on FROM(Source Tables). This option will let us choose the Schema.TableName from our Database. Choose your SchemaName.Employee.

Next, click on the WHERE CLAUSE and then select the Column Field:
Column: FirstNME
Operator: LIKE
Pattern: E

After that, click on the OR Button and perform the above steps for LastName as well, for the pattern choose S.

Click on run to test your query. Once satisfied, click OK.

The result of the above query would return all the employees whose First Name starts with E or Last Name starts with S.
Click OK for the next two times and you will return to the wizard back again.

So we have now built our query successfully.

c.      Parameters: Since we do not expect any parameters for this query, click on next to continue.

d.      Options: Under this screen, you will have the chance to enter a specific name for your stored procedure. In our case, let us name it GetEmp itself. There are two more options in this screen. They are build and Debug. The build option is by default checked, as this will build and create our stored procedure. Debug option is for those who wish to debug their stored procedures in the Development Center.

e.      Summary: The final screen displays a summary for all the work we did in the wizard's various dialogs. To know more about your stored procedure that is about to be created, click on the Show SQL button.

Click ‘Finish’ to complete the creation of our stored procedure.

8.      You now see that our stored procedure has been created.

9.      Right-click on the stored procedure and click on Run.

You will see the results as shown in figure 1.1.



To execute the above stored procedure in the Command Center, navigate to Start -> IBM DB2 -> Command Line Tools -> Command Center. In the command center, perform the following steps

1. Click on Add -> then Select the SAMPLE Database and Choose Use Implicit Credentials and click OK.

2. To execute the stored procedure, enter the following statements and click on Execute.
call GetEmp()

3. You will get the desired results as shown in the figure 1.2.




Fig. 1.2

Congratulations!!! You have created your very first stored procedure in DB2.

We now move on to User Defined Functions.

User Defined Functions

A user defined function (UDF) is an extension or addition to the existing built-in functions of SQL. You can register UDFs to a database in SYSCAT.FUNCTIONS using the CREATE FUNCTION statement.

With UDFs, DB2 allows you to extend the function of the database system by adding function definitions to be applied in the database engine. By adding function to the engine, you can save the effort of retrieving rows from the database and applying similar functions on the retrieved data. UDFs let the database exploit the same engine functions that are used by applications. They provide more synergy between an application and the database. They also contribute to higher productivity for application developers because they encourage code reuse.

With the Development Center, you can create scalar and table UDFs. A scalar function returns a single value each time it is called. A table function returns a table.

You can also use wizards to create special UDFs that work with the following data types or sources:
· WebSphere MQ message queues;
· OLE DB data providers; and
· XML documents.

The Development Center is designed to give you a full development environment for working with UDFs, from creating new UDFs through to deploying UDFs to a production server.

Implementing User Defined Functions

Now we shall create a UDF using the Development Center to get the List of Employees whose Salary is greater than 25000.

To get the above output, perform the same steps as done with the Stored Procedure Wizard; except that choose User Defined Function and the type is SQL User Defined Function.

Specify the following parameters for the creation of our UDF.

1. For the name of our UDF: ANILM.GetEmpSalary (replace ANILM with your Schema).
2. The next section will include – Statement: SQL Statement and Output Type: Scalar.

Click on the Ellipse of the SQL Statement and perform the same steps as discussed in the stored procedure section.

The only difference would be the Query.

Just copy and paste this Query in the Window:

Replace ANILM with your Schema.

3. You then specify a return type, in this case, we need to return the count, and so it will be an INTEGER.
4. No parameters required here.
5. Specific Name: GetEmpSalary. And all UDFs get built by default.
6. Finally, the Summary. Click ‘Finish’ and you will see our new UDF created as shown in figure 1.3.



Fig. 1.3

Now right-click on the UDF and select Run. You will get the results as shown in figure 1.4.



Our UDF returns 18 as the number of Employees who draw a Salary of more than 25000.

Finally, if you are curious on how to run an UDF in the Command Center, type the following statements in the command center window:

Select DISTINCT ANILM.GetEmpSalary() as "Salary > 25000" from Employee

Congratulations again for having made it this far.

We now move on to our final section, Triggers!!! Yahoo!!! J

Triggers

A trigger defines a set of actions that are performed when a specified SQL operation (such as a delete, insert or update) occurs on a specified table. When the specified SQL operation occurs, the trigger is activated and starts the defined actions.

You can use triggers with referential constraints and check constraints to enforce data integrity rules. Triggers are more powerful than constraints because they can also be used to update other tables, automatically generate or transform values for inserted or updated rows, or invoke functions that perform operations both inside and outside of DB2. For example, instead of preventing an update to a column if the new value exceeds a certain amount, a trigger can substitute a valid value and send a notice to an administrator about the invalid update. 

Triggers can be used for defining and enforcing business rules that involve different states of the data, for example, limiting a salary increase to 10%. Such a limit requires comparing the value of a salary before and after an increase. For rules that do not involve more than one state of the data, consider using referential and check constraints. 

You can use triggers to move the application logic that is required to enforce business rules into the database, which can result in faster application development and easier maintenance. With the logic in the database, such as the limit on increases to the salary column of a table, DB2 checks the validity of the changes that any application makes to the salary column. In addition, the application programs do not need to be changed when the logic changes.

Types of Triggers

1. INSTEAD OF triggers: describe how to perform insert, update and delete operations against views that are too complex to support these operations natively.
2. BEFORE Triggers: are fired before any table data is affected by the triggering SQL statements.
3. AFTER Triggers: are fired after the triggering SQL statements.

Implementing triggers
To create a trigger from the Control Center, use the Create Trigger dialog.

We shall create a table for this part and implement triggers. Create a new table via the control center and give it a name as ANILM.Recruits.

(Change your Schema and create the table)

The columns would be Candidate_Name , Age, Email, City.

We shall create our table and whenever a new record has been inserted, we shall fire our trigger.

Just type the following in your command center and execute it.

CREATE TABLE "ANILM "."RECRUITS" (

"CANDIDATE_NAME" VARCHAR(50) NOT NULL , 

"AGE" CHAR(2) NOT NULL , 

"EMAIL" VARCHAR(255) NOT NULL , 

"CITY" VARCHAR(50) NOT NULL ) 

IN "USERSPACE1" ; 

COMMENT ON TABLE "ANILM "."RECRUITS" IS 'NEW RECRUITS TABLE';

Replace ANILM with your Schema

Our Trigger that is being created is given below:

The Trigger name is TRGGETROWCOUNT.

CREATE TRIGGER ANILM.TRGGETROWCOUNT AFTER INSERT ON ANILM.RECRUITS 

REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL 

BEGIN ATOMIC

SELECT DISTINCT COUNT(*) AS "Total Recruits"

FROM ANILM.RECRUITS AS RECRUITS;

END

You may run it in the command center and execute it.

You will find it in the List of Triggers under the Database Objects Tree.

Wow!!! Wasn’t that FUN N EASY? DB2 has a lot more features to offer its wide user base and corporate customers.

There are a host of features to be covered in these three areas of Database Technology. Wait until next time, as I shall illustrate how to use these features in your application with ease.

I hope you have enjoyed learning DB2 Stored procedures, UDFs and Triggers with me as much as I have enjoyed writing about it.

Please do drop me a line and let me know your feedback on the article or anything in general with respect to databases.

Please feel free to mail me your comments and valuable inputs on how I can write better in DB2. And please mention in the Subject Line as “Article on Stored Procedures, UDFs and Triggers”.


IBM DB2 UDB Logo is Copyright of IBM (International Business Machines) Corporation, USA, and other countries where present.



The author can be contacted at: anilm001@gmail.com.