IBM IBM Certified Database Administrator C2090-612 Questions & Answers

• Question 61:

  In using plan stability, what storage space requirement should be of carefully monitored?

  A. MINSTOR

  B. DSNDB07

  C. DSNDB01.SPT01

  D. DSN_STATEMNT_TABLE

  Correct Answer: C

• Question 62:

  The performance of BP5 is being evaluated. The Page residency time is being used to see if VPSIZE should be changed. The statistics that are available are VPSIZE of 90,000 and VPSEQT of 15 %. There are 4458 synch I/O per second and 1696 async I/O per second. 6154 total pages read. The system page residency time is 14.6 seconds. Based on a system page residency time of 14.6 seconds, what should be done to increase page residency time?

  A. Increase DWQT to 10.

  B. Decrease VPSIZE by 45,000 pages.

  C. Increase VPSIZE to 180,000 pages.

  D. Change the buffer pool to use PGFIX(YES).

  Correct Answer: C

• Question 63:

  Which DB2 performance feature is designed specifically to reduce the number of indexes that need to be created?

  A. Inline LOBs.

  B. Include columns.

  C. Hash row access.

  D. Direct row access.

  Correct Answer: B

• Question 64:

  When determining what page size will be best for index compression, which utility can be used to provide information on space saved and potential buffer space wasted for each of the different page sizes?

  A. DIAGNOSE

  B. DSN1COMP

  C. DSN1PRNT

  D. DSN1LOGP

  Correct Answer: B

• Question 65:

  Which of the items listed below is NOT a prerequisite for using CURRENT EXPLAIN MODE?

  A. The dynamic statement cache is enabled.

  B. The client application contains some explainable statements.

  C. The PLAN_TABLE and DSN_STATEMENT_CACHE tables must be created using a EBCDIC encoding scheme.

  D. Both PLAN_TABLE and DSN_STATEMENT_CACHE_TABLE exist on the DB2 server and the table names are qualified with the current SQLID that is used when running the application.

  Correct Answer: C

• Question 66:

  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.

  Correct Answer: A

• Question 67:

  The I/O patterns for objects in database NEWBILL need to be reviewed in detail. All of the NEWBILL work runs under a single plan, NEWBILLPL. What START TRACE command should be used to gather the I/O information needed for the objects in database NEWBILL?

  A. START TRACE(AUDIT) PLAN(NEWBILLPL)

  B. START TRACE(IO) CLASS(ALL) PLAN(NEWBILLPL)

  C. START TRACE(PERFM) CLASS(3) PLAN(NEWBILLPL)

  D. START TRACE(ACCT) CLASS(1,2,3) PLAN(NEWBILLPL)

  Correct Answer: C

• Question 68:

  In order to properly monitor locks on a continual basis, which action should be performed but will avoid using excessive CPU resources to gather the information?

  A. Turn on a monitor trace.

  B. Always have a performance trace running.

  C. Perform a DISPLAY DATABASE (LOCKS ) command often.

  D. Always have statistics classes 1,,3, and 4 and accounting classes 1 and 3 running.

  Correct Answer: D

• Question 69:

  Poorly tuned memory can affect application performance, such as having sequential prefetch turned off. Which statistic should you monitor from the statistics report to be sure this is not occurring?

  A. PREF.DISABLED-NO BUFFER

  B. PREF.DISABLED-NO READ ENG

  C. BUFF.UPDATES/PAGES WRITTEN

  D. PAGES WRITTEN PER WRITE I/O

  Correct Answer: A

• Question 70:

  In determining whether a REORG on an index space would improve performance, which two of the following columns would NOT be needed? (Choose two.)

  A. Column CLUSTERING in SYSIBM.SYSINDEXES

  B. Column LEAFDIST in SYSIBM.SYSINDEXPART

  C. Column CLUSTERRATIOF in SYSIBM.SYSINDEXES

  D. Column LASTUSED in SYSIBM.SYSINDEXSPACESTATS

  E. Columns LEAFNEAR and LEAFFAR in SYSIBM.SYSINDEXPART

  Correct Answer: AD