Monday, 4 June 2012

Validating a successful DB2 UDB installation

Hot on the heels of my last post - Following the script - installing IBM DB2 UDB using a response file ( silent installation ) - I thought I'd run through the steps to validate my installation: -

Check product levels

$ /opt/ibm/db2/V9.7/bin/db2level

DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23034", and Fix Pack
"1".
Product is installed at "/opt/ibm/db2/V9.7".


List instances

$ /opt/ibm/db2/V9.7/bin/db2ilist

db2inst1

Log in as the instance owner

$ su - db2inst1

Create a database

$ db2 create db test

SQL0970N  The system attempted to write to a read-only file.  SQLSTATE=55009

This looks familiar -

SQL0970N when running DB2SAMPL on Unix

Check /tmp permissions

$ ls -al /

...
drwxr-xr-x.  23 root root  4096 Jun  4 20:26 tmp
...

Add global write permissions to /tmp, recursively

$ chmod -R a+w /tmp/

Check /tmp permissions

$ ls -al /

...
drwxrwxrwx.  23 root root  4096 Jun  4 20:26 tmp
...

Create the database again

$ db2 create db test

DB20000I  The CREATE DATABASE command completed successfully.

Connect to our new database

$ db2 connect to test

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.1
 SQL authorization ID   = DB2INST1
 Local database alias   = TEST


Create a table

$ db2 "create table loanrequests(name varchar(30) not null,custnum varchar(10) not null primary key,amount float not null)"

DB20000I  The SQL command completed successfully.


List the tables

$ db2 list tables

Table/View                      Schema          Type  Creation time            
------------------------------- --------------- ----- --------------------------
LOANREQUESTS                    DB2INST1        T     2012-06-04-20.44.44.189699

  1 record(s) selected.


Describe the table

$ db2 "describe select * from db2inst1.loanrequests"

 Column Information

 Number of columns: 3

 SQL type              Type length  Column name                     Name length
 --------------------  -----------  ------------------------------  -----------
 448   VARCHAR                  30  NAME                                      4
 448   VARCHAR                  10  CUSTNUM                                   7
 480   DOUBLE                    8  AMOUNT                                    6


Insert some data

$ db2 "insert into db2inst1.loanrequests values('Dave Hay','006734',12345.67)"

DB20000I  The SQL command completed successfully.

$ db2
"insert into db2inst1.loanrequests values('Homer Simpson','123456',456.78)"

DB20000I  The SQL command completed successfully.

$ db2
"insert into db2inst1.loanrequests values('Marge Simpson','661222',123.42)"

DB20000I  The SQL command completed successfully.

$ db2
"insert into db2inst1.loanrequests values('Lisa Simpson','123123',21323.23)"

DB20000I  The SQL command completed successfully.

$ db2 "insert into loanrequests values('Bart Simpson','43215',6651.21)"

DB20000I  The SQL command completed successfully.

Query the table

$ db2 "select * from db2inst1.loanrequests"

NAME                           CUSTNUM    AMOUNT                 
------------------------------ ---------- ------------------------
Dave Hay                       006734       +1.23456700000000E+004
Homer Simpson                  123456       +4.56780000000000E+002
Marge Simpson                  661222       +1.23420000000000E+002
Lisa Simpson                   123123       +2.13232300000000E+004
Bart Simpson                   43215        +6.65121000000000E+003

  5 record(s) selected.


Looking good :-)

No comments: