creating databases and data placement

7/27/2019 Creating Databases and Data Placement

1/17

CREATINGDATABASESANDDATA

PLACEMENT

Presented By:

Satrio Agung WIcaksono


2/17

AFTERCOMPLETINGTHISUNIT, YOUSHOULD

BEABLETO:

Review specifics of creating a database

Explore the System Catalog tables and

views

Compare table space managementmethods, including DMS, SMS and

Automatic Storage table spaces

Describe how to setup and managedatabase with Automatic Storage enabled

Differentiate between table spaces,

containers, extents, and pages


3/17


4/17

CREATEDATABASEOVERVIEW

Databases are created within a Database Manager

instance

Table spaces are a logical layer created within a

database

Tables are created within table spaces


5/17

DATABASESTORAGEREQUIREMENTS

Database Path: Database Control Files for each database

Includes Database Configuration file, Recovery History, Log Control files, Tablespace Control file, Bufferpool Control file and others

Initial location for database log files

Default location is dftdbpathin DBM CFGNeeds to be a local file system

Automatic Storage paths: Allows table space storage to be managed at the database level

If Automatic storage is enabled there will be at least one path defined

Initial Storage Paths defined when database is created

Default System table spaces: Use Automatic Storage management by default, if enabled, but canbe

defined to use any supported type of table space

SYSCATSPACE: DB2 catalog tables

TEMPSPACE1: System Temporary tables, provide work space for sortingand utility processing

USERSPACE1: Initial table space for defining user tables and indexes


6/17

DB2 STORAGE MANAGEMENTBASICS

DB2 supports three types of storage management for table spaces

All three types can be used in a single database

Storage Management type set when a table space is created

DMS Database Managed Storage: Table space containers defined using the specified files or raw devices

Disk space allocated is reserved for objects in that table space

SMS System Managed Storage: Table space containers defined using the specified directories

No defined initial size or limit

DB2 creates files for each database object

Disk space is freed when objects are dropped

Automatic Storage Management:

Disk Storage Paths are assigned to the database Can be enabled when a database is created or added to an existing database

Available space can be monitored at the database partition level

DB2 defines the number and names for containers

Uses SMS for temporary storage and DMS for other storage types


7/17

DATABASE PATH FILES


8/17

DEFAULTTABLESPACECONTAINERSWITH

AUTOMATIC STORAGE


9/17

SYSTEM CATALOG TABLES & VIEW


10/17

BUFFER POOLS

A buffer poolis an area of main memory that has

been allocated by the database manager for the

purpose of caching table and index data as it is

read from disk

Every DB2 database must have a buffer pool

How buffer pools are used :

When a row of data in a table is first accessed, the

database manager places the page that contains that

data into a buffer pool. Pages stay in the buffer pooluntil the database is shut down or until the space

occupied by the page is required by another page


11/17

TABLE SPACE

A table space is a storage structure containing

tables, indexes, large objects, and long data.

They are used to organize data in a database into

logical storage groupings that relate to where data

is stored on a system.

Table spaces are stored in database partition

groups


12/17

RELATIONSHIPBETWEENTABLESPACESAND

BUFFERPOOLS

Each table space is associated with a specific

buffer pool

IBMDEFAULTBP is the default buffer pool.

The database manager also allocatesthese systembuffer pools: IBMSYSTEMBP4K,

IBMSYSTEMBP8K, IBMSYSTEMBP16K, and

IBMSYSTEMBP32K


13/17

TABLESPACE, CONTAINER, EXTENT, PAGE


14/17

THERELATIONSHIPBETWEENTABLESANDTABLESPACES

WITHINADATABASE, ANDTHECONTAINERSASSOCIATEDWITH

THATDATABASE


15/17

CONTAINER & TABLESPACES

Container is an Allocation of Physical Space


16/17

TABLESPACEDESIGNLIMITS: ROW IDENTIFIERS


17/17

STORAGE MANAGEMENTALTERNATIVES:

AUTOMATIC

Automatic Storage Managed: Administration is very easy, no need to define the number or names of the

containers

Disk space assigned and maintained at the database level

Monitoring of available space at the database partition level instead ofeach table space

Multiple containers will be created using all available storage paths forperformance

Automatic Storage can be enabled when the database is created oraddedto an existing database Default is ON for CREATE DATABASE with DB2 9

Storage paths can be added or removed using ALTER DATABASE

Uses standard DMS and SMS under the covers: DMS used for REGULAR and LARGE table spaces

SMS used for SYSTEM and USER TEMPORARY table spaces

Table space allocation controlled by CREATE/ALTER options: INITIALSIZE: Defaults to 32 MB

AUTORESIZE: Can be set to YES or NO

INCREASESIZE: Can be set to amount or percent increase

MAXSIZE: Can define growth limits

creating databases and data placement

Documents