Basic Walkthrough DB2 Part-4

 

Basic Walkthrough DB2 Part-4

In previous part we had created the database (single table database) and populated the table with some data. The data seemed all right and we had nice time retrieving the data. Except one that (friend of friend)’s last name begins with small ‘w’. We will correct this and move on to creating triggers and other stuff.

Let us try updating the last name with capital ‘W’. We will see contents and update the row based on some unique parameters. Which means that while updating if our predicate selects more than one row, all selected rows will be updated. So be careful while identifying the target row.

Update ADDRESS SET LNAME=’Walker’ Where PHONE=’ 419-932-9322’ DB20000I  The SQL command completed successfully.

Thank God. The correction was cool.

To have al look at the modified data issue the command : SELECT * FROM ADDRESS. The * basically means all columns.

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from address   FNAME            MNAME        LNAME            PHONE           CELL            ADDR ---------------- ------------ ---------------- --------------- --------------- ----------------------------------- Tai                           Tang             647-647-6477    416-614-6144    64 Pluto Way Brampton,ON,CA Sabar                         Raikoti          905-509-5099    419-914-9144    1032 Mavis Rd Mississauga,ON,CA David            M.           Brown            905-264-2644    647-746-7466    1216 Morning Star Drive Miss,ON,CA Dilshaad         Sufi         Akhtar           905-932-9322    416-417-4177    3456 Horner Ave Etobicoke,ON,CA Tai                           Tng              647-647-6477    416-614-6144    64 Pluto Way Brampton,ON,CA John             D            Walker           419-932-9322    647-417-4879    promised help with JOB     6 record(s) selected.     C:\Program Files\IBM\SQLLIB\BIN>

When you look at above output you will find last name corrected. Secondly you see word ‘promised’ in ADDR column. That means you are free to insert any text in ADDR column. There is no CONSTRAINT set up. Usually there are no CONSTRAINTS in text fields like address.

There was an extra entry for Tai, with wrong last name (Tng). We did that intentionally to test behavior of primary key. Let us delete it.

Delete from ADDRESS where lname='Tng' DB20000I  The SQL command completed successfully.   Select fname,lname,addr from address   FNAME            LNAME            ADDR ---------------- ---------------- ----------------------------------- Tai              Tang             64 Pluto Way Brampton,ON,CA Sabar            Raikoti          1032 Mavis Rd Mississauga,ON,CA David            Brown            1216 Morning Star Drive Miss,ON,CA Dilshaad         Akhtar           3456 Horner Ave Etobicoke,ON,CA John             Walker           promised help with JOB     5 record(s) selected.

So, we have seen how to remove ROWS from a table. A word of caution. Do not use DELETE FROM ADDRESS. This will delete all rows. This command should always have where predicate to limit the rows being deleted.

The most liked part of database is input format routines. The user will enter the data and smart systems accept a vide variety of data in loose format and will try its best to format it strict before inserting into table. This seems that this is part of programming. Well, yes, but basic programming that can be handled in small triggers. So I will help you write trigger to adjust hyphens (‘-‘) in the input data when it is fed to PHONE and CELL columns. Please go over DB2 information center for triggers’ definition and syntax for more detail.

Next step will be to create a trigger to validate the data being input into the database table.

Create a text file with any extension having following SQL code in it. Do not worry about everything for now. Just create the trigger definition file and get it created. This trigger will check for existence of hyphens in appropriate position in phone numbers.

CREATE TRIGGER INS_CELL_PHONE  BEFORE INSERT ON ADDRESS  REFERENCING NEW AS N  FOR EACH ROW  MODE DB2SQL  WHEN (SUBSTR(N.PHONE,4,1)<>'-' OR SUBSTR(N.PHONE,8,1)<>'-'         OR SUBSTR(N.CELL,4,1)<>'-' OR SUBSTR(N.CELL,8,1)<>'-' )            SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Phone Numbers Expected as 999-999-999'       @

This will create a trigger which will look far hyphens (‘-‘) at specified locations.  If any of hyphens are missing, trigger will raise an error with message as specified as MESSAGE_TEXT.

            To create the trigger, I suppose you have stored the file as INS_PHONE.TRIG in current folder. See the syntax and note that we had ended the file with @. So calling this SQL file we specify the terminator (@) as below.

C:\Program Files\IBM\SQLLIB\BIN>db2 -td@ -vf ins_phone.trig   DB20000I  The SQL command completed successfully.     C:\Program Files\IBM\SQLLIB\BIN>

            Please note that to run SQL files you have to run it from DB2 Command Line and not DB2 CLP These two windows are different although they look the same. If you have started CLP then you may revert back to Command Line by typing QUIT. So now trigger has been created which checks for ‘-‘  at 4^th and 8^th position in PHONE and CELL. If there is any one missing, trigger will force rejection of whole row and nothing will be added.

 

insert into address values ( 'Gurmit' ,'S', 'Randhawa', '416-742-9242','6477204020','1230 The Walkers  Road') DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0438N  Application raised error with diagnostic text: "Phone Numbers Expected as 999-999-999".  SQLSTATE=75000   D:\SGILL\DBA\PROJ>

Let us try little different. This time we won’t provide the CELL number.

insert into address values ( 'Gurmit', 'S', 'Randhawa', '416-742-9242','','1230 The Walkers Road') DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0438N  Application raised error with diagnostic text: "Phone Numbers Expected as 999-999-999".  SQLSTATE=75000

Same, because trigger wants two hyphens in both numbers. So now you are forced tom enter the phone numbers exactly as 999-999-9999. By the way, we have designed the trigger just for practice, so we kept it simple; and you can provide any bad data except ‘-‘ on 4^th and 8^th places in phone numbers.

Now try providing the data in strict format.

insert into address values ( 'Gurmit' ,'S' ,'Randhawa' ,'416-742-9242','647-720-4020','1230 The Walkers Road') DB20000I  The SQL command completed successfully.

Cool, this works, and is much better because the chances of omission are ruled out this way.

            Triggers are not primarily for this purpose. However, being a database object, it works faster and ensures data integrity; these triggers can be put to different uses. Normally triggers are used to make calculations based on some columns and populate the other columns with the result. More sophisticated use can be thought of updating other tables based on transaction data entered into main table (usually called TRANSACTION table).

            As a last section of this walkthrough part I would like to mention here that entering all data on command line is usually not welcome. But the good thing is that if you do it manually you get more familiar with your database objects; i.e. tables, views, triggers and sequences etc.

            Still we do not have to go long way. If you so wish, you can use the batch file listed below. This one will allow you to enter data into your table. I will keep it simple for this tutorial.

@echo off :another set /p fname=First Name : if [%fname%]==[] goto done set /p mname=Middle Name : set /p lname=Last Name : set /p phone=Phone Number : set /p cell=Cell Number : set /p addr=Address : set /p ok=      Ok ? if [%ok%]==[y] goto doit if [%ok%]==[Y] goto doit if [%ok%]==[Yes] goto doit if [%ok%]==[yes] goto doit if [%ok%]==[YES] goto doit goto redo :doit db2 insert into address values ('%fname%','%mname%','%lname%','%phone%','%cell%','%addr%') echo. :redo set fname= set mname= set lname= set phone= set cell= set addr= set ok= goto another :done set fname= set mname= set lname= set phone= set cell= set addr= set ok=

Please take care saving this script. This will need .bat or .cmd filename extension. Go ahead and practice with data entry. Remember when you want to finish, enter nothing in First Name: If you miss it then say no to ‘Ok?’ prompt and then give it empty First Name again. This batch file will keep looping until you provide empty (NULL) for First Name. For each entry you will be expecting following string from DB2. DB2 confirms the successful entry this way. Anything else will signal an error.

DB20000I  The SQL command completed successfully.

If you see an error then you need to repeat the entry. Keep in mind that you have a trigger in action to check your phone numbers. That should be good enough for this part of tutorial. In next part, we will see how we can build a simple application to retrieve data from our database remotely.