IBM IBM Certified Database Administrator C2090-612 Questions & Answers

• Question 101:

  When a table space defined as both partitioned-by-growth (PBG) and MEMBER CLUSTER YES is altered to MEMBER CLUSTER NO by the ALTER TABLESPACE command. Which statement is correct?

  A. DB2 does not allow the command.

  B. DB2 applies the changes immediately.

  C. DB2 allows the command only if the table is empty.

  D. DB2 places the table space in AREOR (Advisory Reorg).

  Correct Answer: D

• Question 102:

  Given the following statements have been run successfully and the buffer pools BP1 and BP2 both have the same page size: CREATE TABLESPACE TS1 IN DB1 DEFINE YES BUFFERPOOL BP1 MAXPARTITIONS 15 MEMBER CLUSTER ; CREATE TABLE TB1 (COL1 INT NOT NULL WITH DEFAULT) IN DB1.TS1 ; COMMIT ; ALTER TABLESPACE TS1 BUFFERPOOL BP2 MAXPARTITIONS 20 ; What is the next logical step in the sequence to CREATE an index IX1 on COL1 on TB1?

  A. ALTER TABLESPACE TS1 MAXPARTITIONS 15;

  B. COMMIT;

  C. CREATE INDEX IX1 ON TB1 (COL1);

  D. REORG TABLESPACE TS1;

  Correct Answer: C

• Question 103:

  When defining a foreign key on a table, which statement is true?

  A. It is not mandatory to have an index on the foreign key columns but it can help to avoid locking problems.

  B. There is no need to define an index on the foreign key columns as DB2 cannot use an index for foreign key checking.

  C. It is not mandatory to have an index on the foreign key columns but it can help performance when you insert rows into this table.

  D. You must define an index on the foreign key columns where the index columns are the same as the foreign key columns to guarantee uniqueness of the foreign key.

  Correct Answer: A

• Question 104:

  If a ROWID column is defined on a table as GENERATED BY DEFAULT, what must also be done?

  A. Nothing because DB2 will control the values.

  B. A unique index must be defined on the ROWID column.

  C. An identity column must also be created to control uniqueness.

  D. Extra space must be allocated for an overflow area in the table space.

  Correct Answer: B

• Question 105:

  What should be implemented in order to audit all the activity of the install SYSADM?

  A. DATA CAPTURE CHANGES should be set on all tables.

  B. A DB2 audit policy should be established, identifying the SYSADM ID to be audited.

  C. The DB2 authorization exit should be modified to convert the install SYSADM ID to another ID.

  D. Nothing can be done, the install SYSADM has the highest authority and is not controlled or monitored.

  Correct Answer: B

• Question 106:

  Which attribute(s) determine the absolute maximum number of partitions for a partition-by- growth table space?

  A. MAXPARTITIONS

  B. MAXPARTITIONS, DSSIZE

  C. MAXPARTITIONS, page size

  D. MAXPARTITIONS, DSSIZE, page size

  Correct Answer: D

• Question 107:

  Which ALTER TABLE statement will generate a new table space version?

  A. ALTER TABLE ... ADD PARTITION ...

  B. ALTER TABLE ... ADD COLUMN COL2 ...

  C. ALTER TABLE ... DATA CAPTURE CHANGES ...

  D. Altering an already existing VARCHAR column on the table to shorten its maximum length.

  Correct Answer: B

• Question 108:

  Given that the following table (TB1) has been created with this statement: CREATE TABLE TB1 (COLA SMALLINT NOT NULL WITH DEFAULT ,COLB CHAR(5) NOT NULL WITH DEFAULT) IN DB1.TS1; Which of the following statements, which alter a table (TB1), will NOT invalidate all the plans, packages and dynamic cache statements associated with the table TB1?

  A. ALTER TABLE TB1 AUDIT CHANGES;

  B. ALTER TABLE TB1 ADD COLUMN COLC DATE;

  C. ALTER TABLE TB1 RENAME COLUMN COLA TO COLC;

  D. ALTER TABLE TB1 ADD COLUMN COLC CHAR(8) NOT NULL WITH DEFAULT AS SECURITY LABEL;

  Correct Answer: B

• Question 109:

  Which authority level allows a user to run utilities that change the data in an application database?

  A. DBCTRL

  B. SYSOPR

  C. DBMAINT

  D. INSTALL SYSOPR

  Correct Answer: A

• Question 110:

  Which trace gives information about frequent log space shortages?

  A. Audit trace class(8)

  B. Statistics trace class(3)

  C. Accounting trace class(10)

  D. Performance trace class(4)

  Correct Answer: B