project
Creating Tablespaces and Tables in DB2
BCIS 4620
z
Architecture
Databases have tablespaces
Tablespaces have tables
Tables have key indexes
z
Naming Conventions
For all the names below ### refers to your UNTP number and XX Refers to the first two letters of the table name.
You may have to change your XX based on if it is the same as another table.
Example you have two tables Consultant and Course, so the tablespaces can’t be named TSCO for both. So you may change the tablespace for Course to CR instead.
z
Naming Conventions
Databases – P###DB
Tablespaces - P###TSXX
Tables – The name of the entity
Indexes - UNTP###.X1XX
Here, the first X stands for index.
z
Create a TableSpace
--CREATE TABLESPACES
CREATE TABLESPACE P###TSXX IN P###DB USING STOGROUP UNTDISK;
COMMIT;
z
Create a Table
--CREATE XXTABLE TABLE
CREATE TABLE XXTABLE
(XXTABLE _NO CHAR(6) NOT NULL,
XXTABLE _NAME VARCHAR(25) NOT NULL,
XXTABLE_CREDITS DECIMAL (2,0) NOT NULL,
PRIMARY KEY (XXTABLE _NO))
IN P###DB.M###TSXX;
COMMIT;
z
Create a Table (with Foreign Key)
--CREATE XXTABLE TABLE
CREATE TABLE XXTABLE
(XXTABLE _NO CHAR(6) NOT NULL,
XXTABLE _NAME VARCHAR(25) NOT NULL,
XXTABLE_CREDITS DECIMAL (2,0) NOT NULL,
PRIMARY KEY (XXTABLE _NO),
FOREIGN KEY(XXTABLE 2_ID) REFERENCES XXTABLE2)
IN P###DB.M###TSXX;
COMMIT;
z
Create a Table (with Composite PK)
--CREATE XXTABLE TABLE
CREATE TABLE XXTABLE
(XXTABLE1 _NO CHAR(6) NOT NULL,
XXTABLE _NAME VARCHAR(25) NOT NULL,
XXTABLE_CREDITS DECIMAL (2,0) NOT NULL,
XXTABLE2_ID INTEGER NOT NULL,
PRIMARY KEY (XXTABLE1 _NO, XXTABLE2_ID),
FOREIGN KEY(XXTABLE 2_ID) REFERENCES XXTABLE2,
FOREIGN KEY(XXTABLE 1_NO) REFERENCES XXTABLE1)
IN P###DB.M###TSXX;
COMMIT;
z
Create a PK Index
--CREATE PK INDEX ON XXTABLE
CREATE UNIQUE INDEX UNTP###.X1XX
ON UNTP###. XXTABLE (XXTABLE _NO)
USING STOGROUP UNTDISK;
COMMIT;
z
Create a FK Index
--CREATE FK INDEX ON XXTABLE
CREATE INDEX UNTP###.X2XX
ON UNTP###. XXTABLE (XXTABLE2 _ID)
USING STOGROUP UNTDISK;
COMMIT;
z