DB2 DBA Basic Concepts

DB2 Database Administration

Mtro. José Luis León Gómez

José Luis León Gómez

DB2 Instances

 A logical context in

which objects from

several databases

lives

 Independent from

others in the same

machine

 It has its own

memory

José Luis León Gómez

DB2 Memory Model

José Luis León Gómez

Creating an instance in AIX

DBSVR:/usr/opt/db2_08_01/instance#

./db2icrt -a SERVER -s ese -u db2fenc1 -w 64 -p db2cdb2cam

db2cam

DBI1070I Program db2icrt completed successfully.

DBSVR:/usr/opt/db2_08_01/instance# su - db2cam

$ db2start

04/01/2008 20:04:17 0 0 SQL1063N DB2START processing was

successful.

SQL1063N DB2START processing was successful.

# ./db2idrop -f instance_name

# ./db2ilist –a

José Luis León Gómez

DB2 Environment

José Luis León Gómez

Connectivity

attach to nodename user username using password

connect to database_name user username using password

José Luis León Gómez

Security

José Luis León Gómez

Configuration

 db2 create database db_name autoconfigure

 db2 update db cfg for dbname using self_tuning_mem yes

 db2 update db cfg for dbname using self_tuning_mem on

 b2 update db cfg for dbname using database_memory

automatic

 db2 update db cfg for dbname using database_memory

1000000

José Luis León Gómez

Database objects

 Buffer pools

 Table spaces

 Tables and indexes

 Views

 Identity columns

 Temporary tables

 Constraints

 Triggers

José Luis León Gómez

DB2 storage triangle

 A partition group is a

collection of one or more

database partitions within

a database.

 When a table space is

created, it is assigned to a

partition group

 Each table space must

have one or more

containers

 A container can be

 Operating system

directory

 File with a predetermined

size

 Raw device such as an

unformatted hard drive, a

partition on the hard

drive, or a logical volume

get snapshot for tablespaces

list tablespaces

José Luis León Gómez

DB2 Process Model

José Luis León Gómez

DB2 Process Model

José Luis León Gómez

Logs DB2 uses a write-ahead

logging scheme

José Luis León Gómez

Circular logging

 Default

 Reuses the logs in a circular mode.

 If the log is still an active log, it cannot be

reused.

 Then  secondary log

José Luis León Gómez

Archival logging

...