db2經典實驗內容，希望大家趕快動手！（第3部）

db2經典實驗內容，希望大家趕快動手！（第3部）[@more@]DB2 Migration Workshop
LAB 2 – Working with buffer pools and tablespaces

Expected duration: 45 mins

This lab will help you solidify concepts of buffer pools and tablespaces. It is to be completed following the presentation “DB2 Bufferpools and tablespaces”.

The following commands are demonstrated:

ALTER TABLESPACE ALTER BUFFERPOOL
CREATE TABLESPACE CREATE BUFFERPOOL
LIST TABLESPACES DROP BUFFERPOOL
LIST TABLESPACE CONTAINERS
DROP TABLESPACE CREATE TABLE … IN

Prerequisites:
· DB2 Version 8 Server has been installed
· Completion of Lab1 (requires sample database created)

Highly Recommended: Before each step in this lab, locate the demonstrated command in the CLP quick reference and mark it with an asterix (*). This will help you get familiar with the DB2 CLP cheat sheet.

1. Connect to the sample database and view the tablespaces that currently exist

db2 list tablespaces show detail

From the output of the above command, fill in the following chart

TS ID TS Name TS Type TS Page Size







2. Now you’ll create your own table space which uses an 8K page size for data, and a separate 4K page size tablespace for indexes. Before an 8K tablespace can be created, however, an 8K buffer pool must first exist. Create an 8K buffer pool MYBP8K which uses 1000 pages (8MB).

db2 create bufferpool MYBP8K size 1000 pagesize 8K

Next, create a tablespace called MYTS8K which uses the 8K bufferpool.

db2 create tablespace MYTS8K pagesize 8K
managed by database using (
FILE ‘c:myts8k.0’ 1000,
FILE ‘c:myts8k.1’ 1000)
extentsize 8
prefetchsize 16
bufferpool MYBP8K

What type of table space was just created? (DMS or SMS) __________________
How many containers are in this table space? __________________
What is the purpose of using backslash ? __________________

How many kilobytes of data will be written to myts8k.0 before data is written to myts8k.1 (note: calculation required)? Why?

____________________________________________________________________

____________________________________________________________________

In order to store indexes separately from data, DMS tablespaces are required. From step 1, you should have identified that all tablespaces in SAMPLE are SMS. Therefore, we need to create a 4K DMS tablespace to store index data.

db2 create tablespace MYTS4K_IDX pagesize 4K
managed by database using (FILE ‘c:myts4k_idx.0’ 1000)
extentsize 8
prefetchsize 8
bufferpool IBMDEFAULTBP

Which buffer pool is being used by MYTS4K_IDX? __________________
How many kilobytes of data per extent in this tablespace? __________________

3. Take a look at the bufferpools that currently exist (and their sizes)

db2 “select * from syscat.bufferpools”

4. See all the tablespaces just created by repeating step 1. Complete the table by adding the additional information for the tablespaces just created. Show the containers for table space MYTS8K and MYTS4K_IDX

db2 list tablespace containers for

(substitute with the table space ID of MYTS8K and MYTS4K_IDX)

5. How many data pages are free and used in MYTS8K and MYTS4K_IDX?

db2 list tablespaces show detail

Fill in the following information for MYTS8K

Used pages: __________________
Free pages: __________________
High watermark: __________________

Fill in the following information for MYTS4K_IDX

Used pages: __________________
Free pages: __________________
High watermark: __________________

6. Create a table with the following DDL:

db2 create table T1
(id bigint not null primary key,
data char(100),
insert_ts timestamp)
IN MYTS8K
INDEX IN MYTS4K_IDX

If you look at the output of list tablespaces show detail, you’ll see that additional pages have been used in both MYTS8K and MYTS4K_IDX. We expect that pages will be allocated in the data tablespace (overhead data pages for a table). But why are additional pages allocated MYTS4K_IDX?

____________________________________________________________________

____________________________________________________________________


7. The bufferpool sized at 1000 pages is too small for any real use. In version 8, bufferpools, in most cases, can be dynamically changed (you will be given a warning if it is not dynamic) using ALTER BUFFERPOOL

Tip: You can use OS monitoring tools to see the memory use increase.

db2 alter bufferpool ibmdefaultbp size 5000
db2 alter bufferpool mybp8k size 5000

What are the new sizes of each bufferpool, in megabytes?

IBMDEFAULTBP _________________
MYBP8K _________________

8. Tablespace characteristics can also be altered using ALTER TABLESPACE. In this step, increase the aggressiveness of index prefetching

db2 alter tablespace MYTS4K_IDX prefetchsize 16

9. Launch the DB2 Control Center to visually identify the objects you just created
· Table
· Index
· Tablespaces
· Tablespace Containers
· Bufferpools

10. Clean up objects created by this lab exercise:

db2 drop table T1
db2 drop tablespace MYTS8K
db2 drop tablespace MYTS4K_IDX
db2 drop bufferpool MYBP8K