Basic Walkthrough DB2 Part-3

 

Basic Walkthrough DB2 Part-3

This section will help you walk through development of simplest database program (utility). I will make it to happen so smoothly towards the end of this part you will not even believe that the utility is ready for use.

Of course, intention to keep it simple, will prevent us from having Graphical User Interface (GUI); still the main purpose of looking closely at DB2 will be served and along with the knowledge (know how) there will be another part, which is called amusement while learning. If we have to enjoy the product (DB2) which is free, we must learn main features in smallest possible time; which is different person to person.

Once we succeed to setup simplest form of database, I promise I will introduce all to remote control unit with no additional expense (in fact REMOTE CONTROL  was developed to use existing components). This unit helps to retrieve data from your computer via email commands defined by you. This is advised that the data transferred via email is not encrypted; so please use only in utmost urgency. Still this unit will be nicest tool to learn and experiment with Database and System.

            So, let us start. As we tried few commands in previous part, I will prefer to take you along with me to a nice short development tour. Mostly I will use DB2 Command Line, which means that for purpose of understanding commands, I will be using DB2 DOS BOX. Don’t worry id you are not familiar with one or other term, because I will be showing snapshots to help you get used to.

First off all I would like to create a table with few columns. The definition is shown below. I will leave it up to you to see and guess which column is used for what data.

CREATE TABLE ADDRESS (                        FNAME CHAR(16) NOT NULL,                        MNAME CHAR(12),                        LNAME CHAR(16) NOT NULL,                        PHONE CHAR(15),                        CELL  CHAR(15),                        ADDR  CHAR(35)                       )

This will create the table in the current database that you are connected to. If you had not connected to any database so far; DB2 will prompt nicely :-

DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL1024N  A database connection does not exist.  SQLSTATE=08003

Should you have connected to your database it should have been as below.

DB20000I  The SQL command completed successfully.

Well, nothing discouraging so far, Connect to the database and re-issue  the Create Table command.

After creating table, I would emphasize on creating a primary key on First Name (FNAME) column. This will help us keep consistent data in our database. The key will be created as combination of two columns, FNAME and LNAME. Which basically help us search through the data when there are really big number of rows and will help us keep distinct data.

ALTER TABLE ADDRESS ADD CONSTRAINT My_FNAME_LNAME_PK PRIMARY KEY( FNAME, LNAME) DB20000I  The SQL command completed successfully.

So, if we create primary key as a combination of FNAME and LNAME, there can’t be two identical combinations. For example, you can add ‘Tai Tang’ first time, but in subsequent entries ‘Tai Tang’ will not be accepted.

After creating primary key, you will insert some data. You can do this manually because as a sample data of imaginary records, there will be only four rows for our hands on. So, when you will enter data at DB2 CLP, following will be scene.

insert into address values ( 'Tai','','Tang','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA') DB20000I  The SQL command completed successfully. insert into address values ( 'Sabar','','Raikoti','905-509-5099','419-914-9144','1032 Mavis Rd Mississauga,ON,CA') DB20000I  The SQL command completed successfully. insert into address values ( 'David','M.','Brown','905-264-2644','647-746-7466','1216 Morning Star Drive Miss,ON,CA') DB20000I  The SQL command completed successfully. insert into address values ( 'Dilshaad','Sufi','Akhtar','905-932-9322','416-417-4177','3456 Horner Ave Etobicoke,ON,CA') DB20000I  The SQL command completed successfully.

            If you want to check how primary key stops wrong data creep in try to enter same row as row #1 again.

 

insert into address values ( 'Tai','','Tang','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA') DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.ADDRESS" from having duplicate values for the index key. SQLSTATE=23505

This will prevent duplicate data and if you really need two similar names exist in the table you need to set primary key more widely. Try eliminating ‘a’ from ‘Tang’.

insert into address values ( 'Tai','','Tng','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA') DB20000I  The SQL command completed successfully.

 

Now it allows. The check is done only on participating key columns. Others can have duplicate data.

            For our Address Book utility this table is set up. Further we will see few commands to retrieve data. Try some simple SQL to retrieve data in ADDRESS Table:

Select Fname,Lname,Phone,Cell from address FNAME            LNAME            PHONE           CELL ---------------- ---------------- --------------- --------------- Tai              Tang             647-647-6477    416-614-6144 Sabar            Raikoti          905-509-5099    419-914-9144 David            Brown            905-264-2644    647-746-7466 Dilshaad         Akhtar           905-932-9322    416-417-4177 Tai              Tng              647-647-6477    416-614-6144         5 record(s) selected.

Opps, I wanted only David, he called me last night.

Select Fname,Lname,Phone,Cell from address where fname=’David’ FNAME            LNAME            PHONE           CELL ---------------- ---------------- --------------- --------------- David            Brown            905-264-2644    647-746-7466     1 record(s) selected.

So, it’s easy to select one out of many. While comparing strings you have to specify the correct case for all letters or you need to do comparison using some function as below.

Select Fname,Lname,Phone,Cell from address where ucase(fname)=’DAVID’ FNAME            LNAME            PHONE           CELL ---------------- ---------------- --------------- --------------- David            Brown            905-264-2644    647-746-7466     1 record(s) selected

Another situation, I think the gentleman called me three weeks ago; mm I can’t recall the name but phone number was 416-509…… may be 647-509 . . .. Well I remember middle three digits were 509 ... a  kind of… let me try

Select Fname,Lname,Phone,Cell from address where phone like (‘%509%’) FNAME            LNAME            PHONE           CELL ---------------- ---------------- --------------- --------------- Sabar            Raikoti          905-509-5099    419-914-9144     1 record(s) selected.

 

Oh, yeah, he was a nice guy. I gotta help him, let me call him.

There will be many situations; when you may remember one of the properties associated with a contact and then you can get hold of his/her number. Your friend’s friend once met with you and he was talking about some vacancies that are about to open at his workplace. He actually liked chat with you and had promised to recommend your name. They have a reputed setup and just yesterday you heard that S & R Software is hiring.  How will you get to him because your friend has gone back home for a long break?

insert into address values ('John','D','walker','419-932-9322','647-417-4879','promised help with JOB'); 1 row created.

 Oh yes, you could hardly remember that you had entered this fact in the database (Table). Your database was new and you were really interested to enter everything if you could. You only remember ‘promised’ word and nothing else. Let us try.

Select Fname,Lname,Phone,Cell from address where mname like (‘%promised%’) FNAME            LNAME            PHONE           CELL ---------------- ---------------- --------------- ---------------     0 record(s) selected.

Well don’t despair. Give another try.

Select Fname,Lname,Phone,Cell from address where addr like (‘%promised%’)   FNAME            LNAME            PHONE           CELL ---------------- ---------------- --------------- --------------- John             walker           419-932-9322    647-417-4879     1 record(s) selected.

You were lucky, because DB2 had helped? No; lucky, because you had entered something in your table. Now you have his name and go ahead call him, he liked chat with you.

            The best thing about database (or data table) is that once you create it, you can keep adding new contact for ever. There are simple setups to double check that the data that you are entering is valid. I mean no mistake or error. This is done at database level and you do not need to develop the software. Triggers and some constraints will help us do some sanity checks. We will talk about triggers and other constraints in next part.

            Actually, for our walk through the table ADDRESS is ready. But I believe that, because we have a hi-fi database system, why not try some house-keeping kind of things. I mean few features that allow us to maintain our database in top position. We are ok for now and will discuss Triggers and some of constraints in next part.