IBM 000-734 Exam - CertifySky.com

Free 000-734 Sample Questions:

1. A database administrator needs to create a table with key columns C1 (i.e. YearMonth) and C2. This table needs to be partitioned by column C1 with three months per data partition. Additionally, data needs to be organized by column C2, so that all rows within any three month date range are clustered together based on 12 months of data. Which CREATE TABLE statement will accomplish this objective?
A. CREATE TABLE tab1 (c1 INT,
c2 CHAR(2), c3 INT,
c4 CHAR(2))
PARTITION BY RANGE (c2) (STARTING 200601 ENDING 200612 EVERY 12) ORGANIZE BY DIMENSIONS (c1))
B. CREATE TABLE tab1 (c1 INT,
c2 CHAR(2), c3 INT,
c4 CHAR(2))
PARTITION BY RANGE (c2) (STARTING 200601 ENDING 200612 EVERY 3) ORGANIZE BY DIMENSIONS (c1))
C. CREATE TABLE tab1 (c1 INT,
c2 CHAR(2), c3 INT,
c4 CHAR(2))
PARTITION BY RANGE (c1) (STARTING 200601 ENDING 200612 EVERY 12) ORGANIZE BY DIMENSIONS (c2))
D. CREATE TABLE tab1 (c1 INT, c2 CHAR(2), c3 INT, c4 CHAR(2))
PARTITION BY RANGE (c1) (STARTING 200601 ENDING 200612 EVERY 3)
ORGANIZE BY DIMENSIONS (c2))
Answer: D

2. The RECOVER command is used in an attempt to fix a damaged table space. What will the system do during the RECOVER process?
A. Create a new temporary table space if the damaged table space is a temporary table space.
B. Restore the database and attempt to make as much of the database available as possible.
C. Bypass inaccessible containers and make the rest of the table space available.
D. Prompt the user for new container definitions for damaged or missing containers.
Answer: B

3. Which statement is true about clustering indexes in a DPF environment?
A. The partitioning key columns should be added to all indexes with the INCLUDE option.
B. It is not possible to have a clustering index in a DPF environment.
C. The index columns should be prefixed by the partitioning key columns.
D. The partitioning key columns should be added immediately after the column in the index with the highest cardinality.
Answer: C

4. Table TAB1 was created using the following statement:
CREATE TABLE tab1 (c1 INT, c2 INT, c3 INT, c4 INT, c5 INT);
If column C1 is unique and queries typically access columns C1, C2 and C3 together, which statement(s) will create index(es) that will provide optimal query performance?
A. CREATE UNIQUE INDEX xtab1 ON tab1 (c1); CREATE INDEX xtab2 ON tab1 (c2) INCLUDE (c3);
B. CREATE UNIQUE INDEX xtab1 ON tab1 (c1) INCLUDE (c2, c3);
C. CREATE UNIQUE INDEX xtab1 ON tab1 (c3, c2, c1);
D. CREATE UNIQUE INDEX xtab1 ON tab1 (c2) INCLUDE (c1, c3);
Answer: B

5. What is a method for enabling non-buffered I/O at the table space level in a DB2 9 database?
A. Set the DB2_DIRECT_IO configuration parameter to YES.
B. Set the DB2_FILESYSTEM_CACHE configuration parameter to YES.
C. Use the USE DIRECT IO clause with the CREATE TABLESPACE or ALTER TABLESPACE command.
D. Use the NO FILE SYSTEM CACHING clause with the CREATE TABLESPACE or ALTER TABLESPACE command.
Answer: D

6. Database MYDB is created by executing the following command: CREATE DB mydb ON /data/path1, /data/path2, /data/path3
Later, the database MYDB was backed up by executing the following command: BACKUP DB mydb TO /bkuppath.
If MYDB is dropped, which command will re-create it in a new location and maintain the original storage paths?
A. RECOVER DB mydb FROM /bkuppath TO /mydbpath
B. RESTORE DB mydb FROM /bkuppath TO /mydbpath
C. RECOVER DB mydb TO END OF LOGS ON /bkuppath
D. RESTORE DB mydb FROM /bkuppath TO /mydbpath, /data/path1, /data/path2, /data/path3
Answer: B

7. Database MYDB was created using Automatic Storage. If the following command is executed: CREATE TABLESPACE ts1
What is the initial size of table space TS1?
A. 4KB
B. 128KB
C. 32MB
D. 1GB
Answer: C

8. If table space TBSP1 is using automatic storage, and the existing storage paths are full, which command would increase the available storage?
A. ALTER DATABASE ADD STORAGE ON '/db2/filesystem1'
B. ALTER TABLESPACE tbsp1 ADD ('/db2/filesystem1')
C. ALTER DATABASE ADD STORAGE FOR tbsp1 ('/db2/filesystem1')
D. ALTER TABLESPACE tbsp1 EXTEND USING ('/db2/filesystem1')
Answer: A

9. A system administrator creates a DMS LARGE table space with the default file system caching parameters.
To tell DB2 to stop file system caching for that table space, what must you do?
A. Do nothing, file system caching is automatically disabled for all user created table spaces.
B. Use the ALTER TABLESPACE statement to turn off file system caching for the table space.
C. Use the appropriate operating system command to turn off file system caching for the table space.
D. Drop and re-create the table space to disable file system caching.
Answer: B

10. Given the following statements:
CREATE TABLESPACE mytblsp MANAGED BY DATABASE USING (FILE e:\myfile1 1000, FILE f:\myfile2
800);
ALTER TABLESPACE mytbsp EXTEND (FILE e:\myfile1 200, FILE f:\myfile2 400); How large are the containers MYFILE1 and MYFILE2?
A. MYFILE1 = 200 pages, MYFILE2 = 400 pages
B. MYFILE1 = 1200 pages, MYFILE2 = 1000 pages
C. MYFILE1 = 1400 pages, MYFILE2 = 1200 pages
D. MYFILE1 = 1200 pages, MYFILE2 = 1200 pages
Answer: D

11. Given the following steps:
1) CREATE DATABASE sample ON c:
2) CREATE TABLESPACE ts1 PAGESIZE 16K MANAGED BY DATABASE USING (FILE 'c:\sample\ts1'
10M) BUFFERPOOL bp1
3) CREATE BUFFERPOOL bp1 SIZE 20000 PAGESIZE 16K
4) CREATE TABLE tab1 (fname CHAR(30), lname CHAR(30), resume LONG VARCHAR) How would you create table TAB1 using the fewest number of steps?
A. 1, 2, 3, 4
B. 1, 3, 2, 4
C. 1, 2, 4
D. 1, 4
Answer: D

12. Given the following table spaces, their associated page size, and number of pages: TSP1 - 16K page size, 10000 pages
TSP2 - 8K page size, 10000 pages
TSP3 - 4K page size, 20000 pages
USERSPACE1 - 4K page size, 5000 pages
Which sets of buffer pools will ensure that all of the data in the table spaces will be fit into memory for optimal data access?
A. IBMDEFAULTBP - 4K page size, 85000 pages
B. IBMDEFAULTBP - 4K page size, 25000 pages BP1 -16K page size, 20000 pages
C. BP1 - page size 8K, 45000 pages
D. BP1 - 4K page size, 30000 pages BP2 - 8K page size, 20000 pages BP3 - 16K page size, 15000 pages
Answer: D

13. Given a newly created database and sufficient memory. If the following statements are successful: CREATE BUFFERPOOL bp1 SIZE 40MB;
CREATE BUFFERPOOL bp2 PAGESIZE 16K SIZE 40000;
How much memory will be allocated for buffer pools BP1 and BP2 on a Windows platform?
A. 40MB
B. 80MB
C. 680MB
D. 690MB
Answer: D

14. A database administrator needs to create a federated database and configure access to join data from three Oracle instances and one DB2 database.
Which objects are needed to establish the specified connections?
A. 1 Oracle server , 1 DB2 server , 3 Oracle wrappers , and nicknames for each database
B. 1 Oracle server , 1 DB2 server , 1 Oracle wrapper , 1 DB2 wrapper , and nicknames for each database
C. 3 Oracle servers , 1 DB2 server , 3 Oracle wrappers , 1 DB2 wrapper , and nicknames for each database
D. 3 Oracle servers , 1 DB2 server , 1 Oracle wrapper , 1 DB2 wrapper , and nicknames for each database
Answer: D

15. A database administrator has configured an Oracle data source in a federated database (that references a table named ORA1). Yesterday, Oracle table ORA1 had a 60 percent increase in data records (additional rows were inserted).
How do you update the statistics for table ORA1 at the federated server?
A. Perform the equivalent of RUNSTATS on ORA1 in Oracle.
B. Perform RUNSTATS on the ORA1 nickname at the federated server.
C. Perform the equivalent of RUNSTATS on ORA1 in Oracle; then perform RUNSTATS on the ORA1 nickname.
D. Perform the equivalent of RUNSTATS on ORA1 in Oracle; then drop and re-create the ORA1 nickname.
Answer: D

16. A database administrator would like to examine repartitioning options for a partitioned database named PRODDB. A workload has been captured on the system (Windows) and is stored in a file named WORKLOAD.SQL.
What is the proper command to run the Design Advisor so that it will evaluate the information stored in the file and give advice on re-partitioning?
A. db2advis -d proddb -i workload.sql -P
B. db2advis -d proddb -i workload.sql -partitioning
C. db2advis -d proddb -i workload.sql -m P
D. db2advis -d proddb -i workload.sql -m ALL
Answer: C

17. A database administrator wants to design a multi-partition database that can take advantage of both intra-partition parallelism and inter-partition parallelism.
Which configuration will allow the use of these types of parallelism while using the least number of hardware components (servers, processors)?
A. one server having at least two processors
B. two servers having one processor and one logical database partition
C. one server having four processors and two logical database partitions
D. two servers each having four processors and two logical database partitions
Answer: C

18. If the ADD DBPARTITIONNUM command is used to add a new database partition to an existing DB2 instance, which statement is correct?
A. All existing databases in the instance are expanded to the new database partition but data cannot be stored on the new partition until it has been added to a partition group.
B. Database partition groups within existing databases will automatically include the new database partition and will redistribute their existing data to the new partition.
C. Any single partition databases within the instance will automatically become multi-partition databases once the new partition is added to the instance.
D. A database administrator would need to modify the db2nodes.cfg file to complete the addition of the new partition to the instance.
Answer: A

19. An active partitioned instance named DB2INTS1 has the following db2nodes.cfg:
1 host1 0
2 host1 1
3 host2 0
A new partition is added by executing the following command:
db2start DBPARTITIONNUM 4 ADD DBPARTITIONNUM HOSTNAME host2 PORT 1
What must occur before the db2nodes.cfg file will reflect the new partition?
A. The line "4 host2 1" must be appended to the db2nodes.cfg file by a system administrator.
B. The instance DB2INST1 must be stopped and restarted.
C. The instance DB2INST1 must be quiesced and unquiesced.
D. The db2nodes.cfg file was updated automatically by the db2start command.
Answer: B

20. A database named DB1 has a database partition group named MULTIPG that has been defined across partitions 3, 4 and 5.
A database administrator issues the following series of commands/SQL statements: CONNECT TO db1;
SET CURRENT DBPARTITIONNUM 4;
ALTER DATABASE PARTITION GROUP multigp ADD DBPARTITIONNUM (6); Which partition will DB2 use as a model for table spaces on partition 6?
A. catalog partition
B. partition 3
C. partition 4
D. partition 5
Answer: B