DB2 Architecture Overview

DB2 Architecture
Architectural components
1. Tablespace
2. Container
3. Buffer Pool
4. Page Size

© 2007 IBM Corporation

DB2 Architecture Overview

© 2007 IBM Corporation

DB2 Architecture Overview

© 2007 IBM Corporation

DB2 Architecture Overview

TABLESPACE
A tablespace needs to know where data is stored physically.
DB2 distinguishes between two possible data locations: System managed
(Files handled by the operating system) and
Database managed (Files handled by the database or Devices).
In the latter case the database will preallocate space for the files or
devices, giving a slightly faster operation.
On the other hand, database managed files cannot grow automatically.

A tablespaces data location is called container.

Any tablespace must have at least one container.
Note, that one tablespace cannot have both system managed containers
and database managed containers.
If the first container is system managed, then all containers are, and vice
versa.

A TableSpace can be associated to a BufferPool. If it is, it inherits certain

settings from the BufferPool, in particular the PageSize.

© 2007 IBM Corporation

DB2 Architecture Overview

Types of TABLE SPACE

1>Catalog table space

There is only one catalog table space per database, and it is created when
the CREATE DATABASE command is issued.
Named SYSCATSPACE by DB2, the catalog table space holds the system
catalog tables.
This table space is always created when the database is created.
2>Regular table spaces
Regular table spaces hold table data and indexes.
It can also hold long data such as Large Objects (LOBs) unless they are
explicitly stored in long table spaces.
A table and its indexes can be segregated into separate regular table
spaces, if the table spaces are database managed space (DMS).
At least one regular table space must exist for each database. The default
is named USERSPACE1 when the database is created.
3>Long table spaces
Long table spaces are used to store long or LOB table columns and must
reside in DMS table spaces.
They can also store structured type columns or index data. If no long table
space is defined, then LOBs will be stored in regular table spaces.
Long table spaces are optional and none will be created by default.
© 2007 IBM Corporation
DB2 Architecture Overview

4>System temporary table spaces

System temporary table spaces are used to store internal temporary
data required during SQL operations such as sorting,
reorganizing tables, creating indexes, and joining tables. At least one
must exist per database.
The default created with the database is named TEMPSPACE1.
5>User temporary table spaces
User temporary table spaces store declared global temporary tables. No
user temporary table spaces exist when a database is created.
At least one user temporary table space should be created to allow
definition of declared temporary tables.
User temporary table spaces are optional and none will be created by
default.

© 2007 IBM Corporation

DB2 Architecture Overview

Table space management

Table spaces can be managed in two different ways:

System managed space (SMS)

SMS table spaces are managed by the operating system. Containers are defined
as regular operating system files and they are
accessed via operating system calls. This means that all the regular operating
system functions
will handle the following: I/O will be buffered by the operating system, space will be
allocated according to the
operating system conventions, and the table space is automatically extended when
it is necessary.
However, containers cannot be dropped from SMS table spaces, and adding new
ones is restricted to partitioned databases.
The three default table spaces explained in the previous section are SMS.

© 2007 IBM Corporation

DB2 Architecture Overview

Database managed space (DMS)

DMS table spaces are managed by DB2. Containers can be defined either
as files (which will be fully allocated with the size given
when the table space is created) or devices. DB2 will manage as much of
the I/O as the allocation method and the operating system will allow.
Extending the containers is possible by using the ALTER TABLESPACE
command. Unused portions of DMS containers can be also released.

© 2007 IBM Corporation

DB2 Architecture Overview

1>Syntax: <CREATE TABLESPACE ...>

Example: CREATE TABLESPACE USERSPACE3
PAGESIZE 8K
MANAGED BY SYSTEM
USING ('d:\usp3_cont1', 'e:\usp3_cont2', 'f:\usp3_cont3')
EXTENTSIZE 64
PREFETCHSIZE 32
BUFFERPOOL BP3
OVERHEAD 24.1
TRANSFERRATE 0.9
2>Syntax: <ALTER TABLESPACE....>
Example: ALTER TABLESPACE RESOURCE
ADD (DEVICE '/dev/rhd9' 10000,
DEVICE '/dev/rhd10' 10000)
3>Syntax: <DROP TABLESPACE...>
Example: DROP TABLESPACE ACCOUNTING

To view your table space attributes and containers

LIST TABLESPACES SHOW DETAIL
© 2007 IBM Corporation
DB2 Architecture Overview

BUFFER POOL

A buffer pool is associated with a single database and can be used by more
than one table space.
When considering a buffer pool for one or more table spaces, you must
ensure that the table space page size
and the buffer pool page size are the same for all table spaces. A table space
can only use one buffer pool.
When the database is created, a default buffer pool named IBMDEFAULTBP
is created which is shared by all table spaces.
More buffer pools can be added by using the CREATE BUFFERPOOL
statement.
The buffer pool size defaults to the size specified by the BUFFPAGE
database configuration parameter but can be overridden by
specifying the SIZE keyword in the CREATE BUFFERPOOL command.
Adequate buffer pool size is essential to good database
performance since it will reduce disk I/O, the most time consuming operation.
Large buffer pools will also have an effect on query optimization, since more
of the work can be done in memory.

© 2007 IBM Corporation

DB2 Architecture Overview

Block-based buffer pools

Version 8 allows you to set aside a portion of the buffer pool (up to
98%) for block-based prefetching.
Block-based I/O will improve the efficiency of prefetching by reading
a block into a contiguous area of memory instead of
scatter loading it into separate pages. The size of the blocks must be
uniform per buffer pool and is controlled by the BLOCKSIZE parameter.
The value is the size of block in pages, from 2 to 256, the default
being 32.
Extended storage
DB2 does not use extended storage for buffers. However, extended
storage can be used to cache memory pages,
making it faster to move the pages out from memory.

Example of CREATE BUFFERPOOL statement

CREATE BUFFERPOOL BP3 SIZE 2000 PAGESIZE 8K
How to view your buffer pool attributes
SELECT * FROM SYSCAT.BUFFERPOOLS

© 2007 IBM Corporation

DB2 Architecture Overview

CONTAINERS

Every table space has one or more containers.Each container can only
belong to a single table space but a table space can have many containers.
Containers can be added to or dropped from a DMS table space, and their
sizes can be modified. Containers can only be added to SMS table spaces on
partitioned databases in a partition, which does not yet have a container
allocated for the table space. When new containers are added, an automatic
rebalancing will start to distribute the data across all containers.

© 2007 IBM Corporation

DB2 Architecture Overview

PAGE SIZE

Defines the size of pages used for the table space. Sizes supported are
4K, 8K, 16K and 32K. The page size limits the row length and column count
of tables that can be placed in the table space according to the following
table:

Table 1. Implications of page size

Page Size Row Size Limit Column Count Limit Maximum
capacity
4 KB 4 005 500 64 GB
8 KB 8 101 1 012 128 GB
16 KB 16 293 1 012 256 GB
32 KB 32 677 1 012 512 GB

Table spaces are limited to 16384 pages, so choosing a larger page size will
increase the capacity of the table space.

© 2007 IBM Corporation

DB2 Architecture Overview

Extent size
Specifies the number of pages that will be written to a container before
skipping to the next container. The database manager cycles repeatedly
through the containers as data is stored. This parameter has effect only when
there are multiple containers for the table space.

Prefetch size
Specifies the number of pages that will be read from the table space when
data prefetching is being performed. Prefetching reads in data needed by a
query prior to its being referenced by the query so that the query need not
wait for I/O to be performed. Prefetching is selected by the database
manager when it determines that sequential I/O is appropriate and that
prefetching may help to improve performance.

© 2007 IBM Corporation

DB2 Architecture Overview

Overhead and transfer rate

These values are used to determine the cost of I/O during query
optimization. Both values are measured in milliseconds and they should be
the average for all containers. The overhead is the time associated with I/O
controller activity, disk seek time and rotational latency. The transfer rate is
the amount of time necessary to read one page into memory. The default
values are 24.1 and 0.9, respectively. These values can be calculated based
on hardware specifications.

© 2007 IBM Corporation