Скачать вводную лекцию

CONNECT TO MUSICDB;
CREATE TABLE 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 CC1192270426319 PRIMARY KEY ( ARTNO)  ) IN DMS01 INDEX IN DMS02 LONG IN DMS03 ;create table albums (title varchar (50), artno smallint not null, itemno smallint not null) in dms04 index in dms05;create table stock (itemno smallint not null, type char (1) not null, price decimal (5,2) not null with default, qty int not null with default) in dms06;create table concerts (artno smallint not null, date date not null, city varchar (25) not null with default) in dms04;create table reorder (itemno smallint  not null, timestamp timestamp) in sms01;
grant select on table artists to public;grant select on table albums to public;grant select on table stock to public;grant select on table concerts to public;grant select on table reorder to public;
CREATE  INDEX ITEM ON STOCK (ITEMNO ASC)  PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS  PAGE SPLIT SYMMETRIC COLLECT  SAMPLED DETAILED  STATISTICS ;
CREATE  INDEX ITEMNO ON ALBUMS (ITEMNO ASC)  PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS  PAGE SPLIT SYMMETRIC COLLECT  SAMPLED DETAILED  STATISTICS ;
CREATE view MUSIC as select title, classification, name from albums, artists where artists.artno = albums.artno ;create view inventory (type, itemno, totcost, totqty) as select type, itemno, sum (price * qty), sum(qty) from stock group by type, itemno ;
CREATE ALIAS SINGERS FOR ARTISTS;CREATE ALIAS EMPTYSTOCK FOR REORDER;
ALTER TABLE ALBUMS ADD CONSTRAINT CC1192271216475 PRIMARY KEY ( ITEMNO) ADD CONSTRAINT FKARTNO FOREIGN KEY (ARTNO) REFERENCES ARTISTS (ARTNO)  ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED  ENABLE QUERY OPTIMIZATION ;alter table stock foreign key fkitemno (itemno) references albums on delete restrict;
ALTER TABLE STOCK ADD CONSTRAINT CCTYPE CHECK (type in (‘D’,'C’,'R’))  ENFORCED  ENABLE QUERY OPTIMIZATION ;
CREATE TRIGGER REORDER AFTER  UPDATE OF QTY ON STOCK  REFERENCING  NEW AS n  FOR EACH ROW  MODE DB2SQL when (n.qty <= 5) insert into reorder values (n.itemno,current timestamp) ;
CONNECT RESET

create database xmldbru using codeset UTF-8 territory RU;connect to xmldbru;create table items (id int primary key not null, brandname varchar(30), itemname varchar(30), sku int, srp decimal(7,2), comments xml);create table clients (id int primary key not null, name varchar(50), status varchar(25), contact xml);insert into clients values (77, ‘Иван Иванов’, ‘Золотой’, ‘<addr>000112, Москва, Проспект Мира, 111</addr>’);import from “c:xmlfilesclients.del” of del xml from “c:xmlfiles” insert into clients (id, name, status, contact);import from “c:xmlfilesitems.del” of del xml from “c:xmlfiles” insert into items (id, brandname, itemname, sku, srp, comments);connect reset

CREATE DATABASE XQUERYDB USING CODESET UTF-8 TERRITORY RU;CONNECT TO XQUERYDB;CREATE TABLE xmlproduct (Pid VARCHAR(10) NOT NULL PRIMARY KEY, Description XML);CREATE TABLE xmlcustomer (Cid BIGINT NOT NULL PRIMARY KEY, Info XML);IMPORT FROM “D:sqlfilesxmlcustomer.data” OF IXF MODIFIED BY FORCEIN MESSAGES “D:sqlfilesxmlcustomer.err” REPLACE INTO XMLCUSTOMER;IMPORT FROM “D:sqlfilesxmlproduct.data” OF IXF MODIFIED BY FORCEIN MESSAGES “D:sqlfilesxmlproduct.err” REPLACE INTO XMLPRODUCT;CONNECT RESET

CONNECT TO MUSICDB USER inst using ibm4es;
IMPORT FROM “C:musicdbfilesARTISTS.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:musicdbfilesARTISTS.msg” INSERT INTO ARTISTS;IMPORT FROM “C:musicdbfilesALBUMS.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:musicdbfilesALBUMS.msg” INSERT INTO ALBUMS;IMPORT FROM “C:musicdbfilesSTOCK.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:musicdbfilesSTOCK.msg” INSERT INTO STOCK;LOAD FROM “C:musicdbfilesCONCERTS.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:musicdbfilesCONCERTS.msg” INSERT INTO CONCERTS COPY NO INDEXING MODE AUTOSELECT;
CONNECT RESET

DROP DATABASE XMLDBRU;
DROP DATABASE XQUERYDB;