DATABASE
Database 创建在DBM下,TBS是DATABASE的逻辑层。
表是创建在TBS 下的。
每一个INSTANCE可以有一个或多个DATABASE,一个DATABASE 有三个或三个以上的TBS。
1. Create database
2. Create database configuration file and set default values
3. Bind database utilities to the database (db2ubind.lst)
4. Define SYSCATSPACE, TEMPSPACE1, and USERSPACE1 table
spaces
5. Set up all of the system catalog tables and allocate the database
recovery log
6. Catalog database in local database directory and system database
directory
7. Assign codeset, territory, and collating sequence
8. Create SYSCAT, SYSFUN, SYSIBM, SYSSTAT schemata
9. Grant the following privileges to database Creator:
– DBADM authority with CONNECT, CREATETAB, BINDADD,
IMPLICIT_SCHEMA, CREATE_NOT_FENCED_ROUTINE,
CREATE_EXTERNAL_ROUTINE, QUIESCE_CONNECT, and LOAD
authorities
10.Grant the following privileges to Public (If RESTRICTIVE is not
specified):
– SELECT privilege on system catalog tables and views
– BIND and EXECUTE privileges to PUBLIC for each successfully bound utility
– CREATETAB, BINDADD, IMPLICIT_SCHEMA, and CONNECT authorities
– USE privilege on USERSPACE1 table space
只有拥有SYSADM和SYSCTRL的权限的用户才能创建DATABASE
CREATE DATABASE database-name
[AT DBPARTITIONNUM | [AUTOMATIC STORAGE {NO | YES}]
[ON path[{,path}...]指数据库表空间默认存储位置(不指定则是DBM CFG 中DFTDBPATH 所指位置)[DBPATH ON path]]数据库配置文件位置(不指定则是 ON 所指定第一个目录位置)
[ALIAS database-alias] [USING CODESET codeset TERRITORY territory](指定字符集和国家)
[COLLATE USING {SYSTEM | IDENTITY | IDENTITY_16BIT | COMPATIBILITY | NLSCHAR}](指定数据存储使用方式)
[PAGESIZE integer [K]](默认页大小 4,8,16,32K)
[NUMSEGS numsegs](指定SMS对应容器的个数)
[DFT_EXTENT_SZ dft_extentsize](指定表空间默认盘区大小是N PAGESIZE)
[RESTRICTIVE](是否使用限制模式创建数据库,DB CFG 有这个参数)
[CATALOG TABLESPACE tblspace-defn] (创建系统表空间)
[USER TABLESPACE tblspace-defn](创建默认用户表空间)
[TEMPORARY TABLESPACE tblspace-defn](创建系统默认临时表空间,不要DMS 性能差)
[WITH "comment-string"]]
[AUTOCONFIGURE [USING config-keyword value [{,config-keyword value}...]]
[APPLY {DB ONLY | DB AND DBM | NONE}]]
tblspace-defn:
MANAGED BY { SYSTEM USING ('string' [ {,'string'} ... ] ) |(使用SMS 指定 容器)
DATABASE USING ({FILE | DEVICE} 'string' number-of-pages(使用DMS 指定容器)
[ {,{FILE | DEVICE} 'string' number-of-pages} ... ]) |
AUTOMATIC STORAGE}(使用自动存储)
[EXTENTSIZE number-of-pages] (表空间盘区大小,如果不指定则使用上面默认的大小)
[PREFETCHSIZE number-of-pages](指定表空间预读大小,默认是DFT EXTENT SZ)
[OVERHEAD number-of-milliseconds](指磁盘寻道时间 默认7。5MS )
[TRANSFERRATE number-of-milliseconds](一个PAGE 读入内存时间 0。15 MS
[NO FILE SYSTEM CACHING |(不使用OS 的文件系统缓存)
FILE SYSTEM CACHING] (使用OS 的文件系统缓存)
[AUTORESIZE {NO | YES}](DMS和AUTOMATIC STORAGE 时是否在需要时自动调整表空间大小 )
[INITIALSIZE integer {K |M |G}](指定表空间初始大小)
[INCREASESIZE integer {PERCENT |K |M |G}(表空间一次增长的大小)
] [MAXSIZE {NONE | integer {K |M |G}}](表空间最大大小如果指定AUTORESIZE YES 则次参数为NULL)
config-keyword:
MEM_PERCENT, WORKLOAD_TYPE, NUM_STMTS, TPM, ADMIN_PRIORITY
NUM_LOCAL_APPS, NUM_REMOTE_APPS, ISOLATION, BP_RESIZEABLE.
db2 "create database testdb on /db2/database using codeset gbk territory cn collate using system pagesize 4096 dft_extent_sz 8 catalog tablespace managed by system using ('/db2/database/syscat') extentsize 16 PREFETCHSIZE 32 overhead 7.5 transferrate 0.18 no file system caching user tablespace managed by system using ('/db2/database/users') extentsize 16 PREFETCHSIZE 32 overhead 7.5 transferrate 0.18 no file system caching temporary tablespace managed by system using ('/db2/database/temp') extentsize 16 PREFETCHSIZE 32 overhead 7.5 transferrate 0.18 no file system caching"
db2 "create database db1 on /db2/db1/data1,/db2/db1/data2,/db2/db1/data3,/db2/db1/data4,/db2/db1/data5 dbpath on /db2/db1/master using codeset gbk territory cn"
Examples:
CREATE DATABASE TESTDB1
- Automatic storage enabled: Yes
- Database and Storage path: dftdbpath
CREATE DATABASE TESTDB2
ON /testdb2
- Automatic storage enabled: Yes
- Database and Storage path: /testdb2
CREATE DATABASE TESTDB3
AUTOMATIC STORAGE YES
- Automatic storage enabled: Yes
- Database path: dftdbpath
- Storage path: dftdbpath
CREATE DATABASE TESTDB4
AUTOMATIC STORAGE YES ON /dbdir
- Automatic storage enabled: Yes
- Database path: /dbdir
- Storage path: /dbdir
CREATE DATABASE TESTDB5
ON /db2/dir1,/db2/dir2,/db2/dir3
- Automatic storage enabled: Yes
- Database path: /db2/dir1
-Storage paths: /db2/dir1, /db2/dir2,
-/db2/dir3
CREATE DATABASE TESTDB6
ON D: AS_PATH DBPATH ON C:
- Automatic storage enabled: Yes
- Database path: C:
- Storage path: D:AS_PATH
创建BUFFERPOOL同样需要SYSADM和SYSCTRL权限。
db2 "create bufferpool bp8k immediate size 1000 automatic pagesize 8k"
NUMBLOCKPAGES
BLOCKSIZE
db2 "create user temporary tablespace utmp1 pagesize 8k bufferpool bp8k"
db2 "alter tablespace utmp1 bufferpool bp8k"
If you do not specify any table space parameters on the CREATE DATABASE command,
the database manager will create DMS table spaces for SYSCATSPACE and
USERSPACE1, and SMS table space for TEMPSPACE1. The default extent size for
SYSCATSPACE is 4 pages, and 32 pages for TEMPSPACE1 and USERSPACE1.
Table space and containers
1. SMS database manager manages data using operating system
操作系统负责管理和分配表的空间的使用,存储的方式通常是由很多的文件组成,每一个文件代表一个表的对象,用户可以觉得对象的存储位置(容器位置)DB2控制对象的名称,操作系统负责管理他们,database manager控制数据均匀的写入每一个表空间的容器,默认的数据库在创建过程中初始的表空间是SMS。
2. DMS managed by database manager directly
Database manager 控制存储空间,存储模式由少数的设备或文件组成,这些文件或设备的空间由DB2控制,database manager 决定那个设备或文件被使用,表空间本质上是被database manager使用的最佳的另一种文件系统。
可以创建DMS和SMS 2种tablespace 中的任意一种或2种的组合。
在DB2 V9 中automatic storage auto grow 是默认的。
表空间自动增长:
表空间自动增长发生在(DMS和automatic storage 中)
当表空间变满或者更多的空间学要时,它会自动增长,但仅仅是表空间容器中后面的部分进行增长,这样就避免了,表空间增长过程中发生rebalance。
增长在如下情况下停止:
表空间的大小达到了MAXSIZE指定的大小。
或者达到了设备或文件的最大值
表空间中有一个容器不能继续增长
在DB2 V9中的默认3个表空间
The default page size for these table spaces is 4 K.
Default storage paths (Windows):
• Syscatspace — C:DB2NODE0000SAMPLET0000000 (DMS-file)
• Userspace1 — C:DB2NODE0000SAMPLET0000002 (DMS-file)
• Tempspace1 — C:DB2NODE0000SAMPLET0000001 (SMS-folder)
Dft_extent_sz 在创建数据库的时候指定的默认的表空间的盘区大小,也就是表空间的容器的自动扩展的大小。
Extentsize 是在创建表空间的时候指定的盘区大小,如果不指定则使用dft_extnt_sz(默认32个PAGES范围是2-256)
数据写入表空间容器是以循环的方式写入的。
Extent(盘区)是在表空间容器上分配的连续的空间,盘区是一个单一的数据库对象,它有多个连续的PAGES组成,默认的盘区大小是32个PAGES一旦指定此参数则不能进行修改。
The database manager will try to evenly distribute the table among containers. In doing so,
the database manager writes an extent of pages to each container before writing to the
next container. Once the database manager has written an extent to all the containers
allocated to a table space, it will write the next extent to the first container written to in that
table space. This round-robin process of writing to the containers is designed to balance
the workload across the containers of the table space.
Table space container extent pages
表空间由容器组成,容器由盘区组成,盘区由页组成。
表空间盘区的分配:
创建表空间后:
每一个CONTAINER会分配1个EXTENT作为 CONTAINER TAG
每个表空间会分配1个EXTENT 作为表空间头
1个EXTENT 作为表空间空间地图
1个EXTENT 作为object table data
如果表空间上不创建对象,最小就这些空间拉。下面看个例子:
Tablespace name = TBS3
Tablespace ID = 5
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Large table space.
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 8
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
Auto-resize enabled = Yes
File system caching = No
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Tablespace Prefetch size (pages) = 40
Total number of pages = 25600
Number of usable pages = 25560
Number of used pages = 24
Number of pending free pages = 0
Number of free pages = 25536
High water mark (pages) = 24
表空间未创建任何对象 初始分了3个EXTENTS
Container Name = /db2/db1/data5/db2inst/NODE0000/DB1/T0000005/C0000000.LRG
Container ID = 0
Container Type = File (extent sized tag)
Total Pages in Container = 5120
Usable Pages in Container = 5112
Stripe Set = 0
Container is accessible = Yes
Container Name = /db2/db1/data4/db2inst/NODE0000/DB1/T0000005/C0000001.LRG
Container ID = 1
Container Type = File (extent sized tag)
Total Pages in Container = 5120
Usable Pages in Container = 5112
Stripe Set = 0
Container is accessible = Yes
Container Name = /db2/db1/data3/db2inst/NODE0000/DB1/T0000005/C0000002.LRG
Container ID = 2
Container Type = File (extent sized tag)
Total Pages in Container = 5120
Usable Pages in Container = 5112
Stripe Set = 0
Container is accessible = Yes
Container Name = /db2/db1/data2/db2inst/NODE0000/DB1/T0000005/C0000003.LRG
Container ID = 3
Container Type = File (extent sized tag)
Total Pages in Container = 5120
Usable Pages in Container = 5112
Stripe Set = 0
Container is accessible = Yes
Container Name = /db2/db1/data1/db2inst/NODE0000/DB1/T0000005/C0000004.LRG
Container ID = 4
Container Type = File (extent sized tag)
Total Pages in Container = 5120
Usable Pages in Container = 5112
Stripe Set = 0
Container is accessible = Yes
每个CONTAINER 上分了1个EXTENT
当在表空间上创建对象以后:
每个表空间上会额外增加2个EXTENTS:
1个EXTENT 存放 extent map
1个EXTENT存放extent for data
例如:
[db2inst@oracle ~]$ db2 "create table test (id int) in tbs3"
DB20000I The SQL command completed successfully.
Tablespace name = TBS3
Tablespace ID = 5
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Large table space.
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 8
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
Auto-resize enabled = Yes
File system caching = No
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Tablespace Prefetch size (pages) = 40
Total number of pages = 25600
Number of usable pages = 25560
Number of used pages = 40
Number of pending free pages = 0
Number of free pages = 25520
High water mark (pages) = 40
Once DB2 removes the one extent per container for the container tag, it will make
the number of usable pages a multiple of the extent size. So, for optimal space utilization,
allocate an even multiple of the extent size in pages in each container so no space is
wasted.
创建表空间:
需要有SYSADM 和SYSCTRL 的权限:
>>-CREATE--+-----------------------+---------------------------->
+-LARGE-----------------+ (存储LOB,或INDEX,DMS TBALESPACE指定次参数,在DB2 V9中对于LARGE RID的支持使得 一般对象可以存储在LARGE TABLESPACE中)
+-REGULAR---------------+ (存储一般数据)
| .-SYSTEM-. |(database manager 执行排序或连接时使用,每个数据库至少有一个SYSTEM TEMPORARY TABLESPACE,TERPORARY TABLE 可以放到此表空间中,如果有多个TEMPORARY TABLESPACE 系统将采用循环的方式使用)
'-+--------+--TEMPORARY-'
'-USER---'(用来存储用户定义的临时表)
>--TABLESPACE--tablespace-name---------------------------------->
>--+-----------------------------------------------------------+-->
| .-DATABASE PARTITION GROUP-. |
'-IN--+--------------------------+--db-partition-group-name-'
>--+--------------------------+--------------------------------->
'-PAGESIZE--integer--+---+-'(用来定义表空间使用的叶的大小,4K,8K,16K,32K可用)
'-K-'
.-MANAGED BY--AUTOMATIC STORAGE--| size-attributes |---------------------.
>--+------------------------------------------------------------------------+-->
'-MANAGED BY--+-SYSTEM--| system-containers |--------------------------+-'
'-DATABASE--| database-containers |--| size-attributes |-'
>--+---------------------------------+-------------------------->
'-EXTENTSIZE--+-number-of-pages-+-'(指定盘区大小,即一次写入CONTAINER的大小,写完后写下一个CONTAINER,循环方式,不指定将按DFT_EXTNET_SZ大小写)
'-integer--+-K-+--'
'-M-'
>--+-----------------------------------+------------------------>
'-PREFETCHSIZE--+-AUTOMATIC-------+-'(指查询预取页大小,默认是DFT_EXTENT_SZ 大小)
+-number-of-pages-+
'-integer--+-K-+--'
+-M-+
'-G-'
>--+-----------------------------+------------------------------>
'-BUFFERPOOL--bufferpool-name-' (指定BUFFERPOOL,如果不指定则使用IBMDEFAULTBP,注意和PAGESIZE 一样)
>--+----------------------------------+------------------------->
'-OVERHEAD--number-of-milliseconds-'(指定磁盘寻道时间7。5MS)
>--+------------------------+----------------------------------->
+-NO FILE SYSTEM CACHING-+
'-FILE SYSTEM CACHING----'
>--+--------------------------------------+--------------------->
'-TRANSFERRATE--number-of-milliseconds-'(把一个PAGE读入内存的速度0。12MS)
>--+---------------------------------+-------------------------><
'-DROPPED TABLE RECOVERY--+-ON--+-' (可以不用恢复整个数据库恢复表,可以恢复表空间来恢复表)
'-OFF-'
size-attributes
|--+---------------------+--+-----------------------------+----->
'-AUTORESIZE--+-NO--+-' '-INITIALSIZE--integer--+-K-+-'
'-YES-' +-M-+
'-G-'
>--+------------------------------------+----------------------->
'-INCREASESIZE--integer--+-PERCENT-+-'
'-+-K-+---'
+-M-+
'-G-'
>--+-----------------------------+------------------------------|
'-MAXSIZE--+-integer--+-K-+-+-'
| +-M-+ |
| '-G-' |
'-NONE-----------'
system-containers
.----------------------------------------------------------------------.
| .-,------------------. |
V V | |
|----USING--(----'container-string'-+--)--+-----------------------------+-+--|
'-| on-db-partitions-clause |-'
database-containers
.--------------------------------------------------------------.
V |
|----USING--| container-clause |--+-----------------------------+-+--|
'-| on-db-partitions-clause |-'
container-clause
.-,---------------------------------------------------.
V |
|--(----+-FILE---+--'container-string'--+-number-of-pages-+-+--)--|
'-DEVICE-' '-integer--+-K-+--'
+-M-+
'-G-'
on-db-partitions-clause
|--ON--+-DBPARTITIONNUM--+-------------------------------------->
'-DBPARTITIONNUMS-'
.-,--------------------------------------------------.
V |
>--(----db-partition-number1--+--------------------------+-+--)--|
'-TO--db-partition-number2-'
DB2 get snapshot for tablespaces on dbname
还可以使用snapcontainer的视图
Select * from sysibmadm.snapcontainer
Used with the SNAPTBSP, SNAPTBSP_PART, SNAPTBSP_QUIESCER and
SNAPTBSP_RANGE administrative views, the SNAPCONTAINER administrative view
returns data equivalent to the GET SNAPSHOT FOR TABLESPACES ON database-alias
CLP command.
Db2 list tablespaces show detail
Db2 list tablespace containers for tablespaceid show detail
打开MONITOR SWITCH
Instance level
Db2 update dbm cfg using dft_mon_bufpool on
Db2 get dbm monitor switches
Session level
Db2 get monitor switches
Db2 update monitor switches using bufferpool no
db2 reset monitor for database db1
db2 reset monitor all
修改表空间:
ALTER TABLESPACE tablespace-name
database-container-clause:
FILE
DEVICE
'container-string' number-of-pages ( )
,
all-container-clause:
ALL
CONTAINERS
number-of-pages
integer
( )
K
M
G
| database-container-clause | ADD
K|M|G
BUFFERPOOL
OVERHEAD
TRANSFERRATE
bufferpool-name
number-of-milliseconds
number-of-milliseconds
PREFETCHSIZE number-of-pages
integer
EXTEND
RESIZE
REDUCE
| database-container-clause |
| all-container-clause |
| database-container-clause | DROP
修改容器的大小属性只能是DMS
1。ADD 在添加完CONTAINER后系统会自动最表空间进行REBALANCE因此尽量一次加完容器,避免不必要的REBALANCE。
db2 "alter tablespace tbs5 add (file '/db2/db2/tbs5_1/tbs5_02.dbf' 1000) "
2.DROP 删除CONTANER
db2 "alter tablespace tbs5 drop (file '/db2/db2/tbs5_1/tbs5_02.dbf' ) "
1. Extend 增加容器大小
db2 "alter tablespace tbs5 extend (all 3000)"
2. Reduce 减少容器大小
db2 "alter tablespace tbs5 reduce (all 1000)"
5.RESIZE 从定义大小
db2 "alter tablespace tbs5 resize (file '/db2/db2/tbs5_1/tbs5_02.dbf' 2000) "
The RENAME TABLESPACE statement allows you to rename an existing table space.
You can turn AUTORESIZE off by specifying AUTORESIZE NO in the ALTER
TABLESPACE command.
This specifies that the auto-resize capability of a DMS table space or an automatic storage
table space is to be disabled. If the auto-resize capability is disabled, any values that have
been previously specified for INCREASESIZE or MAXSIZE will not be kept.
db2 "rename tablespace tbs5 to tbs5_1"
db2 "alter tablespace tbs1 autoresize no"
数据库配置文件:
Db2 get db cfg show detail
Db2 update db cfg using *** **
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8188536/viewspace-1027580/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8188536/viewspace-1027580/