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
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
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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7199667/viewspace-1022786/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
2009 下半年工作计划
请登录后发表评论
登录
全部评论
