Chapter 17 Quiz: DB2 Utilities — REORG, RUNSTATS, COPY, RECOVER, LOAD, UNLOAD

Test your understanding of the six core DB2 utilities. Select the best answer for each question.

Question 1

Which SHRLEVEL option for the z/OS REORG utility allows full read and write access during reorganization?

A) SHRLEVEL NONE B) SHRLEVEL REFERENCE C) SHRLEVEL CHANGE D) SHRLEVEL CONCURRENT

Question 2

What is the purpose of a MAPPINGTABLE in a z/OS REORG with SHRLEVEL CHANGE?

A) It stores the compression dictionary for the tablespace B) It tracks the correspondence between old and new Record Identifiers during reorganization C) It maps column names to their physical positions in the tablespace D) It records the before and after images of modified rows for logging

Question 3

After running REORG with LOG NO on z/OS, the tablespace enters which status?

A) RECOVER-pending (RECP) B) CHECK-pending (CHKP) C) COPY-pending (COPY) D) REBUILD-pending (RBDP)

Question 4

On DB2 LUW, which command checks whether a table needs reorganization?

A) REORG TABLE ... INPLACE REPORTONLY B) CALL SYSPROC.REORGCHK_TB_STATS('T', 'schema.table') C) SELECT REORG_PENDING FROM SYSCAT.TABLES D) ADMIN_CMD('REORG CHECK TABLE schema.table')

Question 5

What does the KEYCARD option in RUNSTATS collect?

A) The number of unique index key values for each prefix of a compound index B) The cardinality of the primary key only C) The number of index leaf pages containing key values D) A count of null values in each indexed column

Question 6

Why are column group statistics important for the optimizer?

A) They reduce the size of the catalog tables B) They capture correlations between columns that individual column statistics miss C) They replace the need for index statistics D) They are required for parallel query execution

Question 7

What is the correct maintenance sequence for a tablespace?

A) COPY, REORG, RUNSTATS B) RUNSTATS, REORG, COPY C) REORG, COPY, RUNSTATS D) REORG, RUNSTATS, COPY

Question 8

On z/OS, what does the COPY utility's FULL NO option produce?

A) A partial copy containing only modified pages since the last full or incremental copy B) A copy of only the index data, not the table data C) A copy that excludes LOB columns D) A compressed copy at reduced fidelity

Question 9

Which z/OS COPY parameter enables instant copy technology on supported storage subsystems?

A) FLASHCOPY YES B) CONCURRENT YES C) INSTANT YES D) SNAPCOPY YES

Question 10

On DB2 LUW, what is the difference between an INCREMENTAL backup and an INCREMENTAL DELTA backup?

A) INCREMENTAL copies all pages modified since the last full backup; DELTA copies pages modified since the last backup of any type B) INCREMENTAL copies data only; DELTA includes indexes C) INCREMENTAL is online; DELTA is offline D) There is no difference; they are synonyms

Question 11

What happens during z/OS RECOVER TABLESPACE when no TOCOPY, TORBA, or TOLOGPOINT is specified?

A) DB2 recovers to the most recent full image copy only B) DB2 recovers to the last committed transaction using the latest copies and log records C) DB2 prompts the DBA to specify a recovery point D) The recovery fails because a target must be specified

Question 12

After a point-in-time recovery of a tablespace on z/OS, what is the status of associated indexes?

A) They are automatically rebuilt during recovery B) They enter REBUILD-pending (RBDP) status and must be rebuilt manually C) They remain valid because indexes are recovered independently D) They are dropped and must be recreated

Question 13

On DB2 LUW, what is the correct two-step process for database recovery?

A) BACKUP, then RECOVER B) RESTORE, then ROLLFORWARD C) RECOVER, then RESTART D) IMPORT, then REBUILD

Question 14

Which LOAD option on z/OS appends new data to existing rows in a table?

A) RESUME YES B) REPLACE C) APPEND YES D) INSERT MODE

Question 15

On DB2 LUW, what does LOAD FROM cursor_name OF CURSOR INSERT INTO table accomplish?

A) Loads data from an external cursor file B) Loads the result set of a declared SQL cursor into the target table C) Creates a cursor on the loaded data for subsequent processing D) Loads data while maintaining a cursor position for restart

Question 16

Compared to equivalent INSERT statements, the LOAD utility is faster primarily because:

A) LOAD uses a more efficient SQL parser B) LOAD writes directly to tablespace pages, bypasses buffer pool management, and generates minimal log records C) LOAD compresses data more efficiently during insertion D) LOAD uses parallelism while INSERT is single-threaded

Question 17

What does the ENFORCE CONSTRAINTS option do during a z/OS LOAD?

A) Creates constraints on the target table if they do not exist B) Validates referential integrity constraints during the load, preventing CHECK-pending status C) Enforces unique constraints only, ignoring referential integrity D) Locks all parent tables to prevent modifications during the load

Question 18

On DB2 LUW, which EXPORT format preserves column data types and metadata for DB2-to-DB2 transfers?

A) DEL (delimited) B) ASC (fixed-position) C) IXF (DB2 exchange format) D) WSF (worksheet format)

Question 19

What is the purpose of the z/OS UNLOAD utility's SYSPUNCH output dataset?

A) It contains error messages from the unload operation B) It receives a generated LOAD control statement that can reload the unloaded data C) It stores the data dictionary for the unloaded table D) It records the number of rows unloaded per partition

Question 20

What does the z/OS TEMPLATE utility provide when used with REORG and COPY?

A) Pre-built JCL templates for common maintenance tasks B) Dynamic dataset name patterns using substitution variables like &SN. and &DATE. C) A graphical interface for utility configuration D) Template-based compression dictionaries for tablespace data

Question 21

You run RUNSTATS with SAMPLE 25 on a 500 GB tablespace. What does SAMPLE 25 mean?

A) Collect statistics for the 25 most active tables B) Sample 25 rows from each page C) Scan 25% of the data pages and extrapolate statistics for the full tablespace D) Run RUNSTATS 25 times and average the results

Question 22

In the Meridian Bank maintenance plan, which tables receive daily RUNSTATS but only weekly REORG?

A) Reference tables like BRANCHES and PRODUCTS B) Archive tables that rarely change C) High-churn OLTP tables like TRANSACTIONS and DAILY_BALANCES D) Temporary staging tables

Question 23

A tablespace has its last full image copy taken 10 days ago. Archive logs are retained for 7 days. What is the recovery risk?

A) No risk; the full copy is sufficient for recovery B) Recovery will be slow but possible using the 10-day-old copy C) The tablespace is unrecoverable because the required archive logs between the copy and the oldest available log have been deleted D) The incremental copies will fill the gap between the full copy and available logs

Question 24

On DB2 LUW, what does ROLLFORWARD DATABASE meridiandb TO END OF LOGS AND COMPLETE do?

A) Applies all available log records to bring the database to the most recent committed state, then marks it as ready for connections B) Writes all buffered data to the transaction log C) Backs up the database and then rolls forward D) Rolls the database forward and then takes a new backup

Question 25

Which of the following is TRUE about DB2 LUW's automatic statistics collection (AUTO_RUNSTATS)?

A) It replaces the need for any manual RUNSTATS execution B) It monitors data modification activity and automatically runs RUNSTATS when statistics appear stale C) It collects column group statistics automatically for correlated columns D) It runs RUNSTATS on a fixed schedule every 24 hours

Answer Key

  1. C — SHRLEVEL CHANGE allows both read and write access during REORG by capturing log records for concurrent changes and applying them in a brief drain phase.

  2. B — The mapping table tracks old-to-new RID correspondence so that concurrent operations can locate rows that have been moved during the reorganization.

  3. C — COPY-pending status means the tablespace is readable but not recoverable until a full image copy is taken.

  4. B — REORGCHK_TB_STATS evaluates table and index statistics against reorganization formulas (F1-F8) and flags thresholds that are exceeded.

  5. A — KEYCARD collects the number of distinct values for each prefix of a compound index key, which the optimizer uses to estimate selectivity for partial key predicates.

  6. B — Column group statistics capture inter-column correlations (e.g., COUNTRY almost always determines CURRENCY), preventing the optimizer from multiplying individual selectivities that can dramatically underestimate result sizes.

  7. D — REORG first (eliminate fragmentation), RUNSTATS second (collect statistics on clean data), COPY third (back up the reorganized, statistics-current state).

  8. A — FULL NO produces an incremental image copy containing only pages modified since the last full or incremental copy.

  9. A — FLASHCOPY YES instructs DB2 to use the storage subsystem's instant copy technology (available on IBM DS8000 and similar).

  10. A — INCREMENTAL captures all changes since the last full backup; DELTA captures only changes since the last backup of any kind (full, incremental, or delta).

  11. B — Without a specific target, RECOVER restores from the most recent copies and applies all available log records to bring the tablespace current.

  12. B — After point-in-time recovery, indexes are out of sync with the tablespace and enter REBUILD-pending status. REBUILD INDEX must be run to restore them.

  13. B — RESTORE reads the backup image; ROLLFORWARD applies archive and active log records to advance the database to the desired point.

  14. A — RESUME YES appends loaded data to existing table rows. REPLACE deletes existing data before loading.

  15. B — LOAD FROM cursor OF CURSOR loads the result set of a previously declared SQL cursor, enabling data movement without intermediate files.

  16. B — LOAD achieves its speed advantage by writing directly to tablespace pages (bypassing the buffer pool and SQL engine), building indexes in bulk sort passes, and generating minimal log records.

  17. B — ENFORCE CONSTRAINTS validates referential integrity during the load, ensuring the table does not end up in CHECK-pending status.

  18. C — IXF (Integrated Exchange Format) preserves complete column metadata including data types, lengths, and nullability.

  19. B — SYSPUNCH receives a generated LOAD control statement matching the unloaded data layout, enabling easy round-trip data movement.

  20. B — TEMPLATE provides dynamic dataset naming using substitution variables (&SN. for space name, &DATE. for current date, etc.), eliminating the need to hard-code dataset names for each tablespace.

  21. C — SAMPLE 25 scans 25% of the data pages and extrapolates statistics for the full tablespace, trading some accuracy for significantly reduced execution time.

  22. C — High-churn tables need daily statistics updates because their data changes significantly each day, but a full REORG is only needed weekly because fragmentation accumulates more gradually.

  23. C — The gap between the 10-day-old copy and the 7-day log retention means 3 days of log records are missing. Recovery cannot bridge this gap, making the tablespace unrecoverable.

  24. A — ROLLFORWARD TO END OF LOGS applies all available log records to the most recent committed state. AND COMPLETE marks the database as consistent and available for connections.

  25. B — AUTO_RUNSTATS monitors modification activity and triggers RUNSTATS automatically when it detects that statistics are becoming stale. It does not replace the need for explicit RUNSTATS for custom distribution or column group statistics.