IBM Software Group
Introduction To IBM Universal Database For
Linux, UNIX And Windows
1. Database Objects
IBM Software Group
Agenda
Overview of database objects
Overview of storage objects
IBM Software Group
Database Objects
Instances
Databases
Schemas
System catalog tables and views
Tables
Indexes
Views
IBM Software Group
DB2 Object Hierarchy
Instance 1 DBM configuration file
Catalog Catalog
Database 1 Log Database 2 Log
DB configuration file DB config. file
Table1 Table2 Table3 Table2
Index1 View1
View1 Index1
BLOBs View2 Index2
View3
IBM Software Group
Instance, Database, Table Spaces And Tables
Databases are created in DB2 instances
Table spaces are a logical layer created within a database
Tables are created within table spaces
Database Manager
Instance
Database1 Database2
Tablespace A Tablespace B Tablespace A
Table 1 Table 2 Table 3 Table 1 Table 2
Table
4
IBM Software Group
DB2 Instance
Also known as database manager (DBM)
DB2 code that manages data, controls what can be done to
the data
Manages system resources assigned to it
An instance has its own databases (which other instances
cannot access)
Separate security from other instances on the same machine
All its databases share the same system directories
IBM Software Group
DB2 Instance Administration
DB2 instance creation / migration / updating done by user
root on Linux/UNIX (Administrator on Windows)
Programs located in sub-directory instance (Linux, UNIX)
respectively bin (Windows)
– Example Linux V8.1: /opt/IBM/db2/V8.1/instance
IBM Software Group
DB2 Instance Administration (Cont’d)
Overview of some important DB2 Instance administration
tools:
– db2icrt (creates DB2 instance)
– db2ilist (lists DB2 instances on server)
– db2iupdt (updates DB2 instances – e.g. after Fix Pack
installation)
– db2imigr (migrates DB2 instances – e.g. from V7.2 to V8.1)
– db2idrop (drops DB2 instances)
IBM Software Group
DB2 Instance Commands
To start the DB2 Instance / Database Manager, enter:
– db2start
To stop the DB2 Instance / Database Manager, enter:
– db2stop (Command will fail if databases are still active)
To force off all active applications before stopping DB2 Instance,
use:
– db2stop force
Emergency Stop of DB2 Instance / Database Manager
– db2_kill (Crash Recovery necessary prior accessing database
again)
IBM Software Group
CREATE DATABASE Syntax
CREATE DATABASE database-name
DB . . .
Create Database Options
Create Database options:
ON path ALIAS database-alias
drive
USING CODESET codeset TERRITORY territory
SYSTEM DFT_EXTENT_SZ dft-extentsize
COLLATE USING
IDENTITY
CATALOG TABLESPACE | tblspace-defn | USER TABLESPACE | tblspace-defn |
WITH "comment-string"
TEMPORARY TABLESPACE | tblspace-defn |
tblspace-defn:
,
SYSTEM USING ( 'container-string' )
| MANAGED BY ,
DATABASE USING ( FILE 'container-string' number-of-pages )
DEVICE
EXTENTSIZE number-of-pages PREFETCHSIZE number-of-pages
OVERHEAD number-of-milliseconds TRANSFERRATE number-of-milliseconds
IBM Software Group
Tasks Completed By DB2 During CREATE DB
1. Creates database
2. Creates SYSCATSPACE, TEMPSPACE1 and USERSPACE1 table
spaces
3. Creates system catalog tables and recovery log
4. Catalogs database in local database directory and system
database directory
5. Assigns codeset, territory, and collating sequence
6. Creates SYSCAT, SYSFUN, SYSIBM, SYSSTAT schemata
7. Binds previously defined database manager bind files to the
database (db2ubind.lst)
IBM Software Group
Tasks Completed By DB2 During CREATE DB
8. Grants privileges:
DBADM authority with CONNECT, CREATETAB, BINDADD,
IMPLICIT_SCHEMA, CREATE_NOT_FENCED and LOAD
privileges to database creator
SELECT privilege on system catalog tables and views to PUBLIC
BIND and EXECUTE privileges to PUBLIC for each successfully
bound utility
CREATETAB, BINDADD, IMPLICIT_SCHEMA, and CONNECT
privileges to PUBLIC
Use privilege on USERSPACE1 table space to PUBLIC
IBM Software Group
DB2 Directory Structure
Drive / Directory The drive or directory specified on the CREATE DATABASE command
DB2 Instance
DB2 Instance Name
Name The name of the DB2 instance owner
The partition number of the database, 0
NODE0000 for a non-partitioned database
The database ID, starts at 1, increases for all SQL0001
subsequent databases
The catalog table space, SYSCATSPACE SQLT0000.0
The temporary table space, TEMPSPACE1 SQLT0001.0
The user table space, USERSPACE1 SQLT0002.0
The default log directory for the database SQLOGDIR
IBM Software Group
System Catalog Tables
Each database includes a set of system catalog tables, which
describe the logical and physical structure of the data
DB2 creates and maintains an extensive set of system catalog
tables for each database
You cannot explicitly create or drop them, but you can query
and view their contents using the catalog views
SYSCAT v
...
i
SYSIBM.SYSCOLUMNS
SYSSTAT e
SYSIBM.SYSTABLES
w
...
s
IBM Software Group
Tables
A table is an unordered set of data records. It consists of columns
and rows that are generally known as records
Tables can either be permanent (base) tables, temporary
(declared) tables, or temporary (derived) tables
All database and table data is assigned to table spaces
The data in the table is logically related, and relationships can be
defined between tables
Data can be viewed and manipulated create table artists
(artno smallint not null,
based on mathematical principles name varchar(50) with default'abc',
classification char(1) not null,
and operations called relations bio clob(100K) logged,
picture blob( 10M) not logged compact)
in dms01
index in dms02
long in dms03;
IBM Software Group
Indexes
An index is a physical object that is associated with a single
table
Indexes are used to force uniqueness in a table (that is, to make
sure that there are no duplicate values) and to improve
performance when retrieving information
An index is a set of keys, each pointing to rows in a table. The
index allows more efficient access to rows in a table by creating
a direct path to the data through pointers
– The SQL optimizer automatically create unique index itemno on albums (itemno)
chooses the most efficient way
to access data in tables create index item on stock (itemno) cluster
create unique index empidx on employee (empno)
include (lastname, firstname)
IBM Software Group
Views
A view is an efficient way of representing data without needing
to maintain it
A view is not an actual table and requires no permanent storage
but to the user, a view just looks like a table
A view can include all or some of the columns or rows contained
in the tables on which it is based
– A view can be used to restrict which rows and columns can be
viewed or updated by certain users
CONNECT TO TESTDB
CREATE VIEW EMPSALARY
AS SELECT EMPNO, EMPNAME, SALARY
FROM PAYROLL, PERSONNEL
WHERE EMPNO=EMPNUMB AND SALARY > 30000.00
IBM Software Group
Storage Objects
Table space
IBM Software Group
Table Spaces
Database managed storage (DMS) versus system managed
storage (SMS)
Default database configuration
Create Tablespace syntax
Getting info about table spaces and containers
Alter Tablespace syntax
IBM Software Group
SMS Versus DMS
A table space can be either system managed space (SMS),
or database managed space (DMS)
For an SMS table space, each container is a directory in the
file space of the operating system, and the operating
system's file manager controls the storage space
For a DMS table space, each container is either a fixed size
pre-allocated file, or a physical device such as a disk, and
the database manager controls the storage space
IBM Software Group
Containers And Table Spaces
A container is an allocation of physical space
File
Directory
Device
File Directory Device
SMS
DMS
IBM Software Group
SMS Tablespace Summary
Containers are operating system directories
– Can easily increase table space capacity by enlarging underlying operating system
file system
Data striped across container by extent
Disk space allocated on demand
– One page at a time (by default)
Data "objects" (i.e.. table data, indexes, LONG VARCHARs, LOBs)
located by operating system file name
Tips:
– Associate each container (i.e. directory) with a different file system (otherwise table
space capacity limited to that of a single file system)
– Ensure containers have equal capacity (excess in larger containers isn't exploited)
IBM Software Group
DMS Tablespace Summary
Containers are either operating system files or raw devices
– Can increase table space capacity via ALTER TABLESPACE
command
Data striped across container by extent
Disk space allocated at table space creation
– SMPs (space map pages) keep track of what extents are used
and which are free
IBM Software Group
Table Placement In DMS
DB2 is able to distribute one table across multiple DMS table
spaces
DMS table space with three data types:
– Regular, (user | system) temporary and large (large table spaces can
be used for indexes too)
Create table
employee <table
definition>
index in INDEX
Table Space INDEX Table Space LARGE
long in LARGE
Table Space DATA
EMPLOYEE DATA EMPLOYEE INDEX LARGE DATA FOR EMPLOYEE TABLE
= Data Portion of = Index Portion of = Large Data Portion of
Employee Employee Employee
IBM Software Group
Defining Table Spaces
When defining a table space, you can
– Specify containers and their description
– Specify storage type
– Specify type of data
– Specify extent size
IBM Software Group
Writing To Table Space Containers
The extent size is the number PAGESIZE (default 4k) pages
that the database will write to a container before skipping to
next container (data is written in round-robin manner)
– DFT_EXTENT_SZ defined at database level (default extent size)
– EXTENTSIZE defined at CREATE TABLESPACE command
1
page
Container 0
0 2 Container 1
1
3
Extent
Extent = 32 Pages
(Default)
Tablespace B
IBM Software Group
Reading From Table Spaces
Prefetching reads in data needed by a query prior to it being
referenced by the query, so that the query need not wait for the
underlying system to perform I/O operations
PREFETCHSIZE specifies the number of PAGESIZE (default 4k)
pages that will be read from the table space when data Database
Engine
prefetching is being performed Synchronous Read
Buffer Pool
1 page
– PREFETCHSIZE defined at I/O
CREATE TABLESPACE Server
command Prefetch Read
Prefetch Read
– Can be altered with the
I/O Data access
ALTER TABLESPACE Server is faster
command
I/O
Server
Prefetch Read DFT_PREFETCH_SZ
NUM_IOSERVERS
SEQDETECT
IBM Software Group
CREATE TABLESPACE Syntax (1/2)
REGULAR
PAGESIZE 4096
CREATE TABLESPACE tablespace-name
LONG PAGESIZE integer
K
SYSTEM
TEMPORARY
USER
MANAGED BY SYSTEM | system-containers |
DATABASE | database-containers | EXTENTSIZE number-of-pages
integer K
M
G
PREFETCHSIZE number-of-pages BUFFERPOOL bufferpool-name
integer K
M
G
24.1
OVERHEAD number-of-milliseconds
0.9
TRANSFERRATE number-of-milliseconds
IBM Software Group
Creating SMS Table Spaces
UNIX: create tablespace enterprise managed by system using
('/database/firstcontain', '/database/secondcontain',
'/database/thirdcontain') prefetchsize 32
Indicates SMS Table Table Space Container
Space Definitions
Windows: create tablespace enterprise managed by system using
('E:\db\firstcnt', 'F:\db\sndcnt',
'G:\database\thirdcnt') prefetchsize 32
IBM Software Group
Creating DMS Table Spaces
UNIX: create tablespace humanres managed by database
using (device '/dev/data1' 1024, device
'/dev/rdata2' 1024) extentsize 8 prefetchsize 16
Indicates DMS Table Table Space Container
Space Definitions
Table Space Storage
Type
Windows: create long tablespace longtbs managed by
database using (file '/data/longtbs/cont.001'
500M, file '/data/longtbs/cont.002' 500M)
extentsize 8 prefetchsize 16
IBM Software Group
Example – DB2 List Tablespaces Show Detail
db2 list tablespaces show detail
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1729
Useable pages = 1729
Used pages = 1729
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
IBM Software Group
Example – DB2 List Tablespaces Show Detail
Tablespace ID = 5
Name = LONGTBS
Type = Database managed space
Contents = Long data only
State = 0x0000
Detailed explanation:
Normal
Total pages = 1024
Useable pages = 992
Used pages = 96
Free pages = 896
High water mark (pages) = 96
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
IBM Software Group
Changing The Size Of A Table Space
The size of a DMS table space can be changed (increased and
decreased) online. The table space is still accessible to users
– A SMS table space size is altered through changing of underlying file system
– Data will be rebalanced across containers automatically if needed
Rebalance done asynchronously after the following ALTER
TABLESPACE options:
– ADD container (forward rebalancing)
– DROP container (reverse rebalancing)
To avoid rebalancing choose the following ALTER TABLESPACE
options:
– RESIZE / EXTEND / REDUCE container
– BEGIN NEW STRIPE SET
