DB2大型数据库创建数据库对象：表、索引、视图

实验目标：

这个演示将在名为MUSICDB的DB2数据库中创建一组数据库对象。

1、使用数据服务管理器构建Create TABLE语句或在文件中使用保存的DDL创建一组表。
2、使用Create INDEX语句为表创建索引。
3、在文件中使用SQL语句创建视图和别名对象。
4、在文件中使用SQL语句创建外键并检查表的约束。
5、使用db2look实用程序从DB2数据库中提取数据库对象定义。

---

创建表

首先切换到ddl脚本所在的目录，并连接到数据库

cd /home/inst23/ddl

db2 connect to musicdb

 

create_table_albums.ddl

create table music.albums 
 (title     varchar (50), 
  artno     smallint not null, 
  itemno    smallint not null) 
  in tsp04 
  index in tsp05;

alter table music.albums primary key (itemno) ;

运行代码：

db2 -tvf create_table_albums.ddl

输出：

create table music.albums(titleDB20000I The SQL command completed successfully.
varchar(50)，artno

alter table music.albums primary key(itemno)DB20000I The SQL command completed successfully.

查看music模式的albums表的结构描述：

db2 describe table music.albums

输出：

                                Data type                     Column

Column name                     schema    Data type name      Length     Scale Nulls

------------------------------- --------- ------------------- ---------- ----- ------

TITLE                           SYSIBM    VARCHAR                     50     0 Yes   

ARTNO                           SYSIBM    SMALLINT                     2     0 No    

ITEMNO                          SYSIBM    SMALLINT                     2     0 No    

  3 record(s) selected.

create_tables.ddl

create table music.albums 
 (title     varchar (50), 
  artno     smallint not null, 
  itemno    smallint not null) 
  in tsp04 
  index in tsp05;

alter table music.albums primary key (itemno) ;

ubuntu@VM-4-2-ubuntu:/home/inst13$ sudo cat /home/inst23/ddl/
cat: /home/inst23/ddl/: Is a directory
ubuntu@VM-4-2-ubuntu:/home/inst13$ sudo cat /home/inst23/ddl/create_tables.ddl



create table MUSIC.concerts 
 (artno      smallint not null, 
  date       date not null, 
  city       varchar (25) not null with default) 
  in tsp04;

create table MUSIC.reorder 
  (itemno       smallint  not null,  
   timestamp    timestamp) 
   in TSP02;

create table MUSIC.artists 
  (artno     smallint not null, 
   name      varchar(50), 
   classification char(1) not null, 
   bio       clob(100K) logged compact, 
   picture   blob(500K) not logged compact, 
   primary key (artno)) 
   in tsp01 
   index in tsp02 
   long in tsp03 ;

create table MUSIC.stock 
 (ITEMNO SMALLINT NOT NULL ,
    TYPE CHAR(1) NOT NULL ,
    PRICE DECIMAL(5,2) NOT NULL WITH DEFAULT  ,
    QTY INTEGER NOT NULL WITH DEFAULT,  
    SYS_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN IMPLICITLY HIDDEN, 
    SYS_END TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END IMPLICITLY HIDDEN,
    TX_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN,                    
    PERIOD SYSTEM_TIME (SYS_START,SYS_END) ) 
  in tsp06;

CREATE TABLE MUSIC.STOCK_HISTORY LIKE MUSIC.STOCK IN tsp06 ;

ALTER TABLE MUSIC.STOCK ADD VERSIONING USE HISTORY TABLE MUSIC.STOCK_HISTORY ;

运行代码：

db2 -tvf /home/inst23/ddl/create_tables.ddl | more

输出结果基本都是DB20000I The SQL command completed successfully.

列出music模式的所有表：

db2 list tables for schema music

输出：

Table/View                      Schema          Type  Creation time             

------------------------------- --------------- ----- --------------------------

ALBUMS                          MUSIC           T     2024-05-19-16.48.22.912328

ARTISTS                         MUSIC           T     2024-05-19-16.51.11.729939

CONCERTS                        MUSIC           T     2024-05-19-16.51.11.636145

REORDER                         MUSIC           T     2024-05-19-16.51.11.683649

STOCK                           MUSIC           T     2024-05-19-16.51.11.911520

STOCK_HISTORY                   MUSIC           T     2024-05-19-16.51.11.981906

  6 record(s) selected.

---

创建索引

create_stock_ix.ddl

create index music.stockitem_ix on music.stock(itemno) ;

执行文件：

db2 -tvf /home/inst23/ddl/create_stock_ix.ddl

输出含“DB20000I The SQL command completed successfully.”正常。

查看索引描述：

db2 describe indexes for table music.stock

输出：

Index                           Index               Unique         Number of      Index                       Index          Null

schema                          name                rule           columns        type                        partitioning   keys

------------------------------- ------------------- -------------- -------------- --------------------------- -------------- ------

MUSIC                           STOCKITEM_IX        D                           1 RELATIONAL DATA             -              Y       

  1 record(s) selected.

---

创建视图

create_VIEW_ALIAS.ddl

create view music.music as select title, classification, name 
from music.albums alb, music.artists art 
where art.artno = alb.artno ;


create view music.inventory (type, itemno, totcost, totqty) 
as select type, itemno, sum (price * qty), sum (qty) 
from music.stock group by type, itemno;


create alias music.singers for music.artists ;

create alias music.emptystock for music.reorder ;

执行文件：

db2 -tvf create_VIEW_ALIAS.ddl

输出每条SQL执行结果为“DB20000I The SQL command completed successfully.”说明执行成功。

列出music模式下的所有表、视图与索引

db2 list tables for schema music

输出：

Table/View                      Schema          Type  Creation time             

------------------------------- --------------- ----- --------------------------

ALBUMS                          MUSIC           T     2024-05-19-16.48.22.912328

ARTISTS                         MUSIC           T     2024-05-19-16.51.11.729939

CONCERTS                        MUSIC           T     2024-05-19-16.51.11.636145

EMPTYSTOCK                      MUSIC           A     2024-05-19-16.54.36.539459

INVENTORY                       MUSIC           V     2024-05-19-16.54.36.510653

MUSIC                           MUSIC           V     2024-05-19-16.54.36.483311

REORDER                         MUSIC           T     2024-05-19-16.51.11.683649

SINGERS                         MUSIC           A     2024-05-19-16.54.36.530610

STOCK                           MUSIC           T     2024-05-19-16.51.11.911520

STOCK_HISTORY                   MUSIC           T     2024-05-19-16.51.11.981906

  10 record(s) selected.

$ db2 describe indexes for table music.stock

---

修改表的主外键

create_ri_cc.ddl

alter table music.albums add constraint ARTNO_FK 
  foreign key (artno) references music.artists (artno) 
  on delete cascade on update no action ;

alter table music.stock 
      foreign key ITEMNO_FK (itemno) 
         references music.albums on delete restrict;

alter table music.stock 
add constraint cctype check (type in ('D', 'C', 'R')) ; 

运行文件：

db2 -tvf create_ri_cc.ddl

输出SQL执行结果为“DB20000I The SQL command completed successfully.”说明执行成功。

---

创建触发器

create_trigger.ddl

create trigger music.reorder 
after update of qty on music.stock 
referencing new as n 
for each row 
mode db2sql 
when (n.qty <= 5) 
insert into music.reorder values (n.itemno, current timestamp);

运行文件：

db2 -tvf create_trigger.ddl

输出SQL执行结果为“DB20000I The SQL command completed successfully.”说明执行成功。

ALBUMS.ddl

-- This CLP file was created using DB2LOOK Version "11.5" 
-- Timestamp: Sat 25 May 2024 01:46:42 PM CST
-- Database Name: MUSICDB        
-- Database Manager Version: DB2/LINUXX8664 Version 11.5.4.0
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF


CONNECT TO MUSICDB;

------------------------------------------------
-- DDL Statements for Table "MUSIC   "."ALBUMS"
------------------------------------------------
 

CREATE TABLE "MUSIC   "."ALBUMS"  (
		  "TITLE" VARCHAR(50 OCTETS) , 
		  "ARTNO" SMALLINT NOT NULL , 
		  "ITEMNO" SMALLINT NOT NULL )   
		 IN "TSP04" INDEX IN "TSP05"  
		 ORGANIZE BY ROW; 


-- DDL Statements for Primary Key on Table "MUSIC   "."ALBUMS"

ALTER TABLE "MUSIC   "."ALBUMS" 
	ADD PRIMARY KEY
		("ITEMNO")
	ENFORCED;



-- DDL Statements for Foreign Keys on Table "MUSIC   "."ALBUMS"

ALTER TABLE "MUSIC   "."ALBUMS" 
	ADD CONSTRAINT "ARTNO_FK" FOREIGN KEY
		("ARTNO")
	REFERENCES "MUSIC   "."ARTISTS"
		("ARTNO")
	ON DELETE CASCADE
	ON UPDATE NO ACTION
	ENFORCED
	ENABLE QUERY OPTIMIZATION;






----------------------------

-- DDL Statements for Views

----------------------------
SET CURRENT SCHEMA = "INST23  ";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","INST23";
create view music.music as select title, classification, name from music.albums
alb, music.artists art where art.artno = alb.artno;





COMMIT WORK;

CONNECT RESET;

TERMINATE;

运行文件：

db2 -tvf ALBUMS.ddl

输出SQL执行结果为“DB20000I The SQL command completed successfully.”说明执行成功。

也可以使用more命令查看文件内容：more /home/inst23/ddl/ALBUMS.ddl