Table space management, creation, and maintenance

Table space management, creation, and maintenance
1 storage physical structure

Datapage data page is the minimum storage structure of db2: 4 kb, 8 KB, 16 KB, 32 KB

The minimum storage structure of the operating system is block: windows Minimum kb, Linux Minimum 1 KB

The size of the extent field, which is specified by extentsize when creating the database and tablespace. By default, it is composed of 32 data pages;

Exclusive extent for each table;

2. tablespace Management

SMS, space managed by the system:

Space is managed and allocated by the File Manager of the operating system. Space is managed by the operating system.

DMS: the space for database management:

DB2 Management Program manages and allocates space. Therefore, the tablespace can use bare devices or file systems;

Automatic Storage of DMS:

SMS management, performance is relatively poor;

Features	SMS	DMS	DMS Automatic Storage
Strip or not	Yes	Yes	Yes
Default type	V8		V9
Space allocation	On-demand growth	Pre-allocated, DBA intervention is allowed Default autoresize no	Pre-allocated, automatically increasing default autoresize yes
Performance		Bare devices can be implemented	No
Management object	OS	Db2	Db2
Container changes		Alter tablespace add/drop	Alter tablespace reduce

 

Restrictions on SMS management:

A maximum of 64 GB Data (4 kb page) and GB Data (32 KB) can be stored in a tablespace)

DMS Management

The maximum tablespace size is 8 TB (4 kb) and 64 TB (32 KB)

3. Create a database

Default database creation location:

C: \ Program Files \ IBM \ SQLLIB \ BIN> db2 get dbm cfg | find/I "DFTDBPATH"

Default database path (DFTDBPATH) = C:

Specify the creation location:

C: \ Program Files \ IBM \ SQLLIB \ BIN> db2 "create database ibmdb1 on c: \ db2"

The SQL1047N application has been connected to another database.

Automatic Management:

Create database mydb managed byautomatic storage;

SMS

Managed by system

DMS

Managed by database

3.1 code page

Usually set to dubyte code DBCS

Chinese code page 1368, code set GBK, territory is CN

For applications with XML data, only the Unicode format can store XML

Create database xmldb using codesetUTF-8 territroy us;

3.2 define a tablespace

Catalogtablespace

Usertablespace

Temporarytablespace

Prefetchsize number of pages

The database is pre-stored and loaded before the actual reference to reduce IO operations.

DFT_prefetch_sz indicates the number of pages read each time when a database triggers a request. You can discard multiple extended data blocks.

For example

3.3 database creation statement

Createdatabase mydb

Def_prefetch_sz4

Catalogtablespace managed by database

Using (fle 'data1/1. dbf' 10G, file'/data2/1. dbf' 1G)

Extentsize8

Prefetchsize16

Temporarytablespace managed by system

Using ('/data/tmp1.dbf','/data/tmp2.dbf ')

Usertablespace mnaged by database

Using (file '/data/user. dat' 1G );

Extentsize24

Prefetchsize48

By default, the extended data block has four pages;

Extentsize8 will overwrite def_extent_sz

Temporary tablespace is managed by the operating system;

3.4 database directory

C: \ DB2 \ NODE0000 \ SQL00001

SQLSPCS Stores Table space information

SQLSGF saves the storage path information for Automatic Storage Management

SQLdbconf contains the database configuration information and cannot be edited. You can use

Updatedatabase configuration, reset database configuratrion statement

 

Db1rhist. asc history file,

Sqlogctl. glfh log Control File

Rollback is required when the system crashes;

3.5 Maintain tablespaces

Db2list tablespaces show detail

Tablespace id = 3

Name = IBMDB2SAMPLEREL

Type = database management space

Content = all persistent data. Large tablespace.

Status = 0x0000

Explanation:

Normal

Total pages = 4096

Available pages = 4064

Used pages = 608

Available pages = 3456

High water mark (PAGE) = 608

Page size (in bytes) = 8192

Extended data block size (PAGE) = 32

Prefetch size (PAGE) = 32

Container COUNT = 1

 

C: \ ProgramFiles \ IBM \ SQLLIB \ BIN> db2 "list tablespace containers for 3"

 

Tablespace 3 Container

 

Container id = 0

Name = C: \ DB2 \ NODE0000 \ SAMPLE \ T0000003 \ C0000000.

LRG

Type = File

 

3.6 optimize table space performance on raid

If the strip size is 64 KB and the page size is 16 KB, it is more appropriate to extend the size of 64 kB * 4;

DB2_parallel_IO: automatic