Using Cursor at DB2

After Stored Procedure, we will learn about how to use Cursor. Output with Cursor or without Cursor will be the same. The differences is just a the process. With Cursor we lock at 1 row only, without cursor we will lock all record.

The advantage of using Cursor
- only lock 1 row
- there are an operation that must use Cursor

The disadvantage of using Cursor
- it become more complex

For example if we want to make a Stored Procedure to update salary depends on job. There are 2 ways that we can do, Stored Procedure with an without Cursor.

This is the Stored Procedure without Cursor, the easier way.

------------------------------------------------------------
create procedure proc_2(in bonus double,in work varchar(10))
dynamic result sets 1
p1: begin
update staff set salary = salary + bonus where job= work;
end p1
------------------------------------------------------------

This is the screen shoot



This is the Stored Procedure without Cursor, more complicated query.

------------------------------------------------------------
create procedure proc_1(in bonus double,in work varchar(10))
dynamic result sets 1
p1: begin
declare now_salary double;
declare c1 cursor with return for
select salary from staff where job=work;
open c1;
fetch from c1 into now_salary;
close c1;
set now_salary = now_salary + bonus;
update staff set salary = now_salary where job= work;
end p1
------------------------------------------------------------

This is the screen shoot


You can run this Stored Procedure at DB2cmd. Just write this command.
"db2 call proc_2(100.0,'Mgr')", but don't forget to connect to the DBMS first.

Make Stored Procedure at DB2

To make a Stored Procedure with IBM Data Studio, you can follow this step by step tutorial
1. Open IBM Data Studio
2. Choose file -> new -> data development project
3. Give a project name, for example Project2
4. Choose "Use an existing connection". Click Finish
5. At Data Project Explorer will show Project2
6. Right click at Proejct2 -> new -> Stored Procedure
7. Give a Stored Procedure name, for example PROC_3. Choose next
8. At statement detail, change with the query that you want. Click next
9. Click next
10. Choose the specific name. This is an alias name for Stored Procedure.
11. Click Finish

This is the query generated by IBM Data Studio
CREATE PROCEDURE PROC_3 ( )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT *
FROM EMPLOYEE;

-- Cursor left open for client application
OPEN cursor1;
END P1


If you want to run it. First right click the Stored Procedure, then choose Deploy, after that choose run. You can see the output at the right below corner.

This is the screenshot



Or you can run it from DB2cmd. Just write this command.
"db2 connect to sample" then "db2 call proc_3()"

Best regards,

Deny Sutani

UNION and UNION ALL at DB2

Union at DB2 used to join 2 or more resultset from a query. For example if we want to see list of staff who work at IT department and FA department.

This is the syntax
select column1, column2
from table_name
where….
UNION
select column1, column2
from table_name
where….

Each column1 and column2 must be match with other resultset, or it will generate an error.

This is an simple example how to using UNION in Stored Procedure

CREATE PROCEDURE SP_PAKE_UNION ( )
DYNAMIC RESULT SETS 1
————————————————————————
– SQL Stored Procedure
————————————————————————
P1: BEGIN
– Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
select a.firstnme, b.deptname
FROM EMPLOYEE a, DEPT b
where a.workdept = b.deptno
and a.workdept =’E21′
union
select a.firstnme, b.deptname
FROM EMPLOYEE a, DEPT b
where a.workdept = b.deptno
and a.workdept =’A00′;
– Cursor left open for client application
OPEN cursor1;
END P1

That Store Procedure will generate list of staff who work at 'A00' and 'E21' department.

UNION is differ from UNION ALL. UNION just list the DISTINCT output. It will remove any record which is redundant. If you want to see all output, just change UNION with UNION ALL

Best regards,

Deny Sutani