Basic Walkthrough DB2 Part-5

 

Basic Walkthrough DB2 Part-5

In this part we will study some more useful database objects. For example VIEWS, TRIGGERS, PROCEDURES and few other.

As we had an introduction to trigger creation in part 2, we will discuss how we run scripts first. DB2 supports different kind of scripts. You can run system shell scripts (.bat, .cmd, .js, .vbs,.sh and many more).  Let us try to understand how we can run scripts. Simple SQL script looks like

 

C:\Program Files\IBM\SQLLIB\BIN>db2 –t -f script.sql

connect to sample; select id,name,dept,job from staff; select count(*) from staff; connect reset;

This was a simple SQL script. This is to be run in db2 command window. The syntax has been indicated in blue. Being an example script, I just listed few commands. In actual scripts these lines are usually over hundreds. Scripts can also be invoked in verbose mode (as echo on in DOS).

C:\Program Files\IBM\SQLLIB\BIN>db2 –t –v -f script.sql

Note that while using db2 the options can be grouped. As an example the command used above can also be written as

C:\Program Files\IBM\SQLLIB\BIN>db2 –tvf script.sql

            Similarly, we can use system scripts too for accomplishing same mission. Have a look at following script. This one is equivalent to the previous one.

C:\Program Files\IBM\SQLLIB\BIN>Script.bat

db2 connect to sample db2 select id,name,dept,job from staff db2 select count(*) from staff db2 connect reset

This is very straightforward that we use db2 keyword and removed the terminator. The scripts are usually written for various tasks including generating daily reports. As a rule of thumb, add all the commands to script file that are to be repeated in future and invoke the script using one of above syntaxes.

Let us create another trigger on TRANS table (created in part 2) to ensure that user uses in or out keywords only in code column. Using anything else will be waste of data. So, we will try to reject entry if the code is not set properly.

C:\Program Files\IBM\SQLLIB\BIN>db2 –td@ -f TriggerCheckCode.sql

CREATE TRIGGER Check_Code  BEFORE INSERT ON TRANS  REFERENCING NEW AS N  FOR EACH ROW  MODE DB2SQL  WHEN (ucase(N.CODE)<>'IN' AND ucase(N.CODE)<>’OUT’)            SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Code must be either ”in” or “out”’       @

This trigger will stop us from entering wrong ‘Code’ in TRANS table. This is to ensure that data entered will be usable by the trigger update total trigger to update TOTAL tables.

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from total

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

C:\Program Files\IBM\SQLLIB\BIN>db2 insert into TRANS values (default, 120.00,'Not IN')

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: "Code must be either "in" or "out" ".  SQLSTATE=75000

C:\Program Files\IBM\SQLLIB\BIN>db2 insert into TRANS values (default, 120.00,'In')

DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from total

WEEKNUM INCOME   EXPENSE  TIMEUPDATE ------- -------- -------- --------------------------      22   364.20    49.40 2009-05-29-10.43.32.000000     1 record(s) selected.

            Cool, the transaction was applied to TOTAL table when we entered the correct code. Remember we had used ‘case free’ iN and OuT key words. It will work with any case.

Let us try to have some flavor of VIEWS. As explained earlier the VIEW is combination of columns from different tables. We will create another table called PERSONS with names of all family members. Then we will create a view joining TRANS and PERSONS tables to save info about who had carried out the transaction. For this purpose, we will add a column PID to TRANS table.

C:\Program Files\IBM\SQLLIB\BIN>db2 alter table TRANS add column PID CHAR(2)

DB20000I  The SQL command completed successfully.

Now create table PERSONS as below.

C:\Program Files\IBM\SQLLIB\BIN>db2 create table PERSONS (PID CHAR(2) not null primary key, FNAME CHAR(16), LNAME CHAR(12))

DB20000I  The SQL command completed successfully.

Add some data to the table.

C:\Program Files\IBM\SQLLIB\BIN>db2 insert into PERSONS values (‘00’,’David’,’Boon’) DB20000I  The SQL command completed successfully. C:\Program Files\IBM\SQLLIB\BIN>db2 insert into PERSONS values (‘01’,’Marry’,’Boon’) DB20000I  The SQL command completed successfully. C:\Program Files\IBM\SQLLIB\BIN>db2 insert into PERSONS values (‘02’,’John’,’Boon’) DB20000I  The SQL command completed successfully.   C:\Program Files\IBM\SQLLIB\BIN>db2 select * from persons   PID FNAME            LNAME --- ---------------- ------------ 00  David            Boon 01  Marry            Boon 02  John             Boon     3 record(s) selected.

Now we need to enter these PID in TRANS table to indicate who carried the transactions out. Let’s do that.

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from TRANS   ID     AMOUNT   CODE   PID ------ -------- ------ ---      0    34.40 out    -      1   123.45 in     -      2   100.00 in     -      3     0.75 in     -      4    15.00 out    -      6   120.00 In     -     6 record(s) selected.     C:\Program Files\IBM\SQLLIB\BIN>db2 update TRANS set PID='00' where ID=0 DB20000I  The SQL command completed successfully.   C:\Program Files\IBM\SQLLIB\BIN>db2 update TRANS set PID='01' where ID=1 DB20000I  The SQL command completed successfully.   C:\Program Files\IBM\SQLLIB\BIN>db2 update TRANS set PID='00' where ID=2 DB20000I  The SQL command completed successfully.   C:\Program Files\IBM\SQLLIB\BIN>db2 update TRANS set PID='02' where ID=3 DB20000I  The SQL command completed successfully.   C:\Program Files\IBM\SQLLIB\BIN>db2 update TRANS set PID='02' where ID=4 DB20000I  The SQL command completed successfully.   C:\Program Files\IBM\SQLLIB\BIN>db2 update TRANS set PID='00' where ID=6 DB20000I  The SQL command completed successfully.   C:\Program Files\IBM\SQLLIB\BIN>db2 select * from TRANS   ID     AMOUNT   CODE   PID ------ -------- ------ ---      0    34.40 out    00      1   123.45 in     01      2   100.00 in     00      3     0.75 in     02      4    15.00 out    02      6   120.00 In     00     6 record(s) selected.     C:\Program Files\IBM\SQLLIB\BIN>

                        Now we can create a view name trans view to reflect name of the person instead of PID. PID is usually handy for use with managing multiple tables within database. Views are very handy and useful for creating end-user reports.     

C:\Program Files\IBM\SQLLIB\BIN>db2 create view trans_view as SELECT t.AMOUNT,t.CODE,p.FNAME,p.LNAME from TRANS t, PERSONS p where p.PID=t.PID DB20000I  The SQL command completed successfully.   C:\Program Files\IBM\SQLLIB\BIN>db2 select * from trans_view   AMOUNT   CODE   FNAME            LNAME -------- ------ ---------------- ------------    34.40 out    David            Boon   123.45 in     Marry            Boon   100.00 in     David            Boon     0.75 in     John             Boon    15.00 out    John             Boon   120.00 In     David            Boon     6 record(s) selected.

 

            So, we had an idea how Views work. In actual practice, the views are rarely so simple. But the logic behind a view is always same. JOIN tables and pretend to be one table. Please note that for simplicity of examples, we did not use TIMESTAMP column in TRANS table, which is mostly used to keep track of transaction along with some other more useful info.

            For simplest use of database system, we have already covered most objects. In actual usage I hardly need anything other than what we have covered so far. The other objects are for some more sophisticated actions. For example, PROCEDURES and FUNCTIONS are very useful but for the scope of this tutorial these will be covered in last parts.

            Let’s discuss some of useful built-in functions.

CHAR
The CHAR function returns a fixed-length character string representation of the argument.

DATE
The DATE function returns a date derived from a value.

HEX
The HEX function returns a hexadecimal representation of a value.

LCASE
The LCASE function returns a string in which all the characters have been converted to lowercase characters.

LEFT
The LEFT function returns a string that consists of the specified number of leftmost bytes of the specified string units.

LENGTH
The LENGTH function returns the length of a value.

LOCATE
The LOCATE function returns the position at which the first occurrence of an argument starts within another argument.

LTRIM
The LTRIM function removes blanks or hexadecimal zeros from the beginning of a string expression.

MAX
The MAX scalar function returns the maximum value in a set of values.

MIN
The MIN scalar function returns the minimum value in a set of values.

RIGHT
The RIGHT function returns a string that consists of the specified number of rightmost bytes or specified string unit from a string.

RPAD
The RPAD function returns a string that is padded on the right with blanks or a specified string a specified number of times.

RTRIM
The RTRIM function removes blanks or hexadecimal zeros from the end of a string expression.

SUBSTR
The SUBSTR function returns a substring of a string.

SUBSTRING
The SUBSTRING function returns a substring of a string.

TIME
The TIME function returns a time derived from a value.

TIMESTAMP
The TIMESTAMP function returns a timestamp derived from its argument or arguments.

Following are some connection dependant REGISTER VARIABLES.

 

CURRENT TIMESTAMP, CURRENT USER, CURRENT DATE, CURRENT TIME, CURRENT SCHEMA, CURRENT SERVER, CURRENT PATH

 

TRY > db2 values CURRENT . . . for these register variables. Or alternatively you may use    >db2 select CURRENT . . . from SYSIBM.SYSDUMMY1

 These are available upon connection.

            Detailed searchable information is always available online from IBM. The manuals are another source of good info if we have these handy for the time when we need.   

Link to online info center http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp