DB2 Guide V1.00

To DB2 Newbie

Author : Chen Ming(c36378)Email : chenm@huawei.comDept. : SEE

Date : 2005-10-29Version : 1.00

DB2 guide (Author: Chen Ming)1

Outline





Getting StartedGetting ConnectedSQL

Trouble Shooting

DB2 guide (Author: Chen Ming)2

Getting Started





Install the server

using the command line processor(CLP)create instance

setting up the environmentinstance administration

DB2 guide (Author: Chen Ming)3

Getting Started -Install the server



For Unix/Linux, use X-Window and just run

# ./db2setup



Or use ./db2_installand create instance manually

Register the product using db2licm

% db2licm -a /cdrom/db2/license/filename.lic



View the product license

% db2licm -l

DB2 guide (Author: Chen Ming)4

Getting Started -Install the server



Linux/Unix directory structure

Readme -Readme file, etc.

adm -System administrator and executable filesadsm -ADSTAR Distributed Storage Manager filesbin -Binary executable filesbnd -Bind files for utilitiescc -Control Center files

cfg -Default system configuration filesconv -Code page conversion table filesdoc -On-line Books

function -Default location for UDFs

function/unfenced Default location for unfenced UDFsinstall -Installation programinstance -Instance scriptsjava -Java programslib -Libraries

map -Map files for DB2 Connectmisc -DB2 tools and utilitiesmsg/$L -Message catalogs

samples -Sample programs, sample scripts

DB2 guide (Author: Chen Ming)5

Getting Started –Install client



Get the DB2 client runtime packetInstall the client runtime

# ./db2_install -p DB2.RTCL

Create instance user and group

# groupadd -g 9000 db2i

# useradd -g db2i -u 9001 -s /bin/csh -d /home/db2cli db2cli# mkdir /home/db2cli

# touch /home/db2cli/.login

# chown -R db2cli:db2i /home/db2cli



Create instance

# cd /opt/IBM/db2/V8.1/instance/# ./db2icrt db2cli



Catalog database(shift user to db2 instance user)

db2 => CATALOG TCPIP NODE ose147 REMOTE osedb SERVER 50001 WITH \"DB2 node at osedb\"

db2 => CATALOG DATABASE cm AS cm AT NODE ose147 WITH \"Database cm at ose147\"

DB2 guide (Author: Chen Ming)6

Getting Started -Using the CLP

Run the Command Line Processor -`db2’ db2 =>Usage:

db2 ?displays a list of all DB2 commands

db2 ? commanddisplay information about a specific commanddb2 ? SQLnnnndisplays information about a specific SQLCODE.db2 ? DB2nnnndisplays information about a DB2 error.Examples:

db2 => connect to sample

db2 => create table t_users (id int not null primary key, name char(32) not null, sex char(1) default 'M', email varchar())



DB2 guide (Author: Chen Ming)7

Getting Started -instance



Create the instance

%db2icrt [–u fuser] instance_name

Operation SysteminstanceDatabase

Drop the instance

%

db2idrop instance_name



List the instance

% db2ilist

Start the instance

% db2start

Stop the instance

% db2stop [force]

DB2system layout

DB2 guide (Author: Chen Ming)8

Getting Started -Setting up the environment



DB2 environment variables



DB2INSTANCE --Specifics the active DB2 instanceDB2PATH -Specifics the path for DB2 executables...



Declare the DB2 environment variables -db2set



set a parameter for current instance

% db2set parameter=value







set a parameter for a specific instance

%db2set parameter=value -i instance_nameset a parameter for global level%db2set parameter=value -gview the all variables%db2set -all

DB2 guide (Author: Chen Ming)

9

Getting Started -Instance administration



List the instance

% db2ilist



Attach to an instance

db2 => attach [to nodename] [user username[using password]]



Get the current instance

db2 => get instance



DB2 Administrator Server(DAS)

Enable the remote administration of DB2 server.



DB2ADMIN START-Starts the DB2 Administration Server.DB2ADMIN STOP-Stops the DB2 Administration Server.DASICRT-Creates the DAS in UNIX.DASIDROP-Drops the DAS in UNIX.

DB2 guide (Author: Chen Ming)10

describe -obtain information about table structure



Get the table structure

db2 => DESCRIBE TABLE t_users



Get the table indexes

db2 => DESCRIBE INDEXES FOR TABLE t_users

db2 => DESCRIBE INDEXES FOR TABLE t_users SHOW DETAIL



Get the columns information

db2 => DESCRIBE select * from t_users

DB2 guide (Author: Chen Ming)11

explain



Method to know how a query is executed by DB2Prepare: create EXPLAINtables:

% db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL



Use EXPLAIN

db2 => explain plan set queryno=13 for select * from t_int where intcol=



Format the explain result:

% db2exfmt -d sample -e % -n % -s % -g TIC -w -1 -# 0



Use db2explnto explain SQL statement

% db2expln -d testdb -c chenming -p % -s 0 \\

-o db2expln_all_packages.rtp

% db2expln -d SAMPLE -stmtfile stmt1.sql -terminator @ \\

-o db2expln_stmt1.rpt

DB2 guide (Author: Chen Ming)

12

Tips



How to fetch first line

db2 => select * from t_users fetch first 1 rows only



How to list all tables in database?

db2 => list tables

DB2 guide (Author: Chen Ming)13

Outline



Getting StartedGetting ConnectedSQLTrouble Shooting

DB2 guide (Author: Chen Ming)14

SQL





DATABASETABLEVIEWTRIGER

STORE PRODUCEDate/TimePRIVILEGE

DB2 guide (Author: Chen Ming)15

SQL



Methods of execute sql



Execute sql from CLPExecute sql from file

% db2db2 =>



% db2 \"connect to cm\" # connect to database first% db2 -f cmd.sql# use „\\n‟ as end of a sql% db2 –tf cmd.sql # use „;‟ as end of a sql% db2 –td@ -f cmd.sql # use „@‟ as end of a sql

% db2 \"connect to reset\" # disconnect from database



Execute sql from application(embed sql)Comment

Begin with --DB2 guide (Author: Chen Ming)

16

SQL --Database





Create Database

First step:

# mkdir /db2

# chown db2inst1:db2grp1 /db2



Second step:

db2=> CREATE DATABASE testdb ON /db2



Drop Database

db2=> DROP DATABASE testdb

DB2 guide (Author: Chen Ming)17

SQL --TABLE



Demo how to create and modify table structure

CREATE TABLE t_users_log(uid int not null,

name_old varchar(32), name_new varchar(32), modifiedtime timestamp, operator varchar(32));

--modify table strutcture

ALTER TABLE t_users_log ADD action char(1);

--create index

CREATE INDEX idx_uid ON t_users_log(uid); --modify table constraint

ALTER TABLE t_users_log ADD CONSTRAINT check_op

CHECK(action IN ('D', 'I', 'U'));

DB2 guide (Author: Chen Ming)18

SQL --VIEW



creates a view on one or more tables, views or nicknames.

Demo how create a view:

CREATE VIEW MA_PROJ (PROJNO, PROJNAME, IN_CHARGE) AS SELECT PROJNO, PROJNAME, RESPEMP

FROM PROJECT

WHERE SUBSTR(PROJNO, 1, 2) = ‟MA‟CREATE VIEW PRJ_LEADER

AS SELECT PROJNO, PROJNAME, DEPTNO, RESPEMP, SALARY+BONUS+COMM AS TOTAL_PAY

FROM PROJECT, EMPLOYEE

WHERE RESPEMP = EMPNO AND PRSTAFF > 1

LASTNAME,

DB2 guide (Author: Chen Ming)19

SQL --TRIGGER



How to define a triger in database:

CREATE TRIGGER trg_users_del

AFTER DELETE ON t_users REFERENCING OLD AS O

FOR EACH ROW MODE DB2SQL

Insert into t_users_log values(o.uid, o.name, '', current timestamp, current user, 'D') ;

--db2 -td@ -f trg_users_upd.sqlCREATE TRIGGER trg_users_updAFTER UPDATE ON t_users

REFERENCING OLD AS O NEW AS NFOR EACH ROW MODE DB2SQLWHEN (o.uid=n.uid)BEGIN ATOMIC

INSERT INTO t_users_log VALUES(n.uid, o.name, n.name, currenttimestamp, current user, 'U');END @

DB2 guide (Author: Chen Ming)20

SQL -STORE PROCEDURE



How to define store procedure

--sp.sql

CREATE PROCEDURE spname(parameters...)LANGUAGE SQLBEGIN

--store procedure body here...END@

% db2 –td@ -vf sp.sql

Example:



How to run store procedure

CALL spname(parameter...)E.g.

db2=> call sp_max(3, 4, ?)

Note: use ? replace the OUT parameter.

DB2 guide (Author: Chen Ming)21

SQL –Store Procedure



Condition(IF)



Syntax

IF condition THEN sql-statement;

ELSEIF condition THEN sql-statement;ELSE sql-statement;END IF



Example

IF rating = 1 THEN UPDATE employeeSET salary = salary *

1.10, bonus = 1000 WHERE empno = employee_number;

ELSEIF rating = 2 THEN UPDATE employeeSET salary = salary *

1.05, bonus = 500 WHERE empno = employee_number;

ELSE UPDATE employeeSET salary = salary * 1.03, bonus = 0

WHERE empno = employee_number;END IF;

DB2 guide (Author: Chen Ming)22

SQL –Store Procedure



LOOP



Syntax



Example

DECLARE c1 CURSOR FOR

SELECT firstnme, midinit, lastname FROM employee;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET counter = -1;OPEN c1;fetch_loop:LOOP

FETCH c1 INTO v_firstnme, v_midinit, v_lastname;IF v_midinit = ‟ ‟ THEN

LEAVE fetch_loop;END IF;

SET v_counter = v_counter + 1;END LOOP fetch_loop;

SET counter = v_counter;CLOSE c1;

DB2 guide (Author: Chen Ming)23

SQL –Store Procedure



Exception handle

Demo how to use GET DIAGNOSTICS

CREATE PROCEDURE sp_test(IN a INT, IN b INT,OUT retval INT, OUT msg VARCHAR(250))LANGUAGE SQLBEGIN

DECLARE SQLCODE INTEGER ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNINGBEGIN

GET DIAGNOSTICS EXCEPTION 1 msg = MESSAGE_TEXT;SET retval = SQLCODE;ROLLBACK;END;

IF a > b THEN

SIGNAL SQLSTATE '98765' SET MESSAGE_TEXT = 'invalid parameter';END IF;

INSERT INTO t_int values(a);INSERT INTO t_int values(b);END @

DB2 guide (Author: Chen Ming)

24

SQL --Store Procedure



Internal variable



SQLCODE/SQLSTATE need explicit declare

DECLARE SQLCODE INTEGER;DECLARE SQLSTATE CHAR(5);



GET DIAGNOSTICS variableROW_COUNT –return update/insert/delete fetched rows

Update …;

Get diagnostics rowcnt = ROW_COUNT



RETURN_STATUS –return sp call result

Call spname();

Get diagnostics retval = RETURN_STATUS;

DB2 guide (Author: Chen Ming)25

SQL -FUNCTION



Define function



Example:

--cubeit.sql

CREATE FUNCTION CUBEIT(CUBE INT) RETURNS INTLANGUAGE SQL READS SQL DATABEGIN ATOMIC

DECLARE CUBE_NUMBER INT DEFAULT 0;IF (CUBE > 10000) THEN

SIGNAL SQLSTATE '98000'

SET MESSAGE_TEXT = 'NUMBER TOO BIG TO CUBE!';

END IF;

SET CUBE_NUMBER = CUBE * CUBE * CUBE ;RETURN CUBE_NUMBER;END@

% db2 –td@ -vf cubeit.sql



Invoke function



db2 => values cubeit(100)db2 => values cubeit(10001)

DB2 guide (Author: Chen Ming)26

SQL –Date/Time/Current/Sequence



Date/Time/



Date: yyyy-mm-dd E.g. 2005-10-12Time: hh.mm.ssE.g.13.03.24

Timestamp: yyyy-mm-ddhh.mm.ss.nnnnnn E.g. 1991-3-2-8.30.00 or 1991-03-02-08.30.00.000000

date/time/timestamp/user/schema/…E.g

db2 => VALUES CURRENT DATE



Current registers





Sequence

Sequence usage example:

db2=> CREATE SEQUENCE seq_cm AS INTEGER START WITH 100 INCREMENT BY 5db2=> VALUES NEXTVAL FOR seq_cmdb2=> VALUES PREVVAL FOR seq_cm

db2=> create table t_int(colint int)

db2=> insert into t_int values(next value for seq_cm)

DB2 guide (Author: Chen Ming)

27

SQL --PRIVILEGE



GRANT(Database Authorities)



Authorization:

To grant DBADM authority, SYSADM authority is required. To grant other authorities, either DBADM or SYSADM authority is required.



Syntax:

DB2 guide (Author: Chen Ming)28

SQL --PRIVILEGE



GRANT(Index Privileges)



Authorization:

The privileges held by the authorization ID of the statement must include at least one of the following:



DBADM authoritySYSADM authority.



Syntax:

DB2 guide (Author: Chen Ming)29

SQL --PRIVILEGE



GRANT(Package Privileges)



Authorization:

CONTROL privilege on the referenced package

The WITH GRANT OPTION for each identified privilege on package-name.SYSADM or DBADM authority.

The privileges held by the authorization ID of the statement must include at least one of the following:



To grant the CONTROL privilege, SYSADM or DBADM authority is required.



Syntax:

DB2 guide (Author: Chen Ming)30

SQL --PRIVILEGE



GRANT(Routine Privileges)



Authorization:



The privileges must include at least one of the following:

WITH GRANT OPTION for EXECUTE on the routineSYSADM or DBADM authority



Syntax:

DB2 guide (Author: Chen Ming)31

SQL --PRIVILEGE



GRANT(Schema Privileges)



Authorization:

WITH GRANT OPTION for each identified privilege on schema-nameSYSADM or DBADM authority

The privileges held by the authorization ID of the statement must include at least one of the following:



Privileges cannot be granted on schema names SYSIBM, SYSCAT, SYSFUN and SYSSTAT by any user (SQLSTATE 42501).



Syntax:

DB2 guide (Author: Chen Ming)32

SQL --PRIVILEGE



GRANT(Sequence Privileges)



Authorization:

WITH GRANT OPTION for use of the table spaceSYSADM or DBADM authorityDefiner of the sequence

The privileges held by the authorization ID of the statement must include at least one of the following:





Syntax:

DB2 guide (Author: Chen Ming)33

SQL --PRIVILEGE



GRANT(Table,View,or Nickname Privileges)



Authorization:

The privileges held by the authorization ID of the statement must include at least one of the following:





CONTROL privilege on the referenced table, view, or nickname

The WITH GRANT OPTION for each identified privilege. If ALL is specified, the authorization ID must have some grantable privilege on the identified table, view, or nickname.

SYSADM or DBADM authority.

To grant the CONTROL privilege, SYSADM or DBADM authority is required.

To grant privileges on catalog tables and views, either SYSADM or DBADM authority is required.

DB2 guide (Author: Chen Ming)34

SQL --PRIVILEGE



GRANT(Table,View,or Nickname Privileges)



Syntax:

DB2 guide (Author: Chen Ming)35

SQL --PRIVILEGE



GRANT(Tablespace Privileges)



Authorization:

The privileges held by the authorization ID of the statement must include at least one of the following:



WITH GRANT OPTION for use of the table spaceSYSADM, SYSCTRL, or DBADM authority



Syntax:

DB2 guide (Author: Chen Ming)36

SQL --PRIVILEGE



REVOKE(Database Authorities)



Authorization:



DBADM authoritySYSADM authority

To revoke DBADM authority, SYSADM authority is required.



Syntax:

DB2 guide (Author: Chen Ming)37

SQL --PRIVILEGE



REVOKE(Table,View or Nickname Privileges)



Authorization:



SYSADM or DBADM authority

CONTROL privilege on the referenced table, view, or nickname.

To revoke the CONTROL privilege, either SYSADM or DBADM authority is required.



Syntax:

DB2 guide (Author: Chen Ming)38

Database Backup/Restore



Backup



Off-line backup(Backup all tables in a database)

% db2 \"backup database cm to /home/db2backup/db/cm/ without prompting\"



Disadvantage: backup process use database exclusively.



On-line backup

Note: Use this mechanism need change default database configuration.Prepare(change related database cfg swith on):

% db2 \"update db cfg using logretain on\"

% db2 \"backup database cm to /home/backup/db2/cm without prompting\"



Online backup example

% db2 \"backup database cm online to /home/backup/db2/cm without prompting\"

List backup history information

% db2 \"list history backup all for database cm\"

DB2 guide (Author: Chen Ming)39

Database Backup/Restore



Backup file format(UNIX/Linux)

DBalias Instance YYMMDDhhmmss SequenceCM.0.db2i.NODE0000.CATN0000.20060208192256.001Type Node

DB2 guide (Author: Chen Ming)40

Database Backup/Restore



Restore

How to restore a database?

%db2 \"RESTORE DATABASE cm FROM /home/backup/db2/cm\"

%db2 \"RESTORE DATABASE cm INTO cmnew FROM /home/backup/db2/cm\"

DB2 guide (Author: Chen Ming)41

Table export/import

Utility of exchange data between filesystem and databaseExport

Export table data to file. E.g.

db2=> export to filename of del select * from t_users





Import

Import data from file. E.g.

db2=> import from filename of del insert into t_usersdb2=> import from filename of del replace into t_users

DB2 guide (Author: Chen Ming)42

DB2 utilities



db2look

E.g. export database `cm’ layout to file cm.sql

% db2look -d cm -e -l -o cm.sql

DB2 guide (Author: Chen Ming)43

Trouble Shooting



Describe the problem



What is the problem?

Where is the problem happening?When does the problem happen?

Under which conditions does the problem happen?Is the problem reproducible?

DB2 guide (Author: Chen Ming)44

Trouble Shooting



DB2 data collection



db2diag.log file

Location: $HOME/sqllib/db2dump/

Level:0-4 default is 3(Log error and warning messages).E.g. change loglevel:

% db2 “update dbm cfg using DIAGLEVEL 4”



Trap files

Whenever a DB2 process receives a signal or exception (raised by the operating system as a result of a system event) that is recognized by the DB2 signal handler, a trap file is generated in the DB2 diagnostic directory. The files are created using the following naming convention:

tpppppp.nnn

o pppppp : the process ID (PID)

o nnn : the node where the trap occurredo Eg. t123456.000





Dump files

DB2 internal information collect here. file format: pppppp.nnndb2support utility

% db2support . –d testdb –c

syslog

Location: /var/log/message*

DB2 guide (Author: Chen Ming)45

Trouble Shooting



Linux OS(SuSE)



disk usage



dftop vmstat freeiostat



system activity





System log at /var/log

DB2 guide (Author: Chen Ming)

46

Trouble Shooting



DB2 configuration collection

Run following command:

% db2level

% db2 \"get dbm cfg\"

% db2 \"connect to dbname\"% db2 \"get db cfg\"

% db2 \"list db directory\"% db2 \"list node directory\"% db2set -all

DB2 guide (Author: Chen Ming)47

Trouble Shooting



db2trc –DB2 trace utility

Get a trace file and call IBM supporttrace it:% db2stop

% db2trc on -i 8m -m \"*.*.2.*.*\" -t% db2start% db2trc clear

% db2 connect to sample% db2trc dump dmp% db2trc offanaylse:

% db2trc format dmp fmt% db2trc flow dmp flw

DB2 guide (Author: Chen Ming)48

Trouble Shooting



How to detect lock and unlock it?



Step 1: get the application handle

db2=> update monitor switches using lock ondb2=> get snapshot for locks on testdb



Step 2: force the special application handleOr shutcut unlock(Just one step):

db2=> force application(appid)



db2=> force application all

DB2 guide (Author: Chen Ming)49

References



SQL Reference Volume 1/2

DB2 guide (Author: Chen Ming)50

FAQ

DB2 guide (Author: Chen Ming)

51