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

DB2 supports a database with up to 1,000 partitions.

The IBMDEFAULTBP buffer pool is created automatically in every database.

Since the row width is over 9,000 bytes, the table must be created in a table space with at least a 16K page size.

A table's data object in an SMS table space will be placed in a file with a .DAT extension.

The command

LIST TABLESPACES SHOW DETAIL will display the number of used and free pages in each table space.

DMS table spaces are completely allocated when they are created.

SMS table spaces grow and shrink as the tables and indexes within them grow and shrink.

When a table has been defined with its data, index, and large objects in different table spaces, you cannot drop one of the table spaces without dropping the table first.

The block-based area of a buffer pool is used to improve performance of prefetching by allowing DB2 to read entire extents into contiguous pieces of the buffer pool.

The

get snapshot for tablespaces command will show if and when a rebalance is occurring. It will show when the rebalance started and how many extents in the table space still need to be moved as part of the rebalance process.

A . DB2 supports 4K, 8K, 16K, and 32K page sizes.

B . Since the table spaces all use an 8K page size, they can all share a buffer pool with an 8K page size. However, since the system catalogs are created in a table space with a 4K page size, there must also be a buffer pool with a 4K page size. Therefore, the answer is 2.

D . Since there are 4 partitions, and with a 32K page size the maximum table space size is 512GB, the largest table could be 4 x 512GB or 2TB in size.

D . Under the /data directory, DB2 creates a directory with the instance name. The SQL

xxxxx directories are created under the instance directory. Therefore, in this case, DB2 will create the directory

inst1 under the /data directory.

A . DB2 creates a directory with the partition number for that server under the /data/inst1 directory. Since partitions 2 and 3 are on server

mysrv2 , the directories will be NODE0002 and NODE0003.

E . By default DB2 Version 8 creates five subdirectories for the following:

• The catalog table space

• The temporary table space

• Userspace1

• The log directory

• Event Monitor output

B . In this case, the database

sample would use SQL00001. Sales would be placed in the directory SQL00002 and test in SQL00003. When the sales database is dropped, the SQL00002 directory is also dropped, and is then available in case other databases are created on the same drive or path. When the database

my_sales is created, DB2 will reuse the SQL00002 directory.

C . Since the

create database command was run on server

mysrv2 , the catalog partition will be on the partition with port number 0 on that server, in this case, partition 2.

D . You need to connect to the database and create the buffer pool with a 16K page size before you can create a table space with a 16K page size. Therefore, the correct answer is

D .

C . The correct syntax is

CREATE BUFFERPOOL BP1 
SIZE 150MB 
except on DBPARTITIONNUMS 1,2,3,4,5 size 100MB