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

How to Install IBM Data Studio

This is the step by step to install IBM data studio
1. Open drive:\Installer\DB installer2_Install\IBM Data Studio\Windows
2. Extract IBM_data_studio_v111_win file
3. Click setup.exe.
4. Choose Install IBM Data Studio.
5. Tick all checkbox then click next.
6. Choose I accept the aggrement then click next
7. Choose create a new package group then choose your preferred path, then click next
8. If you have alreday had Eclipse, try yo use Extend and existing Eclipse.
9. Choose your preferred language.
10. Choose feature that you want to be installed.
11. IBM instalation manager will install IBM data studio to your computer, just sit relax
12. Finish and you can use IBM data studio right now.

Best regards,

Deny Sutani

DB2 Product Overview

DB2 is a DBMS made by IBM. It was released for the first time at 1983 in MVS. At 1996, IBm released DB2 UDB v5. At this version, DB2 has a feature to support saving many data type like audio, video and text

DB2 9 is the lateset release. In this version, like the previous, can run on many platform like AIX, HP-UK, Linux, Solaris, Windows, i5/OS and z/OS. In this version, DB2 has many type as you can see below.
The bigger the number, it become more expensive, more stable, and has more feature.

1. DB2 Everyplace
This version is the smallest DB2, it's size only 350KB. This version made for handheld user like PDA or smartphone.

2. DB2 Express
This version is an entry level for data server which is designed for
a computer with up to 2 CPU and 4GB memory and run on Linux platform. It has a few important feature such as
- pureXML : give an simple and efisien access to XML data at the same security and integrity with relational data.
- High Availability : give 24 x 7 reliability to DB2 data server. There 3 komponent in this feature, HADR, Online Reorganization, and IBM TSA MP.
- Workload Management : this feature monitoring query actively and manage it to run efisiently
- Performance Optimization : this feature combine 3 module, MQT, MDC and Query Parallelism
- DB2 Homogenous Federation Feature : this feature has ability to manage and access multiple data server

3. DB2 Express-C
This version is an entry level for data server which is designed for
a computer with up to 2 CPU and 4GB memory and run on Linux or Windows platform. This version can be used for evaluation and education freely. It has a few important feature such as
- Spatial Extender Client and samples
- Microsoft Cluster Server support
- Informix Data Source support
- Replication Data Capture
- DB2 Web Tools
- Global Secure Toolkit
- APPC and NetBios support

4. DB2 Personal Edition
This is the DBMS for single user on dekstop or notebook. It can be used to create, modify, and manage many local database.

5. DB2 Workgroup Server Edition
This version is the DBMS for multi user, client/ server which is designed for a computer with up to 4 CPU, 16GB memory and run on Linux, Windows, Solaris, Linux, AIX platform. This has the same feature with DB2 Express, but it has more scalability.

6. DB2 Enterprise Server Edition
This version is the DBMS for multi user, web enabled client/ server which is can hold huge transaction, multi terabyte data warehouse. It can run on server which has up to hundreds CPU running on Linux, Windows, Solaris, Linux, AIX platform. It has the same feature with DB2 workgroup with additional feature such as
• High Availability Disaster Recovery (HADR)
• Table (range) partitioning
• Online reorganization
• Materialized Query Tables
• Multi-dimensional data clustering
• Full intra-query parallelism
• Connection Concentrator
• The DB2 Governor
• Tivoli System Automation for Multiplatforms (TSA MP)

7. DB2 Data Warehouse Edition
This version can be used to make dynamic data warehouse. This is a combination between DB2 enterprise server and DB2 data partitioning.

8. DB2 Personal Developer’s Edition
This version can be used by application developer to make an application which interact with DB2 personal edition. In this version, developer can make application which connect to DB2 by using SQL, SQLJ, .NET or JDBC method.

There are another version of DB2 such as DB2 universal Developer, DB2 enterprise developer, DB2 for i5/OS and DB2 for z/OS. These version have more scalability and feature.

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

How to Connect Java to DB2

It's not difficult to connect java application to DB2. It's almost the same like connect java to other DBMS such as access, SQL server and so on.

This is the snippet code.

String DB_URL = “jdbc:db2:SAMPLE”;
String userid = “db2admin”;
String password = “mypasswd”;

Connection connection = null;

Properties connectProperties = new Properties();
connectProperties.put(”user”, userid);
connectProperties.put(”password”, password);

Class.forName(”com.ibm.db2.jcc.DB2Driver”).newInstance();
connection = DriverManager.getConnection(DB_URL, connectProperties);
PreparedStatement pstmt = connection.prepareStatement(”SELECT * FROM employee”);
ResultSet rs = pstmt.executeQuery();

Best regards,

Deny Sutani