Сама несчастная DB2-Express (362 mb)
Версия для “избранных”: DB-2 Express x64 (423 mb)
Самая последняя версия с их убогого сайта, который, чтобы скачать эту ещё более убогую среду разработки баз данных, требует пройти какую-то идиотскую регистрацию и подключения java-модуля для открытия их особо убогого загрузчика ради этого единственного .exe файла.
Также у них доступна lite версия с урезанным функционалом. Если надо – валите и качайте её сами.
Лекции:
Вводная лекция
Первая
[gview file=”http://dl.dropbox.com/u/31859971/Download%26Install.ppt” save=”1″]
Вторая
[gview file=”http://dl.dropbox.com/u/31859971/DB_Org.ppt” save=”1″]
Третья
[gview file=”http://dl.dropbox.com/u/31859971/Creation.ppt” save=”1″]
Четвёртая
[gview file=”http://dl.dropbox.com/u/31859971/Tables.ppt” save=”1″ force=”1″]
Пятая
[gview file=”http://dl.dropbox.com/u/31859971/XML.ppt” save=”1″]
Шестая[gview file=”http://dl.dropbox.com/u/31859971/xPath.ppt”]
Шестая
[gview file=”http://dl.dropbox.com/u/31859971/xPath.ppt”]
Лабораторные:
Создание базы данны
[gview file=”http://dl.dropbox.com/u/31859971/IBM/Lab1.doc” save=”1″]
Создание табличных пространств
[gview file=”http://dl.dropbox.com/u/31859971/IBM/Lab2.doc” save=”1″]
Создание объектов базы данных (lite)
[gview file=”http://dl.dropbox.com/u/31859971/IBM/Lab3.doc” save=”1″]
Работа с данными в формате XML
[gview file=”http://dl.dropbox.com/u/31859971/IBM/Lab4.doc” save=”1″]
Работа с данными в формате XML.ОТВЕТЫ[gview file=”http://dl.dropbox.com/u/31859971/IBM/Lab4_1.doc” save=”1″]
Работа с данными в формате XML.ОТВЕТЫ
[gview file=”http://dl.dropbox.com/u/31859971/IBM/Lab4_1.doc” save=”1″]
Общий текст по BD2
Сим-сим
[gview file=”http://dl.dropbox.com/u/31859971/IBM/general_BD2.doc” save=”1″ force=”1″]
Инструкция
Здесь
[gview file=”http://dl.dropbox.com/u/31859971/IBM/Manual.doc” save=”1″]
Скрипты:
Создать музыкальную базу данных
CREATE DATABASE MUSICDB AUTOMATIC STORAGE YES ON 'C:\' DBPATH ON 'C:\' USING CODESET 1251 TERRITORY RU COLLATE USING SYSTEM PAGESIZE 4096;
connect to musicdb;
update db cfg for musicdb using maxlocks 20 num_freqvalues 12;get db cfg for musicdb show detail;
create regular tablespace dms01 PAGESIZE 4 K MANAGED BY DATABASE USING ( FILE 'C:\dms\dms01' 106 ) EXTENTSIZE 4 OVERHEAD 10.5 PREFETCHSIZE 4 TRANSFERRATE 0.14 BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY ON;create regular tablespace dms02 managed by database using (file 'c:\dms\dms02' 14) extentsize 2;create long tablespace dms03 managed by database using (file 'c:\dms\dms03' 728) extentsize 8;create regular tablespace dms04 managed by database using (file 'c:\dms\dms04' 22) extentsize 2;create regular tablespace dms05 managed by database using (file 'c:\dms\dms05' 16) extentsize 2;create regular tablespace dms06 managed by database using (file 'c:\dms\dms06' 40) extentsize 4;create regular tablespace sms01 managed by system using ('c:\sms\sms01','c:\sms\sms02') extentsize 4;
list tablespaces;list tablespace containers for 0;list tablespace containers for 1;list tablespace containers for 2;
connect reset
Создать объекты БД
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 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
Создать XML БД
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:\xmlfiles\clients.del” of del xml from “c:\xmlfiles” insert into clients (id, name, status, contact);import from “c:\xmlfiles\items.del” of del xml from “c:\xmlfiles” insert into items (id, brandname, itemname, sku, srp, comments);connect reset
Создать xQuery БД
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:\sqlfiles\xmlcustomer.data” OF IXF MODIFIED BY FORCEIN MESSAGES “D:\sqlfiles\xmlcustomer.err” REPLACE INTO XMLCUSTOMER;IMPORT FROM “D:\sqlfiles\xmlproduct.data” OF IXF MODIFIED BY FORCEIN MESSAGES “D:\sqlfiles\xmlproduct.err” REPLACE INTO XMLPRODUCT;CONNECT RESET
Импортировать объекты
CONNECT TO MUSICDB USER inst using ibm4es;
IMPORT FROM “C:\musicdbfiles\ARTISTS.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:\musicdbfiles\ARTISTS.msg” INSERT INTO ARTISTS;IMPORT FROM “C:\musicdbfiles\ALBUMS.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:\musicdbfiles\ALBUMS.msg” INSERT INTO ALBUMS;IMPORT FROM “C:\musicdbfiles\STOCK.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:\musicdbfiles\STOCK.msg” INSERT INTO STOCK;LOAD FROM “C:\musicdbfiles\CONCERTS.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:\musicdbfiles\CONCERTS.msg” INSERT INTO CONCERTS COPY NO INDEXING MODE AUTOSELECT;
CONNECT RESET
IMPORT FROM “C:\musicdbfiles\ARTISTS.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:\musicdbfiles\ARTISTS.msg” INSERT INTO ARTISTS;IMPORT FROM “C:\musicdbfiles\ALBUMS.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:\musicdbfiles\ALBUMS.msg” INSERT INTO ALBUMS;IMPORT FROM “C:\musicdbfiles\STOCK.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:\musicdbfiles\STOCK.msg” INSERT INTO STOCK;LOAD FROM “C:\musicdbfiles\CONCERTS.EXP” OF IXF MODIFIED BY FORCEIN MESSAGES “C:\musicdbfiles\CONCERTS.msg” INSERT INTO CONCERTS COPY NO INDEXING MODE AUTOSELECT;
CONNECT RESET
Перезапуск
DROP DATABASE XMLDBRU;
DROP DATABASE XQUERYDB;
DROP DATABASE XQUERYDB;
Также в архиве присутствуют файлы Баз Данных, непосредственно нужные для лабораторных работ.
Ну и покопаться в них тоже полезно.
Скачать Всё вместе