Creating Tablespaces And Tables In Db2 Architecture

Question

Creating Tablespaces And Tables In Db2bcis 4620zarchitecturedatabases

Creating Tablespaces and Tables in DB2 BCIS 4620 z Architecture Databases have tablespaces Tablespaces have tables Tables have key indexes z Create a TableSpace --CREATE TABLESPACES CREATE TABLESPACE P###TSXX IN P###DB USING STOGROUP UNTDISK; COMMIT; z Create a Table --CREATE TESTXX TABLE CREATE TABLE TESTXX (COURSE_NO CHAR(6) NOT NULL, COURSE_NAME VARCHAR(25) NOT NULL, CREDITS DECIMAL (2,0) NOT NULL, PRIMARY KEY (COURSE_NO)) IN P###DB.M###TSXX; COMMIT; z Create a PK Index --CREATE PK INDEX ON TESTXX CREATE UNIQUE INDEX UNTP###.X1XX ON UNTP###.TESTXX (COURSE_NO) USING STOGROUP UNTDISK; COMMIT; z 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;

Dr. Jack HW Helper · Accepted Answer

Creating Tablespaces And Tables In Db2bcis 4620zarchitecturedatabases

Creating Tablespaces and Tables in DB2 BCIS 4620 z Architecture Databases have tablespaces Tablespaces have tables Tables have key indexes z Create a TableSpace --CREATE TABLESPACES CREATE TABLESPACE P###TSXX IN P###DB USING STOGROUP UNTDISK; COMMIT; z Create a Table --CREATE TESTXX TABLE CREATE TABLE TESTXX (COURSE_NO CHAR(6) NOT NULL, COURSE_NAME VARCHAR(25) NOT NULL, CREDITS DECIMAL (2,0) NOT NULL, PRIMARY KEY (COURSE_NO)) IN P###DB.M###TSXX; COMMIT; z Create a PK Index --CREATE PK INDEX ON TESTXX CREATE UNIQUE INDEX UNTP###.X1XX ON UNTP###.TESTXX (COURSE_NO) USING STOGROUP UNTDISK; COMMIT; z 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;

Creating Tablespaces And Tables In Db2bcis 4620zarchitecturedatabases

Related Assignments