IBM IBM Certified Advanced Database Administrator C2090-611 Questions & Answers

• Question 11:

  Given the following command: LOAD FROM newrows.del OF DEL INSERT INTO mytab ALLOW READ ACCESS Which statement(s) must be executed before applications can query the newly loaded data?

  A. SET INTEGRITY FOR mytab IMMEDIATE CHECKED

  B. ALTER TABLE mytab CHECK (COL IS VALIDATED)

  C. ALTER TABLE mytab ALLOW READ ACCESS IMMEDIATE CHECKED

  D. ALTER TABLE mytab ALLOW READ ACCESS;SET INTEGRITY FOR mytab IMMEDIATE UNCHECKED;

  Correct Answer: A

• Question 12:

  What is the purpose of specifying the RESTART CONTINUE option in an INGEST command?

  A. To terminate a failed INGEST command.

  B. To restart a failed INGEST command from the beginning.

  C. To clean up the log records of a failed INGEST command.

  D. To restart a failed INGEST command from the last commit point.

  Correct Answer: D

• Question 13:

  If the following statement is executed: REORGCHK UPDATE STATISTICS ON TABLE ALL What will happen?

  A. RUNSTATS will be executed on all user tables and a REORGCHK report will be generated.

  B. RUNSTATS will be executed on all user and system tables and a REORGCHK report will be generated.

  C. RUNSTATS will be executed on all user tables only if statistics have not been collected and a REORGCHK report will be generated.

  D. RUNSTATS will be executed on all system tables only if statistics have not been collected and a REORGCHK report will be generated.

  Correct Answer: B

• Question 14:

  Table TAB_A resides within table space TBSP_1.

  

  Which statement is correct?

  A. Using the COPY NO option means the data in any table within table space TBSP_1 cannot be updated or deleted until either a table spacebackup of TBSP_1 or a full database backup is completed.

  B. Using the ALLOW READ ACCESS option along with INCREMENTAL indexing mode means that the newly inserted data can be accessedbecause of the new CURRENTLY COMMITTED semantics.

  C. Using the COPY NO and ALLOW READ ACCESS options means that the existing table data is only readable until the index build phase begins,since no copy of the existing data is made during the LOAD.

  D. Using the INSERT INTO and the INCREMENTAL indexing mode allows DB2 to increase the concurrency of table access during the LOADoperation because LOAD will always append the inserted data to the end of the table and the new index pages are also added to the end ofthe existing index when using INCREMENTAL indexing mode.

  Correct Answer: A

• Question 15:

  What is the FLUSH PACKAGE CACHE statement used for?

  A. It saves dynamic SQL stored in the package cache to an external file.

  B. To remove all cached static SQL statements from the package cache.

  C. To remove all cached dynamic SQL statements from the package cache.

  D. It causes all SQL statements in the package cache that are currently in use to be recompiled.

  Correct Answer: C

• Question 16:

  Which statement about the REORGCHK command is true?

  A. It cannot be run against the system catalog.

  B. It automatically calls RUNSTATS by default.

  C. It cannot be run against multidimensional clustering tables.

  D. It automatically calls REORG if it is determined that a reorg is necessary.

  Correct Answer: B

• Question 17:

  You are tasked with using the Export, Import, and Load utilities to move data from a UNIX platform database to a Windows platform database. What file format is recommended for moving data across platforms?

  A. IXF

  B. DEL

  C. ASC

  D. WSF

  Correct Answer: A

• Question 18:

  What is the purpose of the Design Advisor?

  A. To recommend physical changes to objects in a database.

  B. To recommend database configuration parameter changes.

  C. To generate profiles for autonomic activity within the database.

  D. To generate query definitions required to create usage reports.

  Correct Answer: A

• Question 19:

  Table TAB_A resides within table space TBSP_1.

  

  Which statement is correct?

  A. TAB_A is a table that has implicitly hidden columns and the file DEL_FILE contains data for both regular columns and hidden columns. Whilenew data is being loaded into TAB_A, applications will be able to read the data that existed prior to the LOAD operation.

  B. A flash copy of TAB_A will be made at the beginning of the LOAD because of the COPY YES option. This copy allows applications to read thedata that existed in the table prior to the LOAD operation and improves performance since the read operations will not collide with the LOAD.

  C. The indexes for TAB_A will not be completely rebuilt because the INCREMENTAL indexing mode is used. Instead, new index keys will beappended to the original indexes. This will improve concurrency for the applications that are reading TAB_A during the LOAD operation.

  D. The indexes for TAB_A will be copied at the beginning of the LOAD operation because of the COPY YES option. Since the indexes will also bebuilt using the INCREMENTAL indexing mode, the concurrency of table TAB_A will be maximized for any applications that wish to access thetable.

  Correct Answer: A

• Question 20:

  What authority must be held to create a trusted context?

  A. DBADM

  B. SQLADM

  C. SECADM

  D. CONNECT

  Correct Answer: C