IBM C2090-612 Online Practice Questions and Exam Preparation

IBM C2090-612 Online Questions & Answers

• Question 81:

  The following DDL statements are executed to create the following objects: CREATE TABLESPACE TS1 BUFFERPOOL BP0 IN DB1; CREATE TABLE USER1.TB1 ( COL1 INTEGER, COL2 VARCHAR(10) ) IN DB1.TS1; CREATE INDEX USER1.IX1 ON USER1.TB1 ( COL2 ) BUFFERPOOL BP0 COPY YES; Next, the following ALTER statements are done (in order): 1) ALTER TABLESPACE DB1.TS1 BUFFERPOOL BP8K0 MAXPARTITIONS 20; 2) ALTER TABLESPACE DB1.TS1 SEGSIZE 64; 3) ALTER INDEX USER1.IX1 BUFFERPOOL BP16K0; Which of the following will materialize all of the changes with the least amount of work?

  A. Nothing needs to be done.
  B. REORG INDEX USER1.IX1 SHRLEVEL CHANGE
  C. REORG TABLESPACE DB1.TS1 SHRLEVEL REFERENCE
  D. REORG TABLESPACE DB1.TS1 SHRLEVEL REFERENCE REORG INDEX USER1.IX1 SHRLEVEL CHANGE
  C. REORG TABLESPACE DB1.TS1 SHRLEVEL REFERENCE

• Question 82:

  Given the following view definition: CREATE VIEW EMPD AS (SELECT D.DEPTNAME,

  A. LASTNAME FROM DEPT D INNER JOIN EMP E ON D.DEPTNO = E.WORKDEPT); Can an UPDATE statement be used to update the view EMPD on joined tables DEPT and EMP?
  B. No, an UPDATE statement against the EMPD view is not allowed at all.
  C. Yes, the view EMPD can be updated directly via an UPDATE statement.
  D. No, only a clone of the view EMPD can be updated via an UPDATE statement.
  E. Yes, an UPDATE statement against the EMPD view is allowed if an INSTEAD OF trigger is defined on the view.
  D. No, only a clone of the view EMPD can be updated via an UPDATE statement.

• Question 83:

  When these two commands are issued in sequence, one after the other: STOP FUNCTION SPECIFIC(A.B) ACTION(REJECT) STOP FUNCTION SPECIFIC(A.B) ACTION(QUEUE) What will occur?

  A. Function A.B is stopped and queues all access.
  B. Function A.B is stopped and rejects all access.
  C. Function A.B is stopped and rejects all access until we start the function or run out of installation timeout.
  D. Function A.B is stopped and queues all access until we start the function or run out of installation timeout.
  D. Function A.B is stopped and queues all access until we start the function or run out of installation timeout.

• Question 84:

  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;
  B. ALTER TABLE TB1 ADD COLUMN COLC DATE;

• Question 85:

  When is a merge scan join a well performing access path?

  A. When the number of qualifying rows of the inner and outer table are both large.
  B. When the query references at least two dimensions and the STARJOIN subsystem parameter is 1.
  C. When the number of rows in the outer table is small and the number of pages accessed in the inner table is small.
  D. When the matching columns of the inner table are in a non-clustering index or the outer table has duplicate qualifying rows.
  A. When the number of qualifying rows of the inner and outer table are both large.

• Question 86:

  A table is created using the ORGANIZED BY HASH UNIQUE syntax, what action should occur?

  A. Specify the column names to be hashed.
  B. Create a unique index on the hash columns.
  C. Create a clustering index on the hash columns.
  D. Create a unique clustering index on the hash columns.
  A. Specify the column names to be hashed.

• Question 87:

  The available window to reorganize table spaces is being reduced. What data can provide information to allow for a priority list of candidate table spaces for reorganization based on recent activity with no recent RUNSTATS execution?

  A. SYSIBM.SYSCOPY
  B. SYSIBM.SYSTABLES
  C. SYSIBM.SYSINDEXES
  D. SYSIBM.SYSTABLESPACESTATS
  D. SYSIBM.SYSTABLESPACESTATS

• Question 88:

  Which DSNZPARM parameter controls the number of locks that may be acquired on a table space?

  A. NUMLKUS
  B. MAXUSRS
  C. NUMLKTS
  D. MXDTCACH
  C. NUMLKTS

• Question 89:

  You want to ALTER a table space specifying MAXPARTITIONS 256. For which table space type does the ALTER fail?

  A. Simple table space with one table.
  B. Segmented table space with one table.
  C. Classic partitioned table space with one table.
  D. Partitioned-by-growth table space with one table.
  C. Classic partitioned table space with one table.

• Question 90:

  What is the one significant difference between sequence objects and identity columns?

  A. Identity columns can cache values and sequence objects cannot.
  B. Identity columns can be defined on tables and sequence objects cannot.
  C. Sequence objects can have a character data type and identity columns cannot.
  D. Sequence object values can be used as primary keys and identity column values cannot.
  B. Identity columns can be defined on tables and sequence objects cannot.