六,创建数据库
创建数据库时,完成如下任务:
* 设置数据库所需的所有系统目录表
* 分配数据库恢复日志
* 创建数据库配置文件,设置缺省值
* 将数据库实用程序与数据库绑定
初始数据库分区组的定义
当最初创建数据库时,会为所有在db2nodes.cfg文件中指定的分区创建数据库分区.可以用
add dbpartitionnum 和 drop dbpartitionnum verify 命令来添加或除去其他分区.
定义了三个数据库分区组:
* 用于容纳syscatspace表空间的ibmcatgroup, 保存系统目录表
* 用于容纳tempspaces1表空间的ibmtempgroup,保存系统临时表
* 用于容纳userspace1表空间的ibmdefaultgroup,缺省保存用户表和索引
(创建新的数据库后最好重新启动db2clp)
EXAMPLE:
CREATE DATABASE PERSONL
CATALOG TABLESPACE
MANAGED BY SYSTEM USING (path 'D:\PCATALOG','E:\PCATALOG')
EXTENTSIZE 16 PREFETCHSIZE 32
USER TABLESPACE
MANAGED BY DATABASE USING (FILE 'D:\DB2DATA\PERSION1' 5000,
FILE 'D:\DB2DATA\PERSION2' 5000)
EXTENSIZE 32 PREFETCHSIZE 64
TEMPORARY TABLESPACE
MANAGED BY SYSTEM USING (path 'F:\DB2TEMP\PERSONL')
WITH "PERSONNEL DB FOR DSCHIEFER CO"
create database sccrm using codeset GBK territory CN
#删除数据库
DROP DATABASE <DB-NAME>
# 数据库目录的定义
*本地数据库目录(节点的目录中的文件SQLDBDIR)
本地数据库目录文件存在于定义了数据库的每条路径(或WIN中的"驱动器")
对于可以从该位置存取得每个数据库此目录都包含一个条目.包含信息:
数据库名称,数据库别名,数据库注释,数据库的根目录的名称,其他系统信息.
*系统数据库目录(实例中的目录中的文件SQLDBDIR)
对于数据库管理器的每个实例,都存在一个系统数据库目录文件,该文件对于针对
此实例编目的每个数据库都包含一个条目.使用CREATE DATABASE时隐式的编目数据库
每个数据库包含一条信息: 数据库名,数据库别名,数据库注释,本地数据库目录的位置,
指示该数据库是间接的指示符,表示它与系统数据库目录文件驻留在相同的机器上.
查看本地或系统数据库目录文件
LIST DATABASE DIRECTORY ON <location>
LIST DATABASE DIRECTORY
*节点目录
数据库管理器在编目第一个数据库分区时会创建节点目录.要编目数据库分区,
使用CATALOG NODE命令.要显示本地节点目录的内容,使用LIST NODE DIRECTORY
.在每个数据库客户机上都创建并维护节点目录.对于具有客户机可以存取得一个
或多个数据库的每个远程工作站,该目录都包含一个条目.db2客户机使用该节点目录中
的通信端点信息.
catalog tcpip node my_node_name remote 10.10.10.10 server 54321
uncatalog node my_node_name
catalog database DB as my_data_alias at node my_node_name
# "轻量级目录访问协议" (LDAP)目录服务
目录服务是一个关于分布式环境中的多个系统和服务的资源信息的资源库;它
提供对这些资源的客户机和服务器存取.客户机和服务器将使用目录服务来找出
如何存取其他资源.
LDAP是业界标准的存取目录服务的方法.每个数据库服务器实例都会将它的存在情况发布给LDAP,
并在创建数据库时向LDAP目录提供数据库信息.当客户机与数据库连接后,可从LDAP目录检索
服务器的目录信息.不再要求每个客户机将目录信息以本地方式存储在每台机器上.
# 创建数据库分区组
可以使用CREATE DATABASE PARTITION GROUP语句创建数据库分区组.此语句指定表空间
容器和表数据将驻留其上的一组数据库分区.
*为数据库分区组创建分区映象.
*生成分区映象标识
*将记录插入下列目录表:
SYSCAT.DBPARTITONGROUPS,SYSCAT.PARTITIONMAPS,SYSCAT.DBPARTITIONGROUPDEF
CREATE DATABASE PARTITION GROUP <name> ON DBPARTITIONNUMS (<value>,<value>)
#创建表空间
表空间建立数据库系统使用的物理存储设备与用来存储数据的逻辑容器或表的关系
CREATE TABLESPACE <name>
MANAGED BY SYSTEM
USING ('<path>')
CREATE TABLESPACE <name>
MANAGED BY DATABASE
USING (FILE'<path>' <size>)
DEVICE
*指定分区组
CREATE TABLESPACE PLANS IN ODDNODEGROUP
MANAGED BY DATABASE
USING (DEVICE '/dev/HDISK0' 10000,DEVICE '/dev/n1hd01' 40000) on node 1
(DEVICE '/dev/HDISK0' 10000,DEVICE '/dev/n1hd03' 40000) on node 3
(DEVICE '/dev/HDISK0' 10000,DEVICE '/dev/n1hd05' 40000) on node 5
CREATE REGULAR TABLESPACE CUSTTBS IN DATABASE PARTITION GROUP CUSTOMER
PAGESIZE 4 K MANAGED BY DATABASE USING
( FILE 'D:\testdbtbs\custtbs0_1' 5120 ) ON DBPARTITIONNUM (0)
USING
( FILE 'D:\testdbtbs\custtbs1_1' 5120 ) ON DBPARTITIONNUM (1)
EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16
在多分区数据库中创建单分区组.
CREATE DATABASE PARTITION GROUP single_tbs_grp ON DBPARTITIONNUM(0)
在单分区组中创建单分区表空间.
CREATE TABLESPACE single_part_tbs IN DATABASE PARTITION GROUP single_tbs_grp
MANAGED BY DATABASE USING (FILE 'd:\testdbtbs\single_part_tbs_1' 5120)
在UNIX中使用字符设备.
* 创建特定类型的表空间
创建系统临时表空间
CREATE SYSTEM TEMPORARY TABLESPACE tmp_tbsp
MANAGED BY SYSTEM
USING ('d:\tmp_tbsp','e:\tmp_tbsp')
在分区数据库中创建系统临时表空间 只能在IBMTEMPGROUP中产生
CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS_TBSP1
IN DATABASE PARTITION GROUP IBMTEMPGROUP
PAGESIZE 4 K MANAGED BY SYSTEM
USING ('D:\testdbtbs\sys_temp_0' ) ON DBPARTITIONNUM (0)
USING ('d:\testdbtbs\sys_temp_1' ) ON DBPARTITIONNUM (1)
EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16
创建用户临时表空间
CREATE USER TEMPORARY TABLESPACE usr_tbsp
MANAGED BY DATABASE
USING (FILE 'd:\db2data\user_tZbsp' 5000,
FILE 'e:\db2data\user_tbsp' 5000)
在分区数据库中创建用户临时表空间 可在除IBMTEMPGROUP中的其他分区组中产生.
CREATE USER TEMPORARY TABLESPACE USER_TEMP_TBSP
IN DATABASE PARTITION GROUP USER_TEMP_TBSGRP
PAGESIZE 4 K MANAGED BY SYSTEM
USING ('D:\testdbtbs\user_temp_0' ) ON DBPARTITIONNUM (0)
USING ('d:\testdbtbs\user_temp_1' ) ON DBPARTITIONNUM (1)
*指定物理设备
在WINDOWS上,指定物理硬盘驱动器,使用\\.\PhysicalDriveN (N-0,1,2..)
指定逻辑驱动器使用\\.\N: (N是系统中的逻辑盘符)
在UNIX中指定字符设备.
#SMS 当对象增大时，文件每次扩展一页。若需要提高插入性能，可以考虑启用多页分配，如MDC表。
运行db2empfa. 对于多分区数据库必须对每个分区运行此使用程序，一旦启用就不能禁止。
#DMS
单分区表空间大小，4kb - 64gb;8kb-128gb;16kb-256gb;32kb-512gb
在缺省情况下，每个容器都保留一个数据块作为开销，表空间的最小大小是5个数据块。
三个保留给开销使用。两个用于用户表数据。
＃在多个节点上产生表空间
CREATE TABLESPACE TS1 MANAGED BY DATABASE USING
(device '/dev/rcont $N' 20000)
CREATE TABLESPACE TS2 MANAGED BY DATABASE USING
(file '/DB2/containers/TS2/container $N+100' 10000)
CREATE TABLESPACE TS3 MANAGED BY SYSTEM USING
('/TS3/cont $N%2', '/TS3/cont $N%2+2')
#增加表空间的空间
ALTER TABLESPACE RESOURCE
ADD (DEVICE '/dev/rhd9' 10000,
DEVICE '/dev/rhd10' 10000)
#改变表空间状态
DB2 ALTER TABLESPACE <name> SWITCH ONLINE
#删除表空间
DROP TABLESPACE <name>
可以先增加一个系统临时表空间,然后删除老的.
#获取表空间使用信息
get snapshot for tablespaces on sample
＃获取表空间的CONTAINER
LIST TABLESPACE CONTAINERS FOR 0(TABLESPACE_ID) SHOW DETAIL
# 创建和填充表
列出表:
list tables － 列出当前用户的表
list tables for all － 列出数据库中所定义的所有表
list tables for schema schemaname － 列出具有指定模式的表
describe table tablename － 显示指定表的结构
产生表和主键
CREATE TABLE DEPARTMENT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(29) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16),
PRIMARY KEY (DEPTNO))
IN RESOURCE
产生外键
CREATE TABLE EMPLOYEE
(EMPNO CHAR(3) NOT NULL PRIMARY KEY,
FIRSTNME VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
PHOTO BLOB(10m) NOT NULL,
FOREIGN KEY DEPT (WORKDEPT)
REFERENCES DEPARTMENT ON DELETE NO ACTION)
IN RESOURCE
ON DELETE NO ACTION 表示如果该部门有任何雇员则该部门不能被删除.
定义表检查约束
在创建或改变表时,通过将检查约束定义与表关联来对该表创建表检查约束.
当INSERT或UPDATE语句修改该表中的数据时,就自动激活此约束.表检查约束对
DELETE或SELECT没有影响.检查约束不能与类型表相关.
约束名不能与在同一个CREATE TABLE语句内指定的任何其他约束相同.若不
指定约束名,系统会自动生成18个字符的唯一标识符.
表检查约束用于实现键唯一性或引用完整性约束所未涵盖的数据完整性规则.
如:
CREATE TABLE EMP_ACT
(EMPNO CHAR(6) NOT NULL,
PROJNO CHAR(6) NOT NULL,
ACTNO SMALLINT NOT NULL,
EMPTIME DECIMAL(5,2),
EMSTDATE DATE,
EMENDATE DATE,
CONSTRAINT ACTDATES CHECK(EMSTDATE <= EMENDATE) )
IN RESOURCE
约束EMSTDATE必须小于EMENDATE
定义信息性约束
信息性约束是一个规则,可由SQL编译器使用,但数据库管理器不会强制使用它.
SQL编译器包括一个重写查询阶段,它将SQL语句变换为可能是优化的格式并改进
所需数据的存取路径.目的是改进查询性能.
对新表定义生成列
生成列在基本表中定义,在这些列中,存储的值是使用表达式计算得出的,而不是
通过插入或更新操作指定.可以改善查询性能,特别是计算很复杂或在查询时要进行
多次表达式求值.
CREATE TABLE t1 (c1 INT,
c2 DOUBLE,
c3 DOUBLE GENERATED ALWAYS AS (c1 + c2)
c4 GENERATED ALWAYS AS
(CASE WHEN c1 > c2 THEN 1 ELSE NULL END))
创建用户定义临时表
临时表不出现在系统目录中,不能共享此表
DECLARE GLOBAL TEMPORARY TABLE gb1_temp
LIKE tmpltab1
ON COMMIT DELETE ROES
NOT LOGGED
IN usr_tbsp
定义此用户临时表所使用的列的名称和描述与empltabl的列的名称和描述完全相同.
隐式定义只包括列名,数据类型,可空性特征和列缺省值属性.未定义其他列属性,包括
唯一约束,外键约束,触发器和索引.
对新表定义身份列
为插入表的每一行自动生成保证唯一数字值的方法.
只在单分区数据库中支持.
CREATE TABLE test_table ( col1 int,
col2 double,
col3 int not null generated always as identity
(start with 100,increment by 5))
创建序列
序列是一个数据库对象,它允许自动生成值.序列特别适合于生成唯一键值.
与身份列属性不同,未使序列与特定表列相关,也未将它绑定至唯一列,只是仅可通过该表
列存取.只在单分区数据库中才受支持.
在多分区环境中的单分区数据库也不行.
CREATE SEQUENCE order_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 24
使用序列
insert into order (orderno,custno)
values (nextval for order_seq,123456);
insert into line_item (orderno,partno,quantity)
values (prevval for order_seq,987654,1)
使用NEXTVAL,PREVVAL可以在两个不同的表中使用相同的序列号.
对表定义维
引用完整性约束 第 13 页（共17 页）
引用完整性约束是在创建表时定义的，或者是在之后使用 alter table 语句定义的。
建立引用完整性的子句有：
primary key 子句
unique constraint 子句
foreign key 子句
references 子句
例如：
create table artists (artno INT, ... primary key (artno) foreign key dept (workdept)
references department on delete no action)
让我们了解一下各种引用完整性规则：
插入规则：
有一个隐式规则，在没有找到父项时取消插入。
删除规则：
Restrict：如果有从属行就不能删除父行。
Cascade：删除父表中的行会自动删除其从属表中的任何相关行。
No Action（缺省值）：在应用了所有其它引用约束之后强制每个子行的父行都存在。
Set Null：外键字段设置成 null；其它列保持不变。
更新规则：
Restrict：如果从属表中的行与键的初始值相匹配，则不更新父键。
No Action（缺省值）：如果从属表中没有任何行与父键相匹配，则不更新父键。
增加表中的列
ALTER TABLE EMPLOYEE
ADD <COLUMN_NAME> <DATA_TYPE> <NULL_ATTRIBUTE>
增加唯一性约束
ALTER TABLE EMPLOYEE
ADD CONSTRAINT NEWID UNIQUE(EMPNO,HIREDATE)
删除唯一性约束
ALTER TABLE EMPLOYEE
DROP UNIQUE NEWID
删除主键
ALTER TABLE <NAME>
DROP PRIMARY KEY
增加主键
ALTER TABLE <NAME> ADD PRIMARY KEY (COL1,COL2,..)
删除外键
ALTER TABLE <name>
DROP FOREIGN KEY <foreign_key_name>
删除表检查约束
ALTER TABLE <table_name>
DROP CHECK <check_constraint_name>
添加表的外键
ALTER TABLE <NAME>
ADD CONSTRAINT <constraint_NAME>
FOREIGN KEY <COLUMN_NAME>
references table_name <col_name>
ON DELETE <ACTION_TYPE>
ON UPDATE <ACTION_TYPE>
在多个表空间中创建表
表数据,表索引及与表相关联的任何长型列数据可以存储在同一表空间中,也可以
放在不同的表空间中.只能使用DMS.
CREATE TABLE <name>
(<column_name> <data_type> <null_attribute>)
IN <table_space_name>
INDEX IN <index_space_name>
LONG IN <long_space_name>
在分区数据库中创建表
必须小心的选择适当的分区建,以后不能跟改.再者,必须将任何唯一索引定义为分区键的一个超集.
表的大小是(分区数*分区大小(4K是64GB).
CREATE TABLE MIXREC (MIX_CNTL INTEGER NOT NULL,
MIX_DESC CHAR(20) NOT NULL,
MIX_INT INTEGER NOT NULL)
IN MIXTS12
PARTITIONING KEY (MIX_INT) USING HASHING
产生触发器
用途:
验证输入的数据
为新插入的行生成值
为交叉引用而从其他表中进行读取
为审计跟踪而向其他表写入
CREATE TRIGGER <name>
<action> ON <table_name>
<operation>
<triggered_action>
创建用户定义函数(UDF)或方法
UDF扩展并添加了SQL的内置函数提供的支持,且可在可使用内置函数的任何
地方使用.可使用两种方式创建UDF:
外部函数,用一种编程语言编写
有源函数,从另一个现有函数继承产生.
三种UDF:标量,返回一个单值答案.
列,从一组相似(一列)的值中返回单值答案,如AVG()只能定义有源函数.
表,将一个表返回至引用它的SQL,只能在select语句的from子句中引用表函数.
UDF 记录在SYSCAT.FUNCTIONS AND SYSCAT.FUNCPARMS目录视图中.
用户定义类UDT
UDT是由用户在数据库中创建的命名的数据类型.UDT可以是单值类型,它与内部数据类型或
结构化类型共享一个公共的表示法,结构化类型具有一个命名属性序列,其中每个属性都有一个类型.
结构化类型可以是另一个定义类型层次结构的结构化类型的子类型.
创建视图
CREATE VIEW <name> (<column>,<column>,<column>)
SELECT <column_names> FROM <table_name>
WITH CHECK OPTION
WITH CHECK OPTION 子句指示必须根据该视图定义检查该视图的任何更新的行或插入的行,
如它不符合,则拒绝它,增加了数据完整性.
CREATE VIEW EMP_VIEW
SELECT LASTNAME AS DA00NAME,
EMPNO AS DA00NUM,
PHONENO
FROM EMPLOYEE
WHERE WORKDEPT = 'A00'
WITH CHECK OPTION
创建具体查询表
具体查询表是以查询结果为基础所定义的一种表.因此,具体查询表通常包含预先计算的结果,
这些结果是根据表定义中引用的一个或多个表中的现有数据计算而得.若SQL编译器确定查询
在针对具体查询表运行时比对一个或多个基本表运行时效率更高,将对具体查询表执行该查询.
创建别名
别名是引用表,别名或视图的间接方法,这样SQL语句可与该表或视图的限定名无关.
CREATE ALIAS WORKERS FOR EMPLOYEE
索引,索引扩展或索引规范
索引是行位置的列表,按一个或多个指定列的内容来排序.
索引扩展是一个索引对象,它配合带有结构化类型或单值类型列的索引使用.
索引规范是一个元数据结构.它告诉优化器别名所引用的数据源对象(表或视图)
是否存在索引.只是索引的描述.
索引顾问,,db2advis
索引得最大列数是16,最大长度是1024字节.
CREATE INDEX <name> ON <talbe_name> (<column_name>)
重命名表或索引
要重命名的表或索引不能是目录表或索引,总结表或索引,类型表,已声明
全局临时表以及昵称的名称.
并且不能在下列任何一个对象中引用现有表或索引:
视图,触发器,引用约束,总结表,现有引用列的作用域
表中不能有检查约束,不能有除身份列以外的其他生成列.
RENAME TABLE <SCHEMA_NAME>.<TABLE_NAME> TO <NEW_NAME>
RENAME INDEX <schema_name>.<index_name> TO <new_name>
索引可以：
是升序或是降序（如果没有指定，缺省值是升序）
是唯一的，也可以是不唯一的（如果没有指定，缺省值是不唯一的）
是复合的
用来执行群集
是双向的（这由 allow 或 disallow reverse scans 控制的）
包括其它列（这只适用于唯一索引）。
删除表
DROP TABLE <TABLE_NAME>
删除索引
DROP INDEX <index_name>
通过命令行处理器调用"性能配置向导"
使用AUTOCONFIGURE
终止所有应用程序与数据库的连接
db2 force applicaitons all
#给用户授权
GRANT privilege ON object-type object-name
TO [{USER | GROUP | PUBLIC}] authorization-name
[WITH GRANT OPTION]
GRANT INSERT,DELETE ON TABLE staff TO USER rosita WITH GRANT OPTION
#撤消用户权利
REVOKE privilege ON object-type object-name
FROM [{USER | GROUP | PUBLIC}] authorization-name
REVOKE ALL PRIVILEGES ON TABLE staff FROM joanna