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