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

发布时间:2019-09-24编辑:脚本学堂
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