DB2 10 DBA for z/OS v6.0

Page:    1 / 9   
Total 133 questions Expand All

The EXPLAIN STMTCACHE ALL statement provides information about SQL tuning. Which
information is part of the DSN_STATEMENT_CACHE_TABLE?

  • A. Filter factor information.
  • B. Stage 1 and stage 2 information.
  • C. Number of columns used in an index.
  • D. Number of times anSQL statement is executed.


Answer : D

When converting a multiple table segmented table space to partition-by-growth table
spaces, which statement is correct?

  • A. DSMAX may have to be increased.
  • B. Storage requirements for EDMDBDC will decrease.
  • C. Storage requirements for some applications will increase.
  • D. The dynamic statement cache should be increased to handle the additional SQL statements.


Answer : A

What is the DSNZPARM thatdetermines the maximum amount of temporary storage in the
work file data base for a single user at any given time?

  • A. MAXRBLK
  • B. MAXDBAT
  • C. MAXKEEPD
  • D. MAXTEMPS


Answer : D

You have to design a table and application layout, in which automatic number generationis
used. The application also wants to assign database generated numbers in an SQL
UPDATE statement. Which statement is true?

  • A. Neither Identity nor Sequence can deliver this functionality.
  • B. Identity can deliver this functionality, but Sequence cannot.
  • C. Identity cannot deliver this functionality, but Sequence can.
  • D. Identity and Sequence can both deliver this functionality.


Answer : C

What is a consideration when specifying DATA CAPTURE CHANGES?

  • A. Can be specified for capturing changes to an XML object.
  • B. To minimize logging, specify NOT LOGGED when DATA CAPTURE CHANGES is specified.
  • C. REFRESH TABLE statement is not allowed with a table defined with DATA CAPTURE CHANGES.
  • D. You cannot turn on DATA CAPTURE CHANGES if the table space is in advisoryREORG-pending.


Answer : D

A unique index (IX1) currently exists on COL1, COL2 and COL3 of the table. There is a
request to be able to have an index on these three columns and COL4 to achieve the
optimal access path. The addition of COL4 will not support the uniqueness requirement of
the index. In order to add this column to the index while still preserving the unique
constraint of the first three columns, which option should be taken?

  • A. Add a new index on COL4.
  • B. Perform a ALTER INDEX IX1 ADD COLUMN (COL4).
  • C. Add a new UNIQUE index on COL1, COL2, COL3, COL4.
  • D. Perform an ALTER INDEX IX1 ADD INCLUDE COLUMN (COL4).


Answer : D

Thefollowing 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
thechanges 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


Answer : C

The billing application has a DB2 referential integrity setof three tables. TABA, TABB and
TABC. Daily, Full Image copies are done for the three table spaces related to the three
tables. There is a requirement to recover the three table spaces to noon time, which is
about 8 hours after the full image copies were run. Which one of the following statements is
true about this recovery situation?

  • A. Recovery will need to be done on the children table first.
  • B. No recovery is allowed since the tables have been updated.
  • C. No recovery is allowed, a QUIESCE utility is missing to create a point of consistency.
  • D. BSDS contains information useful to find an appropriate RBA to use for the TORBA value.


Answer : D

Which statement is NOT true?

  • A. REORG table space to materialize pending definition changes for a table space.
  • B. REORG with SHRLEVEL CHANGE would automatically detect which threads need to be canceled before switch phase.
  • C. DSNACCOX can be used for collecting data for table space and index space for getting recommendations when to run REORG, RUNSTATS or COPY.
  • D. REORG of index space could be eliminated because the list prefetch of index leaf pages in DB2 V10 is based on non-leaf page information.


Answer : B

A table space is in AREO*. What does this status mean?

  • A. A full image copy of the tablespace must be taken.
  • B. The table space should be reorganized for optimal performance.
  • C. A START command must be issued to remove it from the pending status.
  • D. The table space must be reorganized to materialize all pending changes.


Answer : B

If a single row of the PLAN_TABLE has a 'Y' value in more than one of the sort composite
columns, what is indicated?

  • A. The next sort step will perform two sorts.
  • B. There are multiple sorts in the plan step.
  • C. One sort in the plan step will accomplish twotasks.
  • D. Two sorts are performed on the new table of a star join.


Answer : C

What START TRACE command provides detailed lock suspend and lock contention trace
information?

  • A. START TRACE(AUDIT)
  • B. START TRACE(STATS) CLASS(1)
  • C. START TRACE(PERFM) CLASS(30) IFCID(44,45)
  • D. START TRACE(ACCTG) CLASS(1,2,3) DEST(SMF)


Answer : C

For which table type could you specify ALTERTABLE tb1 DATA CAPTURE CHANGES?

  • A. Catalog tables
  • B. Directory tables
  • C. Auxiliary tables
  • D. Declared global temporary tables


Answer : A

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;


Answer : B

For monitoring space growth over time, which statistics should NOT be considered?

  • A. SYSIBM.SYSTABLEPART_HIST CARDF, SPACEF SYSIBM.SYSINDEXPART_HIST CARDF, SPACEF
  • B. SYSIBM.SYSTABLEPART CARDF, SPACEF SYSIBM.SYSINDEXPART CARDF, SPACEF
  • C. SYSIBM.SYSTABLESPACE DSSIZE, AVGROWLEN SYSIBM.SYSINDEXES PIECESIZE, AVGKEYLEN
  • D. SYSIBM.SYSTABLESPACESTATS SPACE SYSIBM.SYSINDEXSPACESTATS SPACE


Answer : C

Page:    1 / 9   
Total 133 questions Expand All

Talk to us!


Have any questions or issues ? Please dont hesitate to contact us