Free IBM Db2 12 for z/OS Administrator Practice Questions

Sample Practice Questions

Try these 10 sample questions from the IBM Db2 12 for z/OS Administrator practice bank. Each includes the answer and explanation. Continue with the interactive practice above to review the full question set.

1. 1A DBA has an eligible base table and needs to create its clone at the current server. Which statement and option should the DBA use?

   1. A.ALTER TABLE with ADD CLONECorrect answer: A
   2. B.CREATE TABLE with REFRESH DEFERRED
   3. C.ALTER TABLE with ENABLE ARCHIVE
   4. D.CREATE VIEW with a fullselect

   Explanation: IBM states: "Issue the ALTER TABLE statement with the ADD CLONE option." The other choices define a materialized query table, enable an archive relationship, or create a view rather than a clone.

   Reference: Db2 12 for z/OS: Administration Guide — Chapter 2, Creating a clone table, Procedure

2. 2Which Db2 command displays the status of table spaces and index spaces in a database?

   1. A.DISPLAY DATABASECorrect answer: A
   2. B.DISPLAY BUFFERPOOL
   3. C.DISPLAY THREAD
   4. D.DISPLAY GROUP

   Explanation: IBM says: "The -DISPLAY DATABASE command displays status information about Db2 databases." Its listed objects include table spaces and index spaces. The other commands display thread, group, or buffer-pool information.

   Reference: Db2 12 for z/OS: Command Reference — Chapter 21, DISPLAY DATABASE

3. 3How does a Db2 role relate to privileges and trusted contexts?

   1. A.It groups privileges and is available in a trusted contextCorrect answer: A
   2. B.It groups privileges and is available without a trusted context
   3. C.It groups authorization IDs and is available in a trusted context
   4. D.It groups authorization IDs and is available without a trusted context

   Explanation: IBM says: "A role is a database entity that groups one or more privileges together in a trusted context." It is available only in a trusted context.

   Reference: Db2 12 for z/OS: Managing Security — Chapter 2, Roles in a trusted context

4. 4What does a Db2 trace class identify within a trace type?

   1. A.A group of one or more IFCIDsCorrect answer: A
   2. B.Exactly one IFCID that cannot belong to another class
   3. C.Every IFCID across all trace types
   4. D.A group of trace types that each contain a single IFCID

   Explanation: IBM states: "A trace class is a number that identifies a group of one or more IFCIDs." An IFCID can belong to more than one class.

   Reference: Db2 12 for z/OS: Introduction to Db2 for z/OS — Chapter 3, Traces

5. 5A newly activated Db2 12 function level introduces SQL syntax. In most cases, what application compatibility setting is needed for an application to use that syntax?

   1. A.The equivalent or a higher APPLCOMPAT levelCorrect answer: A
   2. B.A lower APPLCOMPAT level because function-level activation alone suffices
   3. C.Exactly the introducing APPLCOMPAT level and never a higher level
   4. D.The previous Db2 release's APPLCOMPAT level regardless of syntax

   Explanation: IBM states: "In most cases, new SQL syntax and behaviors in a Db2 function level also require the applications to run with the equivalent or higher application compatibility (APPLCOMPAT) level." Function activation and application compatibility are separate controls.

   Reference: Db2 12 for z/OS: Introduction to Db2 for z/OS — Chapter 2, Application compatibility levels in Db2 12 for z/OS

6. 6When a row in a system-period temporal table is updated or deleted, what happens to its prior version?

   1. A.Db2 replaces it in the history table with the new row
   2. B.Db2 stores it only in the primary index
   3. C.Db2 discards it after writing the current row
   4. D.Db2 inserts it into the history tableCorrect answer: D

   Explanation: IBM states: "When you update or delete a row in a system-period temporal table, Db2 inserts the previous version of the row into the history table." The previous committed version is preserved as history.

   Reference: Db2 12 for z/OS: Introduction to Db2 for z/OS — Chapter 2, Creation of temporal tables

7. 7Which CDB table associates a remote server's location name with its TCP/IP or SNA network attributes?

   1. A.SYSIBM.LOCATIONSCorrect answer: A
   2. B.SYSIBM.IPNAMES
   3. C.SYSIBM.USERNAMES
   4. D.SYSIBM.LUNAMES

   Explanation: IBM states: "Each row associates a LOCATION name with the TCP/IP or SNA network attributes for the remote server." The sentence describes SYSIBM.LOCATIONS; the other CDB tables serve related translation and network roles.

   Reference: Db2 12 for z/OS: Managing Security — Chapter 3, SYSIBM.LOCATIONS columns

8. 8What must a DBA do to make a materialized query table available for automatic query rewrite?

   1. A.Define a view and periodically rebuild its indexes
   2. B.Define, populate, and periodically refresh the tableCorrect answer: B
   3. C.Define the table and exchange it with a clone table
   4. D.Define the table and set MOVE_TO_ARCHIVE to Y

   Explanation: IBM states: "To take advantage of automatic query rewrite, you must define, populate, and periodically refresh the materialized query table." The other options confuse query rewrite with clone exchange, archiving, or a view.

   Reference: Db2 12 for z/OS: Administration Guide — Chapter 2, Creating materialized query tables, About this task

9. 9What risk should a DBA consider before issuing CANCEL THREAD for an application updating a NOT LOGGED table space?

   1. A.The updates are always rolled back from the log with no further recovery
   2. B.The table space can enter the logical page list and require recoveryCorrect answer: B
   3. C.The thread remains active until a new image copy has been taken
   4. D.The unlogged updates become logged before the thread is canceled

   Explanation: IBM states: "The CANCEL THREAD command can cause the NOT LOGGED table space to be placed in the LPL, so that the table space must be recovered." The outcome is possible rather than guaranteed.

   Reference: Db2 12 for z/OS: Command Reference — Chapter 15, CANCEL THREAD, Canceling threads on applications that update NOT LOGGED table spaces

10. 10SEPARATE_SECURITY is YES. What is the effect on ordinary SYSADM authority?

    1. A.It automatically gains SECADM's security-object management privileges
    2. B.It becomes installation SYSADM with unrestricted security administration
    3. C.It can manage roles but cannot administer database objects
    4. D.It cannot manage security objects or grant and revoke privileges granted by othersCorrect answer: D

    Explanation: IBM says: "If you set SEPARATE_SECURITY to YES, the SYSADM authority can no longer manage security-related objects" and identifies grants and revocations by others as another removed ability. The separate setting does not change SYSADM into installation SYSADM.

    Reference: Db2 12 for z/OS: Managing Security — Chapter 2, Managing administrative authorities