Chapter 9 Quiz: DB2 Utilities for the COBOL Developer

Questions

Question 1

What is the primary purpose of the REORG utility?

A) To create backup copies of tablespace data B) To restore physical data order, reclaim space, and refresh compression dictionaries C) To collect optimizer statistics for access path selection D) To verify referential integrity constraints

Answer: B REORG reorganizes data in a tablespace to restore clustering order, reclaim space from deleted rows, and rebuild compression dictionaries. Backup copies are created by COPY, statistics by RUNSTATS, and RI verification by CHECK DATA.

Question 2

In an online REORG with SHRLEVEL CHANGE, what is the purpose of the mapping table?

A) It stores the REORG utility parameters for restart purposes B) It maps old column names to new column names after an ALTER TABLE C) It tracks changes made to the original data during the BUILD phase so they can be applied to the reorganized copy D) It maps logical partitions to physical storage locations

Answer: C The mapping table records all INSERT, UPDATE, and DELETE operations that occur on the original data while REORG is building the shadow (reorganized) copy. During the SWITCH phase, these changes are applied to ensure no data is lost.

Question 3

Which REORG parameter determines what happens if the SWITCH phase drain fails after all retries are exhausted?

A) DRAIN_WAIT B) RETRY_DELAY C) TIMEOUT D) SHRLEVEL

Answer: C TIMEOUT determines the behavior when drain fails: TIMEOUT TERM terminates the REORG utility (leaving applications running), while TIMEOUT ABEND terminates the applications blocking the drain. TIMEOUT TERM is the recommended setting.

Question 4

Why should RUNSTATS always be followed by a REBIND of affected packages?

A) REBIND applies the statistics to the DB2 catalog B) Without REBIND, the optimizer still uses the access paths chosen with the old statistics C) REBIND validates that the statistics are correct D) RUNSTATS places packages in an invalid state that REBIND resolves

Answer: B Packages contain frozen access paths determined at bind time using the statistics available then. RUNSTATS updates the catalog statistics, but existing packages continue using their old access paths until they are rebound. Only REBIND causes the optimizer to reconsider access paths with the new statistics.

Question 5

What is the purpose of COLGROUP statistics in RUNSTATS?

A) To collect statistics on columns that belong to the same column group defined in the CREATE TABLE statement B) To measure the combined cardinality of multiple columns for predicates that reference them together C) To group columns for compression analysis D) To identify columns that should be included in a composite index

Answer: B COLGROUP collects multi-column cardinality statistics. Without COLGROUP, the optimizer assumes column values are independent and multiplies individual selectivities, which produces incorrect estimates when columns are correlated (e.g., STATE and ZIP_CODE).

Question 6

What is the difference between a full image copy and an incremental image copy?

A) A full copy is compressed; an incremental copy is not B) A full copy includes all pages; an incremental copy includes only pages changed since the last copy C) A full copy goes to tape; an incremental copy goes to DASD D) A full copy backs up the tablespace; an incremental copy backs up the indexes

Answer: B A full image copy captures every page in the tablespace. An incremental image copy captures only pages modified since the last full or incremental copy. Full copies are the baseline for recovery; incremental copies reduce backup time but increase recovery time because multiple copies must be applied.

Question 7

Why should you always take dual image copies to different media?

A) DB2 requires at least two copies to register in the catalog B) If one copy media fails, you still have a backup for recovery C) Dual copies improve recovery performance through parallel restore D) One copy is for the primary site and one for the disaster recovery site

Answer: B Dual copies to different media (e.g., DASD and tape) protect against media failure of a single copy. If you maintain only one copy and that media fails, your backup is lost and recovery may be impossible for the period since the previous copy.

Question 8

What does RECOVER TORBA accomplish?

A) Recovers a tablespace to the state at a specific Relative Byte Address in the log B) Recovers a tablespace to the state at the time of a specific image copy C) Recovers a tablespace to the current point using the most recent copies and all available logs D) Recovers a tablespace by rebuilding it from the active log only

Answer: A RECOVER TORBA performs point-in-time recovery to a specific log position identified by its Relative Byte Address. DB2 restores the most appropriate image copy and applies log records up to (but not past) the specified RBA, restoring the tablespace to that exact moment.

Question 9

After a point-in-time recovery of a parent tablespace, why must you also recover all dependent tablespaces to the same point?

A) DB2 automatically places dependent tablespaces in RECOVER-pending status B) The dependent tablespaces may contain foreign key rows pointing to parent rows that no longer exist in the recovered parent C) The indexes on dependent tablespaces become corrupted during parent recovery D) DB2 requires all tablespaces in a database to be at the same recovery point

Answer: B Point-in-time recovery rolls the parent tablespace back to an earlier state. Dependent tablespaces still reflect the current state and may contain rows with foreign keys referencing parent rows that were inserted after the recovery point — rows that no longer exist in the recovered parent. This creates orphan rows and referential integrity violations.

Question 10

What is the advantage of LOAD with LOG NO compared to SQL INSERT statements?

A) LOAD with LOG NO validates referential integrity constraints; INSERT does not B) LOAD with LOG NO generates more log data but runs faster due to parallel processing C) LOAD with LOG NO bypasses logging, making it 5-10x faster, but requires an image copy afterward since the load cannot be recovered through the log D) LOAD with LOG NO automatically creates indexes; INSERT requires separate index creation

Answer: C LOG NO tells LOAD not to log individual row inserts, which dramatically improves performance (typically 5-10x faster than equivalent INSERTs). The trade-off is that you cannot recover through the LOAD using logs — you must take an image copy after the LOAD completes to have a recovery point.

Question 11

What is the risk of LOAD REPLACE if the job fails mid-execution?

A) The table will contain duplicate rows B) The table will have a mix of old and new data with no way to distinguish them C) The original data was deleted at the start and the load is incomplete, leaving a partially loaded table that cannot be recovered through logs if LOG NO was specified D) The indexes will be in CHECK-pending status

Answer: C LOAD REPLACE deletes all existing rows before loading new data. If the job fails partway through with LOG NO, you have a partially loaded table. Since LOG NO means the changes aren't logged, you can't roll back. You need the pre-LOAD image copy to recover the original data.

Question 12

What does the STATISTICS YES parameter do when specified on a REORG or LOAD utility statement?

A) It writes utility execution statistics to SMF B) It runs inline RUNSTATS at the end of the utility, collecting optimizer statistics without requiring a separate RUNSTATS job C) It compares before and after statistics to verify the utility improved performance D) It validates that the catalog statistics are consistent with the actual data

Answer: B STATISTICS YES activates inline RUNSTATS, which collects optimizer statistics as part of the utility execution. This eliminates the need for a separate RUNSTATS job and ensures statistics are immediately current after the REORG or LOAD completes.

Question 13

What does CHECK DATA detect?

A) Corrupted data pages in a tablespace B) Orphan rows that violate referential integrity constraints and check constraint violations C) Missing index entries for data rows D) Stale optimizer statistics

Answer: B CHECK DATA verifies referential integrity (RI) constraints and table check constraints. It detects orphan rows — rows in dependent tables whose foreign key values don't match any row in the parent table — and rows that violate check constraints. Index consistency is verified by CHECK INDEX, not CHECK DATA.

Question 14

When should you run REBUILD INDEX?

A) After every REORG to ensure indexes are current B) After CHECK INDEX reports inconsistencies between index entries and tablespace data C) Before RUNSTATS to ensure statistics are collected on correct index structures D) Whenever a query performs a tablespace scan instead of an index scan

Answer: B REBUILD INDEX is a repair utility that drops and recreates indexes from the tablespace data. It's needed when CHECK INDEX detects inconsistencies — index entries that don't match the actual data. REORG already rebuilds indexes internally, so REBUILD INDEX after REORG is redundant.

Question 15

What SQLCODE does a COBOL program receive when it attempts to access a tablespace in COPY-pending (COPYP) status?

A) SQLCODE -805 (package not found) B) SQLCODE -911 (timeout or deadlock) C) SQLCODE -904 (resource unavailable) D) SQLCODE -501 (cursor not open)

Answer: C SQLCODE -904 with the appropriate reason code is returned when a program tries to access a DB2 object that is in a restrictive pending status (COPY-pending, CHECK-pending, REORG-pending, etc.). The reason code identifies the specific pending status.

Question 16

What is the primary advantage of using RUNSTATS profiles (USEPROFILE)?

A) Profiles execute RUNSTATS faster than manual specifications B) Profiles ensure consistent statistics collection across all invocations, as the specification is defined once and stored in the catalog C) Profiles automatically trigger REBIND after statistics collection D) Profiles allow RUNSTATS to run with SHRLEVEL NONE for better accuracy

Answer: B RUNSTATS profiles store the statistics collection specification (which columns, COLGROUP, FREQVAL, HISTOGRAM options) in the DB2 catalog. Every RUNSTATS invocation with USEPROFILE uses the same specification, eliminating the risk of inconsistent or incomplete statistics collection across different JCL streams.

Question 17

In a partition-level REORG strategy, why would you REORG the current month's partition more frequently than historical partitions?

A) Historical partitions are smaller and don't benefit from REORG B) The current month's partition receives the most INSERT activity, causing the fastest clustering degradation and space fragmentation C) DB2 doesn't allow REORG on partitions older than 90 days D) Historical partitions are automatically reorganized by DB2's background maintenance

Answer: B Partitions receiving heavy INSERT activity experience the fastest clustering degradation (as new rows are inserted out of clustering sequence) and the most space fragmentation (from deleted rows). Historical partitions with minimal activity maintain their organization much longer and need less frequent REORG.

Question 18

What is the SYSPUNCH dataset in an UNLOAD job used for?

A) Storing error messages from the UNLOAD utility B) Generating LOAD control statements that match the unloaded data format, enabling direct reload C) Recording the SQL query used to select the unloaded data D) Storing the UNLOAD utility's execution statistics

Answer: B SYSPUNCH contains LOAD control statements automatically generated by UNLOAD. These statements describe the exact format of the unloaded data (column positions, data types, lengths), allowing you to feed them directly into a LOAD job to reload the data — either into the same table or a restructured one.

Question 19

Which of the following is an anti-pattern in DB2 utility scheduling?

A) Running RUNSTATS after every major batch load operation B) Using threshold-based monitoring to determine when REORG is needed C) Running RUNSTATS daily but never rebinding the affected packages D) Taking inline image copies during REORG

Answer: C Running RUNSTATS without rebinding packages is wasteful — the new statistics are collected and stored in the catalog, but no package uses them because packages retain their access paths from bind time. RUNSTATS without REBIND is "noise" that consumes resources without improving performance.

Question 20

What is the correct sequence of utilities to run after a point-in-time recovery of a tablespace involved in referential integrity relationships?

A) RUNSTATS → REBIND → COPY B) CHECK DATA → CHECK INDEX → COPY → RUNSTATS → REBIND C) REORG → COPY → RUNSTATS → REBIND D) REBUILD INDEX → LOAD → COPY → RUNSTATS

Answer: B After point-in-time recovery: (1) CHECK DATA verifies RI integrity, since dependent tablespaces may have orphan rows; (2) CHECK INDEX verifies index consistency; (3) COPY creates a new image copy, since the recovered tablespace is in COPY-pending; (4) RUNSTATS collects current statistics; (5) REBIND ensures packages use the current statistics and access paths.