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