Section 8.6. Case Study

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)