Stored Procedures,User Defined Functions
and Triggers in IBM DB2 8.2
(Level 200 Session)
By
Anil Mahadev
Database Technologist and DB2 User
Introduction:
For most of us working with stored procedures,user defined functions and triggers have been on the lighter side, from a developers 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 one as this is a going to be 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.
So lets get started !!! . We need to the having the following system and software requirements in place.
System Requirements:
A basic P4 Machine with 512 MB RAM and about 20 GB HDD, a pointing device,
A keyboard and a monitor.
Software Requirements
1) Windows 2000 and above.
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.
What are Stored Procedures?
Well stored procedures are a set of pre-compiled SQL statements that will enable users to run multiple statements in one go.
Features:
- Its is very fast and can save lot of memory from the database server.
- You can stored procedures
using
- And SQL procedures are very
easy to write and maintain as we shall see in the coming sections.
- Improved Efficiency
- Code Reuse
Next we shall take a look on
Implementing Stored Procedures.
In this section we shall learn on how to implement a SQL Stored Procedure.
You must keep in mind the following points while implementing SQL stored procedures.
- The stored procedure name.
- The Parameters being
passed(IN,OUT,INOUT).
- 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
-- We can enter our SQL statements here -- This is a comment in DB2
4 SELECT * from ANILM.EMPLOYEES;
5 END P1
Let me explain the above syntax
Line 1: Tells us on 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, that 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 used in the SAMPLE Database provided by DB2.
The code is given below
The Employee Sample Stored Procedure
To create a stored procedure in DB2 , there are two ways:
1) Via
the
2) Via
the
I shall be showing you on how to Develop a Stored Procedure
using the
1) Click
on Startà IBM DB2 à Development Tools à
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
now have our connection with DB2, we can now create Stored procedures,User
Defined Functions and Triggers within the
5) There
are two ways to create Application Objects in DB2 Development Center:
a) Through the Editor by typing the code by hand.
b) Through a Wizard is the next option. We shall be using the Wizard Option for
this article.
6) Now
Right Click on Stored Procedures and Click on 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) 1.Name: This will be your Stored Procedure name: In our case
ANILM.GetEmp ( Please replace ANILM with the Schema of your choice).
b) 2. Definition: On this screen , click on the ellipse of the Statement
option. You will be presented with a screen that will allow 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 to
choose the Schema.TableName from our Database. Choose your SchemaName.Employee.
Next click on the WHERE CLAUSE and Click on 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 on 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 2 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 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
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) Now you see 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 Fig 1.1
Figure 1.1
To execute the above stored procedure in the
Navigate to Start à IBM DB2 à
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) Now
to execute the stored procedure, enter the following statements and click on
Execute.
call GetEmp()
3) And
you will get the desired results as shown in the Figure 1.2.
Figure 1.2
Congratulations. You have created your very first Stored Procedure in DB2.
We now move on to User Defined Functions:
What are 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
Features and benefits of using UDFs
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. User-defined functions 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
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
- XML documents
The
Implementing User Defined Functions:
Now we
shall create a UDF using the
To get the above output. Please perform the same steps as for the Stored Procedure Wizard, Except, that choose User Defined Function and 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
SELECT COUNT(*) AS "Salary > 25000"
FROM ANILM.EMPLOYEE AS EMPLOYEE
WHERE EMPLOYEE.SALARY > 25000
Replace ANILM with your Schema.
3) You
then specify a return type, in this case, we need to return the count, 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 below.
Figure 1.3
Now right click on the UDF and Click on Run. You will get the Results as shown in Figure 1.4 below
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 a UDF in the
Select DISTINCT ANILM.GetEmpSalary() as "Salary > 25000" from Employee
Congratulations Again for having made it this far J.
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.
Benefits of using triggers:
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.
You can use triggers 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:
INSTEAD OF triggers describe how to perform insert, update, and
delete operations
against views that are
too complex to support these operations natively.
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:
Before Triggers are
fired before any table data is affected by the triggering SQL
statements.
3) AFTER Triggers:
After Triggers are fired after the triggering SQL statements.
Implementing triggers
To
create a trigger from the
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
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 3 areas of Database Technology. Wait until next time as I show on 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.
Anil Mahadev
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,