DB2实验教程：创建数据库对象

实验任务：
A.建立表；
B. 建立索引；
C. 建立视图；
D.建立别名；
E. 对其中一表添加参照完整性约束；
F. 对其中一表添加检查约束；
G.对其中一表添加触发器；
H.访问这些对象相关的系统编目信息。

一. 创建表
1.利用向导创建表artists。
(1)在控制中心刷新表空间，并确保在开始创建表之前，能看到所有的表空间。
(2)在控制中心的MUSICDB数据库对象列表中，右键单击“表”，并在弹出菜单中选择“创建”，则弹出“创建表向导”对话框。
(3)在“创建表向导”中，在“表名”处输入artists，然后单击“下一步”定义列。
   在列定义窗口中，点击“添加”按钮,在“添加列”对话框中，输入列名artno，在数据类型中，选择SMALLINT，去掉“可空”前面的选择(该列不能为空)，点击“应用”按钮，继续定义其它列。
   定义列name，选择数据类型为VARCHAR，长度为50，允许为空；
   定义classification列，数据类型为CHARACTER，长度为1,不允许为空；
   定义bio列，数据类型CLOB，LOB单位选择KBytes,长度为100。LOB选项的“记录”和“压缩”均选上，并选择“可空”；
   定义picture列。数据类型为BLOB,LOB单位为Kbytes,长度为500。选择LOB选项的“压缩”选项，选择“可空”。
(4)在继续下面操作前，要确保上述定义的列具有下列的性质。如果没有，则用“更改”按钮进行更改（此处定义错误，可能会使后续结果不正确）。
Column name  Data type     Length     Nullable     LOB option
-----------  ---------   ----------  ------------------
artno  smallintNo
name   varchar 50     Yes
classificationcharacter1No
bio    clob   100KBytes     YesLogged,Compact
pictureblob  500 KBytes     YesCompact
(5)按“下一步”进入表空间窗体，为artists表定义表空间。
在表空间处，选择DMS01表空间；选择“使用单独的索引空间”，并选择DMS02表空间；选择“使用独立的长空间”并选择DMS03表空间。然后单击“下一步”，在新表上定义键。
(6)按“添加主键”，在可用列中，选择ARTNO列，并按下 > 按钮，将ARTNO加入到“选择的列”中（只有非空列才被选择，所以，主键必须被定义为非空）。
(7)在“总结”窗口，按下“显示SQL”，则会出现如下的SQL语句。其中，约束名会有所不同。
CREATE TABLE INST1.ARTISTS ( ARTNO SMALLINT NOT NULL , NAME VARCHAR (50) , CLASSIFICATION CHARACTER (1) NOT NULL , BIO CLOB (100 K ) LOGGED COMPACT ,
PICTURE BLOB (500 K ) NOT LOGGED COMPACT ,
CONSTRAINT CC1044760857306 PRIMARY KEY ( ARTNO) ) IN "DMS01" INDEX IN
"DMS02" LONG IN "DMS03" ;
(8)按“完成”创建该表。
2.利用Create Table语句创建表albums。
在“命令中心”中输入下列语句，创建数据表stock（需要先连接到数据库，即connect to musicdb）。
create table albums
 (title     varchar (50),
   artno     smallint not null,
   itemno    smallint not null)
   in dms04
   index in dms05;
 
在控制中心左边的对象树中右键单击MUSICDB下的“表”，在弹出菜单中选择“刷新”，再在右边的内容面板中找到artists表和albums表，查看这两个表。如果表不符合要求，则将其删除，然后再返回上面步骤重新建立。
3.导入脚本文件创建其余表。
在“命令中心”的脚本模式下导入CRTABLES的脚本文件创建其余表，在执行它之前，先查看脚本内容，注意表STOCK、CONCERTS、REORDER将分别把表数据和索引放在哪些表空间。
按Ctrl+Enter执行脚本文件。
4.查看所建表的信息。
在控制中心左边的对象树中右键单击MUSICDB下的“表”，在弹出菜单中选择“刷新”，然后再在右边内容面板中查看脚本所创建的表，确保这些表都建立得正确。
要查看表的详细信息，可以在内容面板中右键单击所要查看的表，在弹出菜单中选择“改变”。使用此方法查看albums表。
5.通过在命令中心中导入执行GRANTS脚本，将inst1模式下所有表的select权限授予public，并在脚本执行完毕后检查权限授予是否成功。
注意查看该脚本的具体内容，以及相应的SQL语句的写法。
6.查看模式inst1所有表的列信息（选做）。
(1)表的列信息可以通过SYSCAT.COLUMNS视图查询到。在DB2命令行处理器输入如下指令执行X：cf23下的tabchk.sql脚本，并将输出结果重定向到tabchk.file中。
打开“开始菜单—>程序—>IBM DB2—>命令行工具—>命令窗口”，输入：
X：
Cd cf23（数据文件所在目录）
db2 connect to musicdb
db2 –tf tabchk.sql > tabchk.file
more tabchk.file
或者输入 db2 select tabname， colname， typename from syscat.columns where tabschema = 'inst1' order by 1, 2 > tabchk.file
(2)检查输出文件tabchk.file与 tabchk.master内容上的差别（须确保两文件的内容没有任何差别）。首先在命令提示符下输入指令：
fc /w tabchk.file tabchk.master | more
如果输出消息显示“FC：找不到相异处”，tabchk.file与tabchk.master相一致。如果输出显示两个文件某些行不相同，则应仔细检查相应错误，删除建立不当的表，并返回到上步重新建立。
7.查看模式inst1表空间的信息（选做）。
(1)某个表所相关的表空间信息可以通过SYSCAT.TABLES视图来获得。输入一条SQL语句来从SYSCAT.TABLES获取模式inst1下所有表的表名（TABNAME）、存储该表数据的表空间（TBSPACE）、存储该表索引的表空间（INDEX_TBSPACE）信息，并将结果集按表名排序。完整的SQL语句为：
select tabname， tbspace， index_tbspace from syscat.tables where tabschema= 'inst1' order by tabname
X：cf23下的脚本文件tbschk.sql包含该SQL语句，在DB2命令窗口中进入x：cf23目录下，输入：
db2 –tf tbschk.sql > tbschk.file
more tbschk.file
(2)检查输出文件tbschk.file与 tbschk.master内容上的差别。
fc /w tbschk.file tbschk.master | more
如果输出消息显示“FC：找不到相异处”，tbschk.file与tbschk.master相一致。
如果输出显示两个文件某些行不相同，则应仔细检查相应错误，删除建立不当的表，并返回到上步重新建立。

二. 创建索引
1.在stock表的itemno列上建立名为item的索引。
控制中心的对象列表中右键单击MUSICDB下的“索引”，并在弹出菜单中选择“创建”，在“创建索引”窗口中按下列要求填入相关信息。其中，索引模式为inst1；索引名为item；表模式为inst1；表名为stock。
在“可用的列”中选择itemno并单击“>”按钮将该列加入到“选择的列”中，然后点击“确定”按钮。
2.为albums表的itemno列建立唯一索引。其中，索引模式为inst1；索引名为itemno；表模式为inst1；表名为albums；
在“可用的列”中选择itemno并单击“>”按钮将该列加入到“选择的列”中，并且选中“唯一”复选框，然后点击“确定”按钮。
 
有关索引的信息可以通过SYSCAT.INDEXES视图获取。

三. 建立视图
1.在控制中心中建立一名为music的视图。
控制中心的对象列表中右键单击MUSICDB下的“视图”，在弹出菜单中选择“创建”；
选择视图的模式为inst1；
视图名一栏中输入music；
点击SQL语句框右边的“清除”按钮，并输入：
as select title, classification, name from inst1.albums, inst1.artists where inst1.artists.artno= inst1.albums.artno ；
点击“确定”按钮。
2.利用Create View语句创建视图inventory。
在“命令中心”中，输入下列语句，建立视图inventory。
connect to musicdb;
create view inventory (type, itemno, totcost, totqty)
   as select type, itemno, sum (price * qty), sum(qty)
from stock group by type, itemno;
3.查看视图信息
(1)在控制中心的对象树中右键单击MUSICDB下的“视图”，并在弹出菜单中选择“刷新”，然后可见在右边的内容面板中列出了新建立的视图。
(2)视图的相关信息可以通过SYSCAT.VIEWS和SYSCAT.TABLES来获取。List tables同样可以列出数据库中的视图（类型为V）。
在“命令中心”中输入如下命令，查看新建立的视图是否列出：
connect to MUSICDB；
list tables for user；
或者connect to MUSICDB；
select viewschema, viewname from syscat.views where definer=user；

四. 建立别名
1.在控制中心中为artists表建立别名singers，为reorder表建立别名emptystock。
2.别名的相关信息可以通过视图SYSCAT.TABLES来获取，此外list tables命令也能获取当前连接的用户下的别名列表。
输入如下命令列出别名列表：
connect to MUSICDB；
list tables for user；
或者select tabname, type from syscat.tables where tabschema=user；

五. 添加参照完整性约束条件
本部分实验在表artists与albums之间以及albums与stock之间添加参照完整性约束。
1.修改albums表，并且为它定义与表artists的参照完整性约束。
(1)在该表itemno列上建立主键。
在控制中心中右键点击albums表并在弹出菜单中选择“改变”，再在“改变表—albums”窗口中选择“键”标签页，点击“添加主键”按钮，在“定义主键”窗口“可用的列”中选择itemno，然后点击“>”按钮，并点击“确定”按钮。
(2)为该表定以外键。
点击“添加外键”按钮，在“添加外键”窗口中，表模式一栏选择inst1，表名一栏选择artists，注意这时主键框内显示该表主键为artno，再选择“可用的”列中artno，点击“>”按钮使该列成为外键，选择“删除时”一栏为CASCADE，约束名为fkartno，点击“确定”按钮。
2.在命令中心脚本模式下执行CRRI脚本，该脚本修改STOCK表并为其定义参照完整性约束。（在运行脚本前，请仔细查看相关SQL语句的书写方法。）
3.通过SYSCAT.REFERENCES视图查看参照完整性约束的相关信息（选做）。
在命令中心中输入如下SQL语句获取inst1模式下所有参照完整性约束的名称、所在表名、参照表的模式、参照表名、删除规则、外键所在列名、主键所在列名：
select constname, tabname, reftabschema, reftabname, deleterule, fk_colnames, pk_colnames
 from syscat.references
 where tabschema='inst1'
4.通过SYSCAT.TABLES视图查看参照完整性约束的相关信息（选做）。
在命令中心中输入如下SQL语句，其中CHILDREN为参照该表的所有表的个数，PARENTS为该表所要参照的表的个数：
   select tabname, parents, children
from syscat.tables
where tabschema='inst1' and (parents>0 or children>0) order by 2，3 desc
5.参照完整性约束定义结果检查（选做）
在DB2命令窗口中执行脚本richk.sql，通过SYSCAT.TABLES视图获得当前连接的用户模式参照完整性约束的相关信息，richk.sql的内容为：
select substr(tabname, 1, 18), parents, children
from syscat.tables
where tabschema = user order by tabname
 
db2 –tf richk.sql > richk.file
more richk.file
 
然后检查该输出文件的内容与richk.master有无差别：
fc /w richk.file richk.master | more
如果输出显示两文件内容存在差异，则应仔细检查相关信息后重新修改表。

六. 为stock表添加检查约束
1.修改stock表并为该表添加检查约束cctype。
该约束确保stock表type字段的取值必须是字符'D'、'C'、'R'之一。
在控制中心中右键单击stock表，在弹出菜单中选择“改变”，在“改变表—stock”窗口中选择“检查约束”标签页，点击“添加”按钮，再在检查条件框中输入：
type in ('D', 'C', 'R')
约束名一栏输入cctype，点击“确定”按钮关闭“添加检查约束”窗口，再点击“确定”按钮关闭“改变表—stock”窗口。
2.检查约束的相关信息可通过SYSCAT.CHECKS、SYSCAT.COLCHECKS、SYSCAT.TABCONST和SYSCAT.TABLES这些视图获取。
在命令中心中输入：
select constname, tabname, colname from syscat.colchecks
3.在命令中心中输入如下SQL语句，列出的约束中，TYPE为K表示是一个检查约束，TYPE为P表示是一主键，TYPE为F表示是一外键
select constname, tabname, type from syscat.tabconst
4.DB2命令窗口中输入如下命令执行脚本ckchk.sql，并将输出结果重定向到ckchk.file：
Db2 –tf ckchk.sql > ckchk.file
More ckchk.file
然后检查该输出文件的内容与ckchk.master有无差别：
fc /w ckchk.file ckchk.master | more

七. 为 reorder表创建触发器，当stock表某一存货少于6时，reorder表中将会插入一条新记录(选做)
1.将要建立的触发器应具备下列性质：
触发器名为reorder；
触发时机为更新stock表某些记录的qty字段值后该字段值小于或等于5；
新记录变量new通过n来引用；
该触发器将新记录变量的itemno值和当前时间截插入到reorder表；
应为for each row mode db2sql触发器。
右键单击控制中心MUSICDB下的“触发器”，在弹出菜单中选择“创建”，在创建触发器窗口中，输入触发器模式选为inst1，表或视图模式选为inst1，触发器名为reorder，表或视图名称选为stock，触发操作的时间选为“之后”，导致执行触发器的操作设为“更新列”并选择qty列，然后选择“触发操作”标签页，在“新行的相关名”中输入n，在触发操作框内填入如下SQL语句：
when (n.qty<=5)
insert into reorder values (n.itemno, current timestamp)
点击“显示SQL”按钮查看完整的SQL语句。
点击“确定”按钮，然后在控制中心查看该触发器是否列出。
2.触发器的相关信息可以通过SYSCAT.TRIGGERS和SYSCAT.TRIGDEP获取。
在命令中心中输入如下SQL语句：
select trigname， tabname，trigevent from syscat.triggers
其中trigevent为I表示触发操作为insert，D表示触发操作为delete，U表示触发操作为update。
在命令中心中输入下列SQL语句：
select trigname, btype, bschema, bname from syscat.trigdep

您可能感兴趣的文章：
DB2常用知识
db2常用命令大全-基础篇
DB2实验教程：DB2实验环境设置
DB2基础知识(2)DB2数据库的安装
DB2实验教程-管理权限
收集了一些常用的DB2命令(附例子)
DB2 V9.7 Linux安装记录
db2和mysql的区别是什么
JSP连接DB2数据库的一段代码
DB2基础知识(1)了解DB2