IBM – Практика

[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/IBM/Lab1.doc” save=”1″]

[gview file=”http://dl.dropbox.com/u/31859971/IBM/Lab2.doc” save=”1″]

[gview file=”http://dl.dropbox.com/u/31859971/IBM/Lab3.doc” save=”1″]

[gview file=”http://dl.dropbox.com/u/31859971/IBM/Lab4.doc” save=”1″]

[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 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

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

DROP DATABASE XMLDBRU;
DROP DATABASE XQUERYDB;