Learning Visually with Examples [Electronic resources] نسخه متنی

8.6. Case Study

Let's start by creating a database named

mydb on your Windows server's C: drive as follows:

CREATE DATABASE mydb ON c: 

Your server was configured with four physical hard drives that are dedicated to your database, so you want to create a new table space that will use these drives. Since DMS table spaces are more flexible, you have decided to use DMS file containers for the table space. Once you have formatted the four drives using the operating system tools and given them the labels G, H, I, and J drives, you are ready to create the table space.

First you need to connect to the database:

CONNECT TO mydb 

You can then create the table space:

CREATE TABLESPACE myts MANAGED BY DATABASE USING 
(FILE 'g:\ts1' 50000, 
FILE 'h:\ts1' 50000, 
FILE 'i:\ts1' 50000, 
FILE 'j:\ts1' 50000) 

To optimize access to data in this table space you can add a new buffer pool, and then assign the

myts table space to use the buffer pool:

CREATE BUFFERPOOL mytsbp SIZE 50000 
ALTER TABLESPACE myts BUFFERPOOL mytsbp 

You have a table with a large number of columns that will not fit the default 4K page, so you will need to create a new table space with a larger page size. However, before you can create the table space you must first create a buffer pool with the page size you intend to use for the table space:

CREATE BUFFERPOOL my16kbp SIZE 50000 PAGESIZE 16K 
CREATE TABLESPACE myts16k 
PAGESIZE 16k 
MANAGED BY DATABASE USING 
(FILE 'g:\ts16k' 50000, 
FILE 'h:\ts16k' 50000, 
FILE 'i:\ts16k' 50000, 
FILE 'j:\ts16k' 50000) 
BUFFERPOOL my16kbp 

To create the table you need to specify the 16K page size table space:

CREATE TABLE foo 
(c1 VARCHAR(2500), 
c2 VARCHAR(2500) 
) IN myts16K 

You execute the command

CREATE TABLE foo2 
(c1 VARCHAR(250), 
c2 VARCHAR(250) 
) 

and DB2 puts the table in the first user-created table space,

myts . You can verify this by querying the SYSCAT.TABLES catalog table:

SELECT tabname, tbspace FROM syscat.tables 
WHERE tabname='FOO2' 

You want to create another table and store any indexes defined on the table in a different table space, so you must specify the data and index table spaces when you create the table. You first need to create the index table space.

CREATE TABLESPACE INXts16k 
PAGESIZE 16K 
MANAGED BY DATABASE USING 
(FILE 'g:\INX16k' 500, 
    FILE 'h:\INX16k' 500, 
FILE 'i:\INX16k' 500, 
FILE 'j:\INX16k' 500) 
BUFFERPOOL my16kbp 

You can then create the table and specify that the data will be stored in the table space

myts16k , and the index in the table space will be

INXts16k .

CREATE TABLE staff 
(empid INT, 
fname VARCHAR(30), 
lname VARCHAR(30), 
deptid INT 
) 
IN myts16K 
INDEX IN INXts16K 

When you now create any indexes on the staff table, they will be placed in the

INXts16K table space.

CREATE INDEX staffx ON staff (empid) 

As you can see above, the index table space only has 2,000 pages of space defined. Since you anticipate having a large number of rows in this table, you enlarge the table space:

ALTER TABLESPACE INXts16k EXTEND (ALL CONTAINERS 40000)