Basic Walkthrough DB2 Part-2

 

Basic Walkthrough DB2 Part-2

This part will provide simple introduction to DB2. Some might have some experience with relational database systems, while others might not have even thought about it. In simplest form RDBS (relational data base systems) is a database package that manages relational data. Now relational data is all about properties of an object that needs to be related via any other properties. Suppose we talk about a person. A person has name, last name, phones and may have vehicles. The person may have an income. The person may have expenses. The person may have choices and options. So, all the properties are linked to each other via a person. This way or that way, whatever is linked via another data is usually called relational data. The database that provides the relational data interlink (interface) is usually called relational database.

            So DB2 is a Relational Database. Like other databases it also provides few database objects that help us save, organize and retrieve data. Other databases provide some other features and may to some length, while DB2 has a reputation of providing a reliable database system that can easily be handled. The best part is that now DB2 Express-C server is free to use an is highly suitable for new learners and matured developers. Let us discuss about some of DB2 objects.

Tables

            Tables are the first one that I can think of. And mostly used right from simplest database to most complicated system. I our walkthrough we will be dealing with one simple table that will be very very simple to understand and you will never need to ask the question “what is a table??” Definition wise a table is a simple collection of data in rows and columns. The first table I ever came across was a dBase table that the dBase guys used to call ‘database’ instead of ‘table’.

Views

            Second object is usually views. The views are actually a logical combination of columns from different tables. These columns usually linked via a link id. Link id is normally a common column among related tables to indicate the object that these columns should relate. From user’s perspective the views are same as tables (not in dBase). You use same command to retrieve data from views as from tables. While we will be using simplest form of DB application; with one table only; we won’t be dealing with views a lot except simple usage example towards the end of series.

Triggers

            Triggers are another object that play a vital role in database life. As the name the triggers get triggered when an event is sensed. Database manager will execute the code in trigger body to perform some actions described in the trigger when an event described in trigger header occurs. Normally triggers are called when insert, update or delete is performed on a table. So, the triggers are created on tables.

Procedures

            Procedures are another part that plays an important role in data manipulation.  These are similar to procedures, sub routines and functions. These procedures are written and when we create these; database compiles and stores on server machine. These are later called to do actions as preplanned in the Procedure Body.

Packages

            Packages are little bigger than procedures. These are almost same as procedures. These packages are also compiled and later bound to the database. Any errors are usually phased out in compile time.

Sequences

            Sequences are some numerical series created by database based on criteria provided by the creator. This helps numbering the data being entered into the database automatically.

            In fact, for introductory walkthrough, you do not need to remember all these. More you learn, more will you discover the objects. All objects re for some purpose; and if you are so simple, you may not need to learn all. But they are there, just in case you need these any time.

Let us start exploring some of these. We will create few basic tables to study behavior of some of objects. Tables will be created quickly and we will study other objects briefly.

To start with let us take a look at ways we can issue commands to DB2. First method is to use DB2 Command Window. Go to start>All Programs>IBM DB2 >DB2COPY1(default)>Command line Tools>Command Window. Command window is similar to DOS box except that it initializes environment for DB2 commands. DB2 commands being entered at command line are usually preceded by ‘db2’ keyword. While the same command can be executed in DB2 CLP window without ‘db2’ keyword.

 

You can connect to SAMPLE database using db2 connect to sample.

If you enter db2 without any command; this DB2 command Line will change to DB2 CLP.

 

This can also be invoked from Start menu, same as previous one. Type Quit to revert back to DB2 Command Line.

            These two methods for executing commands are most commonly used by programmers. For even simpler GUI interface you can start Command Editor from the same place where you found command line tool. Alternatively, you can execute db2ce command to start it.

 

            On Target drop down, if no database in drop down,  hit Add... button to select the database.

 

Select SAMPLE and hit OK.

 

 

So simple, isn’t it ?  Go ahead and type a query plus a semicolon (‘;’).

 

 

The rows have been displayed, but these are on Query Results tab.

 

Well for now let us use db2 command window for creating tables view and triggers. So, close Command Editor and open Command Window (db2cmd) . Execute following commands one by one.

db2 connect to sample db2 CREATE TABLE TRANS ( ID SMALLINT  NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1, NO CACHE ) , AMOUNT DECIMAL (6, 2)  NOT NULL , CODE CHARACTER (6)   ) db2 CREATE TABLE TOTAL ( WEEKNUM SMALLINT  NOT NULL  WITH DEFAULT 0, INCOME DECIMAL (6, 2)  NOT NULL  WITH DEFAULT 0 , EXPENSE DECIMAL (6, 2)  NOT NULL  WITH DEFAULT 0   , TIMEUPDATE TIMESTAMP  NOT NULL  WITH DEFAULT current timestamp)

 

After creating these two tables, you will create a trigger that will update your TOTAL table. See first line for syntax to create trigger when you name the code file as trig1.sql.

 

db2 -td@ -f trig1.sql

create trigger update_total after insert on trans referencing new as n for each row mode db2sql when (n.amount<>0) begin atomic if exists (select 1 from total where weeknum=week(current timestamp))   then       if lcase(n.code)='in' then update total set income= income + n.amount, timeupdate=current timestamp where weeknum=week(current timestamp) ; end if;       if lcase(n.code)='out' then update total set expense= expense + n.amount, timeupdate=current timestamp where weeknum=week(current timestamp);end if ;   else       if lcase(n.code)='in' then insert into total values(week(current timestamp),n.amount,0,current timestamp);end if ;       if lcase(n.code)='out' then insert into total values(week(current timestamp),0,n.amount,current timestamp);end if ;   end if;   end @

Only thing to note here is that the triggers can be created in Command Window not CLP. You can create it in Command Editor, but for that you will have to change terminator character to ‘@’ instead of ‘;’ (look bottom left of CE).

            After creating these three objects try entering data into TRANS table as below. You will see that TOTAL table will be populated automatically. This is feature of the database; not the program. The TOTAL table will create one row for each week. When week changes it will generate another row. This table will reflect weekly income and expenses. On regular basis you will enter all transactions into TRANS table as below. Remember to enter code as either ‘in’ for income or ‘out’ for expenses. Any other code will be accepted but will be useless.

db2 insert into TRANS values ( default,123.45,’in’) db2 insert into TRANS values ( default,34.40,’out’) db2 insert into TRANS values ( default,100,’in’) db2 insert into TRANS values ( default,15,’out’) db2 insert into TRANS values ( default,.75,’in’)

            And lastly check contents of TOTAL table

 

select * from total   WEEKNUM INCOME   EXPENSE  TIMEUPDATE ------- -------- -------- --------------------------      22   224.20    49.40 2009-05-28-16.23.42.000000     1 record(s) selected.

Cool, get the calculator and see if the calculation is correct. Last update stamp only shows the last time when the data was entered.

            In fact, we have created a very useful utility. This can further be customized as required. You will see how triggers can help us validate data while data entry in following sections. Till then happy DB2ing.