Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 2. Database Design > Practice Questions

Practice Questions

Question 1Which CREATE TABLESPACE statement option is used to enable non-buffered I/O?

  1. CONCURRENT IO

  1. NO FILE SYSTEM CACHING

  1. USE DIRECT IO

  1. NO FILE BUFFERING


Question 2A database named MYDB was created using automatic storage and a table space in the database named MY_TBSP is running out of space. Which command will provide more storage space to the table space so more data can be added to it?

  1. ALTER DATABASE ADD STORAGE ON ‘/db2/dbspace1’

  1. ALTER TABLESPACE my_tbsp EXTEND 100 MB

  1. ALTER DATABASE EXTEND STORAGE FOR my_tbsp 100 MB

  1. ALTER TABLESPACE my_tbsp ADD STORAGE ON ‘/db2/dbspace1’


Question 3A table space named MY_TBSP was created in a database partition group named DBPG1. Later, a decision is made to move this table space to a database partition group named DBPG2. How can this change be made?

  1. Change the partition group associate with the table space by executing the ALTER TABLESPACE statement.

  1. Detach the table space from the DBPG1database partition group and attach it to the DBPG2 database partition group using the DETACH PARTITION and ATTACH PARTITION statements.

  1. Drop the table space using the DROP statement and recreate it in database partition DBPG2 using the CREATE TABLESPACE statement.

  1. Move the table space from the database DBPG1 partition group to the DBPG2 database partition group using the REDISTRIBUTE PARTITION GROUP statement.


Question 4If the SQL statement shown below is executed:
CREATE REGULAR TABLESPACE sales_ts
MANAGED BY DATABASE USING
   (FILE 'C:\TABLESPACES\dms_tbsp.tsf' 1000 M)

Assuming DB2 9 is being used, which of the following is NOT a characteristic of the resulting table space?

  1. The page size used is 4K.

  1. File system caching is enabled.

  1. The table space resides in the IBMTEMPGROUP database partition group.

  1. Dropped tables in the specified table space can be recovered using the RECOVER DROPPED TABLE option of the ROLLFORWARD DATABASE command.


Question 5Which two of the following can be successfully performed using the ALTER TABLESPACE statement? (Select two)

  1. Convert an SMS table space to a DMS table space.

  1. Change the extent size for a DMS table space.

  1. Change the prefetch size for an automatic storage table space.

  1. Resize the containers used by an SMS table space.

  1. Add a container to an SMS table space on a database partition that currently has no containers.


Question 6Which of the following commands will create a DMS table space named TBSP1 on all database partitions used by a multi-partition database?

  1. CREATE TABLESPACE tbsp1 MANAGED BY DATABASE USING (FILE ‘/db2files/tbsp1/cont1P $N’ 5000)

  1. CREATE TABLESPACE tspc1 MANAGED BY DATABASE USING (FILE ‘/db2files/ tbsp1/ cont1P $NODE’ 5000)

  1. CREATE TABLESPACE tspc1 MANAGED BY DATABASE USING (FILE /db2files/ tbsp1/ cont1P $P’ 5000)

  1. CREATE TABLESPACE tspc1 MANAGED BY DATABASE USING (FILE ‘/db2files/ tbsp1/ cont1P $DBPARTITIONNUM’ 5000)


Question 7Given the following statements:
CREATE TABLESPACE payroll MANAGED BY DATABASE USING
   (FILE '\home\myfile1' 200 FILE '\home\myfile2' 400);
ALTER TABLESPACE payroll EXTEND (FILE '\home\myfile2' 1000
   FILE '\home\myfile1' 1200);

How large are the containers MYFILE1 and MYFILE2?

  1. MYFILE1 = 200 pages, MYFILE2 = 400 pages

  1. MYFILE1 = 1200 pages, MYFILE2 = 1000 pages

  1. MYFILE1 = 1400 pages, MYFILE2 = 1400 pages

  1. MYFILE1 = 1200 pages, MYFILE2 = 1600 pages


Question 8If no initial size is specified for a table space that is created in a database that is using automatic storage, how much storage space is preallocated for the table space?

  1. 32 pages

  1. 32 MB

  1. 64 pages

  1. 64 MB


Question 9Given the following sequence of SQL statements:
  1. CREATE DATABASE test ON C:

  2. CONNECT TO test

  3. CREATE BUFFERPOOL bp1 SIZE 2000

  4. CREATE TABLESPACE ts1 MANAGED BY DATABASE USING (FILE ‘C:\ts1data. dat’ 10 M) BUFFERPOOL bp1

  5. CREATE TABLE employees(empid INT, fname CHAR(30), lname CHAR(30))

Which two statements are not required in order to create the table named EMPLOYEES? (Select two)

  1. 1

  1. 2

  1. 3

  1. 4

  1. 5


Question 10While connected to a database that has four partitions, a database administrator executes the following command:
CREATE BUFFERPOOL mybp SIZE 3000

If 8 MB of memory is available at the time the command is executed, how much memory is immediately consumed?

  1. 0 MB

  1. 6 MB

  1. 8 MB

  1. 12 MB


Question 11Given the following table spaces, their associated page size, and number of pages:

TBSP1-16K page size, 150000 pages
TBSP2-8K page size, 100000 pages
TBSP3-4K page size, 200000 pages
USERSPACE1-4K page size, 500000 pages


How many buffer pools must be created before the table spaces listed can be created?

  1. 1

  1. 2

  1. 3

  1. 4


Question 12Given a newly created database and sufficient memory, if the following statements are successfully executed on a UNIX server:
CREATE BUFFERPOOL bp1 SIZE 60 MB;
CREATE BUFFERPOOL bp2 SIZE 20000 PAGESIZE 16K;
CREATE BUFFERPOOL bp3 SIZE 2000 PAGESIZE 8K;

How much memory will be allocated for all buffer pools used by the database?

  1. 320 MB

  1. 396 MB

  1. 400 MB

  1. 401 MB


Question 13Which two of the following are valid ALTER BUFFERPOOL statements? (Select two)

  1. ALTER BUFFERPOOL mybp NUMBLOCKPAGES 32000

  1. ALTER BUFFERPOOL mybp TABLESPACE tbsp1

  1. ALTER BUFFERPOOL mybp PAGESIZE 8 K

  1. ALTER BUFFERPOOL mybp BLOCKSIZE 32

  1. ALTER BUFFERPOOL mybp SIZE -1


Question 14A database administrator needs to create a federated database and configure access to join data from two Oracle instances, one SQL Server instance, and one DB2 database.

Which objects are needed to establish the specified connections?

  1. 2 Oracle servers, 2 Oracle wrappers, 1 SQL Server server, 1 SQL Server wrapper, and nicknames for all databases.

  1. 2 Oracle servers, 1 Oracle wrapper, 1 SQL Server server, 1 SQL Server wrapper, and nicknames for all databases.

  1. 2 Oracle servers, 2 Oracle wrappers, 1 SQL Server server, 1 SQL Server wrapper, 1 DB2 server, 1 DB2 wrapper, and nicknames for all databases.

  1. 2 Oracle servers, 1 Oracle wrapper, 1 SQL Server server, 1 SQL Server wrapper, 1 DB2 server, 1 DB2 wrapper, and nicknames for all databases.


Question 15Last week, a database administrator configured a SQL Server data source in a federated database (that references a table named HISTORY). Yesterday, insert operations caused the HISTORY table to double in size. Which of the following steps must be performed in order to update the statistics for the HISTORY table at the federated server?

  1. Perform the SQL Server equivalent of RUNSTATS on the HISTORY table at the SQL Server server.

  1. Perform the SQL Server equivalent of RUNSTATS on the HISTORY table at the SQL Server server; then perform RUNSTATS on the nickname for the HISTORY table at the federated server.

  1. Perform the SQL Server equivalent of RUNSTATS on the HISTORY table at the SQL Server server; then drop and re-create the HISTORY nickname at the federated server.

  1. Perform the SQL Server equivalent of RUNSTATS on the HISTORY table at the SQL Server server; then use the “db2updfedstat” tool to update the statistics at the federated server.


Question 16A database administrator created all of the necessary federated objects for an Oracle data source in a federated system.

Given the following set of steps:

1.
Drop the current user mapping

2.
Drop the current data type mapping

3.
Drop the nickname to the table

4.
Create the new user mapping

5.
Create the new data type mapping

6.
Re-create the nickname to the table

Which steps must be performed if the data types of several columns in the Oracle data source are changed?

  1. 2, 5

  1. 3, 5, 6

  1. 2, 5, 3, 6

  1. 1, 2, 4, 5, 6



  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free 10-Day Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint