IBM C2090-544 Online Practice Questions and Exam Preparation

IBM C2090-544 Online Questions & Answers

• Question 41:

  A database named MYDB was created by executing the following command: CREATE DATABASE mydb AUTOMATIC STORAGE NO Later, a decision is made to modify this database so that both it and its table spaces use automatic storage. What steps are required to make this modification?

  A. Use the ALTER DATABASE command to convert the database; all existing table spaces will be converted automatically.
  B. Use the ALTER DATABASE command to convert the database; use the ALTER TABLESPACE command to convert all existing table spaces.
  C. Use the ALTER DATABASE command to convert the database; use the ALTER TABLESPACE command to convert all existing SMS table spaces.
  D. Use the ALTER DATABASE command to convert the database; use the ALTER TABLESPACE command to convert all existing DMS table spaces.
  D. Use the ALTER DATABASE command to convert the database; use the ALTER TABLESPACE command to convert all existing DMS table spaces.

• Question 42:

  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);
  B. CREATE UNIQUE INDEX xtab1 ON tab1 (c1) INCLUDE (c2, c3);

• Question 43:

  Given a database with Self-Tuning Memory Manager (STMM) enabled, what will DB2 do when a new buffer pool is created using the IMMEDIATE option?

  A. Attempt to create the buffer pool and immediately request additional memory from the operating system to allocate. If the operating system memory is not available, the buffer pool will not be created.
  B. Attempt to create the buffer pool and immediately allocate the memory requested, resizing the existing buffer pools and heaps as required. If there is insufficient memory in the global memory allocation for DB2, the buffer pool will not be created.
  C. Attempt to create the buffer pool and immediately allocate the memory requested from available database shared memory. If the memory available is not sufficient, then allocation of the buffer pool will be deferred until the database is stopped and restarted.
  D. Attempt to create the buffer pool and immediately request additional instance level memory. If the instance level memory is not available, then creation of the buffer pool is deferred until the database is stopped and restarted.
  C. Attempt to create the buffer pool and immediately allocate the memory requested from available database shared memory. If the memory available is not sufficient, then allocation of the buffer pool will be deferred until the database is stopped and restarted.

• Question 44:

  A DBA 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
  D. 3 Oracle servers , 1 DB2 server , 1 Oracle wrapper , 1 DB2 wrapper , and nicknames for each database

• Question 45:

  

  Table T1 was created by executing the following statement:

  Immediately after creation, table T1 was populated with 10 rows. Later, user USER1 ran an application that inserted 100,000 rows into table T1.

  The following query is frequently ran against the T1 table:

  SELECT deptno, empno, deptname FROM t1 WHERE deptno = 'EN' AND empno = '123' Assuming current statics exist for the table and index, which index will provide optimal performance for this query?

  A. CREATE INDEX idx1 ON t1 (deptno, empno) INCLUDE (deptname)
  B. CREATE INDEX idx1 ON t1 (deptno, empno)
  C. CREATE INDEX idx1 ON t1 (deptno) INCLUDE (empno)
  D. CREATE INDEX idx1 ON t1 (deptno)
  B. CREATE INDEX idx1 ON t1 (deptno, empno)

• Question 46:

  A Windows user with a local database is not able to connect to a zOS database. The System Database Directory and the Node Directory have the correct entries. Other users can connect to the database from their workstations without problems. What should be done resolve the problem?

  A. Uncatalog and re-catalog the directory entries for the zOS database and try again.
  B. Catalog the zOS database in the Local Database Directory.
  C. Catalog the zOS database in the Database Connection Services Directory.
  D. Catalog the zOS database in the Distributed Data Facility Directory.
  C. Catalog the zOS database in the Database Connection Services Directory.

• Question 47:

  A DBA migrating an existing database to DB2 9.7 wants to allow the primary and secondary recovery logs to be used with non-buffered IO. Which statement is true?

  A. This behavior is automatically enabled by default.
  B. The DB2_LOGGER_BUFFERED registry variable must be set to OFF.
  C. The DB2_LOGGER_AUTO_BUFFERED registry variable must be set to YES.
  D. The DB2_LOGGER_NON_BUFFERED_IO registry variable must be set to AUTO.
  A. This behavior is automatically enabled by default.

• Question 48:

  Which type of table queue is used to pass data between database partitions when rows are being fetched?

  A. asynchronous table queue
  B. synchronous table queue
  C. non-merging table queue
  D. listener table queue
  A. asynchronous table queue

• Question 49:

  The DBA needs to create a table with key columns YEARDAY, YEAR, and DAY. This table needs to be partitioned by column YEARDAY with three months per data partition. Additionally, data needs to be organized 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(yearday INT,year CHAR(2),day INT))PARTITION BY RANGE (year) (STARTING 201001 ENDING 201012 EVERY 3)ORGANIZE BY DIMENSIONS (year, day))
  B. CREATE TABLE tab1(yearday INT,year CHAR(2),day INT))PARTITION BY RANGE (yearday) (STARTING 201001 ENDING 201012 EVERY 3)ORGANIZE BY DIMENSIONS (year, day))
  C. CREATE TABLE tab1(yearday INT,year CHAR(2),day INT))PARTITION BY RANGE (yearday) (STARTING 201001 ENDING 201012 EVERY 3)ORGANIZE BY DIMENSIONS (year))
  D. CREATE TABLE tab1(yearday INT,year CHAR(2),day INT))PARTITION BY RANGE (yearday) (STARTING 201001 ENDING 201012 EVERY 3)ORGANIZE BY DIMENSIONS (day))
  C. CREATE TABLE tab1(yearday INT,year CHAR(2),day INT))PARTITION BY RANGE (yearday) (STARTING 201001 ENDING 201012 EVERY 3)ORGANIZE BY DIMENSIONS (year))

• Question 50:

  You have a database with eight partitions on a single server. Immediately after executing the following command:

  CREATE BUFFERPOOL mybp DEFERRED SIZE 3000

  How much memory is consumed by buffer pool MYBP on the server?

  A. 0 pages
  B. 3000 pages
  C. 24000 pages
  D. 192000 pages
  A. 0 pages