Chapter 21 Quiz: Catalog and Directory
Test your understanding of Chapter 21. Attempt all questions from memory before revealing the answers. The act of retrieval — even when you get it wrong — strengthens learning more than re-reading.
Scoring Guide: - 21-25 correct: Excellent — you have strong command of the material - 16-20 correct: Good — review the sections where you missed questions - 11-15 correct: Fair — re-read the chapter with the quiz questions in mind - Below 11: Re-read the chapter carefully, then retake this quiz in 48 hours
Multiple Choice Questions
Question 1
What is the DB2 catalog?
A) A backup of all database objects stored on tape B) A set of system-managed tables/views containing metadata about every database object C) A graphical tool for browsing database schemas D) A configuration file that stores DB2 startup parameters
Answer
**B) A set of system-managed tables/views containing metadata about every database object** The DB2 catalog stores metadata — information about tables, columns, indexes, privileges, relationships, and every other database object. DB2 maintains it automatically through DDL operations.Question 2
On DB2 for z/OS, what database contains the catalog tables?
A) DSNDB01 B) DSNDB04 C) DSNDB06 D) DSNDB07
Answer
**C) DSNDB06** The catalog is stored in DSNDB06. The directory is stored in DSNDB01. This distinction is critical — losing DSNDB01 (directory) prevents DB2 from starting, while losing DSNDB06 (catalog) prevents DDL operations and new binds.Question 3
Which SYSIBM table contains one row per table, view, alias, or MQT?
A) SYSIBM.SYSOBJECTS B) SYSIBM.SYSTABLES C) SYSIBM.SYSSCHEMAS D) SYSIBM.SYSENTITIES
Answer
**B) SYSIBM.SYSTABLES** SYSTABLES is the foundational catalog table for object discovery. The TYPE column distinguishes between tables ('T'), views ('V'), aliases ('A'), MQTs ('M'), and global temporary tables ('G').Question 4
On DB2 for LUW, what is the correct way to query the catalog?
A) Query SYSIBM tables directly B) Query SYSCAT views C) Query the INFORMATION_SCHEMA views D) Use the GET CATALOG command
Answer
**B) Query SYSCAT views** On LUW, IBM provides SYSCAT views as the stable, documented interface to catalog metadata. While SYSIBM tables exist underneath, their structure may change between versions. SYSCAT views provide a version-stable API.Question 5
What does the CARD column in SYSTABLES / SYSCAT.TABLES represent?
A) The actual current number of rows in the table B) The estimated number of rows based on the most recent RUNSTATS C) The maximum number of rows the table can hold D) The number of rows inserted since the table was created
Answer
**B) The estimated number of rows based on the most recent RUNSTATS** CARD is a statistics value set by RUNSTATS, not a real-time count. It can be stale if the table has been heavily modified since the last RUNSTATS. The optimizer uses CARD for access path selection, which is why stale statistics can cause poor query plans.Question 6
What is stored in the DB2 directory's SPT01 component?
A) System performance trace records B) Skeleton package tables — the executable form of bound SQL packages C) System parameter tables — DB2 configuration settings D) Stored procedure text — the source code of SQL procedures
Answer
**B) Skeleton package tables — the executable form of bound SQL packages** When you BIND a package, DB2 compiles SQL statements and stores the chosen access paths in SPT01. At runtime, DB2 reads the skeleton from SPT01, fills in variable values, and executes the plan.Question 7
Which directory component does DB2 consult during a RECOVER TABLESPACE operation?
A) DBD (Database Descriptor) B) SPT01 (Skeleton Package Table) C) SCT02 (Skeleton Cursor Table) D) SYSLGRNX (Log Range Table)
Answer
**D) SYSLGRNX (Log Range Table)** SYSLGRNX maps each object to the range of log records containing changes to that object. During recovery, DB2 uses SYSLGRNX to identify which log records to read, rather than scanning the entire log.Question 8
On LUW, which catalog view would you use to manually update statistics for optimizer testing?
A) SYSCAT.TABLES B) SYSSTAT.TABLES C) SYSIBM.SYSTABLES D) SYSADM.TABLES
Answer
**B) SYSSTAT.TABLES** SYSCAT views are read-only. The SYSSTAT schema provides updatable views limited to statistics columns, allowing you to manually set statistics values for optimizer testing or adjustment.Question 9
What does SYSIBM.SYSKEYS / SYSCAT.INDEXCOLUSE store?
A) Primary key columns for each table B) Foreign key columns for each relationship C) Individual columns that make up each index key, with their order D) Encryption key information for each encrypted column
Answer
**C) Individual columns that make up each index key, with their order** SYSKEYS (z/OS) and INDEXCOLUSE (LUW) contain one row per column in each index key. The COLSEQ column indicates the position within the key, and the ORDERING/COLORDER column indicates ascending or descending.Question 10
When you issue a CREATE TABLE statement, who inserts the corresponding rows into the catalog?
A) The DBA must insert catalog rows manually after the CREATE B) DB2 inserts catalog rows automatically as part of the CREATE TABLE transaction C) A separate catalog maintenance utility runs periodically D) The system administrator inserts rows using a privileged API
Answer
**B) DB2 inserts catalog rows automatically as part of the CREATE TABLE transaction** The catalog is system-managed. DB2 creates, updates, and deletes catalog entries as part of the transactions that execute DDL statements. If the CREATE TABLE rolls back, the catalog entries are also rolled back.Question 11
What does SYSIBM.SYSRELS / SYSCAT.REFERENCES store?
A) Related table groupings for data sharing B) Foreign key relationships between tables C) Referral paths for distributed query routing D) Reference data (lookup table) designations
Answer
**B) Foreign key relationships between tables** SYSRELS (z/OS) and REFERENCES (LUW) contain one row per foreign key constraint, identifying the child table, parent table, and delete/update rules.Question 12
What is the CLUSTERRATIOF column in SYSINDEXES?
A) The percentage of index pages that are clustered on disk B) The ratio of physical row order to logical index order (0.0 to 1.0) C) The ratio of clustering index size to total index size D) The number of times the index has been reorganized
Answer
**B) The ratio of physical row order to logical index order (0.0 to 1.0)** The cluster ratio measures how well the physical data order matches the index order. A value close to 1.0 means data is well-clustered. A low value (below 0.80) suggests REORG may be needed to restore physical order.Question 13
Why is DB2 for LUW's catalog-only approach (no separate directory) considered simpler than z/OS?
A) LUW stores less metadata overall B) LUW does not need runtime structures like access paths C) Runtime information like access paths and recovery ranges is managed through different mechanisms without a separate critical database D) LUW uses flat files instead of database tables
Answer
**C) Runtime information like access paths and recovery ranges is managed through different mechanisms without a separate critical database** On LUW, access paths are stored in the package cache and catalog package tables; recovery ranges are managed through the recovery history file. There is no separate DSNDB01-equivalent database that must be protected separately.Question 14
What does a NULL STATSTIME / STATS_TIME value in SYSTABLES indicate?
A) The table was created but never had data inserted B) The table has been dropped and recreated C) RUNSTATS has never been executed on this table D) The statistics have been manually cleared
Answer
**C) RUNSTATS has never been executed on this table** A NULL statistics timestamp means no statistics have ever been collected. The optimizer will use default assumptions, which may lead to suboptimal access paths. This is a common problem with newly created tables.Question 15
In SYSIBM.SYSTABAUTH, what does a value of 'G' in the SELECTAUTH column mean?
A) The privilege was granted by a global administrator B) The privilege is granted for SELECT on GROUP BY queries only C) The privilege was granted WITH GRANT OPTION D) The privilege is a system-generated default
Answer
**C) The privilege was granted WITH GRANT OPTION** 'Y' means the privilege was granted normally. 'G' means it was granted WITH GRANT OPTION, meaning the grantee can further grant the same privilege to others. This creates a privilege chain that requires careful management.Question 16
What is the primary purpose of Real-Time Statistics (RTS) on z/OS?
A) To replace RUNSTATS entirely B) To provide continuous space and change tracking for intelligent utility scheduling C) To give the optimizer real-time row counts during query execution D) To monitor CPU usage by table
Answer
**B) To provide continuous space and change tracking for intelligent utility scheduling** RTS continuously tracks inserts, deletes, updates, space usage, and change counts since the last REORG, COPY, and RUNSTATS. This enables DBAs to schedule utilities based on actual need rather than fixed calendars. RTS does not replace RUNSTATS — the optimizer still needs full column distribution statistics.Question 17
Which RTS table stores the count of inserts since the last REORG for a tablespace?
A) SYSIBM.SYSTABLESPACESTATS B) SYSIBM.SYSINDEXSPACESTATS C) SYSIBM.SYSTABLEPART D) SYSIBM.SYSTABLESPACE
Answer
**A) SYSIBM.SYSTABLESPACESTATS** The REORGINSERTS column in SYSTABLESPACESTATS tracks the number of rows inserted since the last REORG. Combined with REORGDELETES and REORGUPDATES, this gives the total change volume used for REORG scheduling decisions.Question 18
You need to find all tables that contain a column named ACCOUNT_ID for impact analysis. Which catalog table/view do you query?
A) SYSIBM.SYSTABLES / SYSCAT.TABLES B) SYSIBM.SYSCOLUMNS / SYSCAT.COLUMNS C) SYSIBM.SYSINDEXES / SYSCAT.INDEXES D) SYSIBM.SYSRELS / SYSCAT.REFERENCES
Answer
**B) SYSIBM.SYSCOLUMNS / SYSCAT.COLUMNS** SYSCOLUMNS/SYSCAT.COLUMNS contains one row per column in every table. Filtering on the column name (NAME = 'ACCOUNT_ID' or COLNAME = 'ACCOUNT_ID') returns all tables that use this column.Question 19
What is the REPAIR DBD command used for on z/OS?
A) Repairing corrupted data in user tables B) Rebuilding a corrupted Database Descriptor from catalog information C) Repairing broken foreign key relationships D) Fixing invalid index structures
Answer
**B) Rebuilding a corrupted Database Descriptor from catalog information** If a DBD becomes corrupted, DB2 can reconstruct it from the catalog. This demonstrates that the catalog is the master definition — the directory's DBD is a derived runtime structure.Question 20
Which of the following is NOT stored in the z/OS DB2 directory?
A) Skeleton Package Table (SPT01) B) Log Range Table (SYSLGRNX) C) Table privilege grants (SYSTABAUTH) D) Database Descriptor (DBD)
Answer
**C) Table privilege grants (SYSTABAUTH)** SYSTABAUTH is a catalog table (DSNDB06), not a directory component (DSNDB01). The directory contains runtime structures: DBD, SPT01, SCT02, SYSLGRNX, and SYSUTILX.Short Answer Questions
Question 21
Explain why a comprehensive impact analysis before dropping a column should check at least five different catalog tables. Name the tables and what dependency each one captures.
Answer
A comprehensive impact analysis should check: 1. **SYSCOLUMNS / SYSCAT.COLUMNS** — Find all tables using the same column name (potential join partners) 2. **SYSRELS / SYSCAT.REFERENCES** — Foreign keys that include the column 3. **SYSVIEWDEP / SYSCAT.VIEWDEP** — Views that reference the table containing the column 4. **SYSPACKDEP / SYSCAT.PACKAGEDEP** — Compiled packages (programs) that reference the table 5. **SYSTRIGGERS / SYSCAT.TRIGGERS** — Triggers defined on the table that might reference the column 6. **SYSINDEXES + SYSKEYS / SYSCAT.INDEXES + SYSCAT.INDEXCOLUSE** — Indexes that include the column Missing any of these dependency types can lead to cascading failures after the change is applied.Question 22
A junior DBA runs SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE CREATOR = 'MERIDIAN' and gets 47. They then run SELECT COUNT(*) FROM SYSIBM.SYSTABLES WHERE CREATOR = 'MERIDIAN' AND TYPE = 'T' and get 32. Explain the discrepancy.
Answer
The 15-row difference represents non-table objects in the MERIDIAN schema: views (TYPE = 'V'), aliases (TYPE = 'A'), materialized query tables (TYPE = 'M'), and/or global temporary tables (TYPE = 'G'). SYSTABLES stores ALL of these object types, not just base tables. Filtering on TYPE = 'T' returns only base tables. The junior DBA should always specify TYPE = 'T' when counting "real" tables.Question 23
Explain how Real-Time Statistics can be used to implement a "smart" REORG schedule that replaces a fixed calendar approach. What metrics would you use, and what thresholds would you set?
Answer
Real-Time Statistics provide three key metrics for REORG scheduling: 1. **Change volume**: REORGINSERTS + REORGDELETES + REORGUPDATES from SYSTABLESPACESTATS. When this exceeds 20% of TOTALROWS, the tablespace is a candidate for REORG. 2. **Clustering degradation**: CLUSTERRATIOF from SYSINDEXES (updated by RUNSTATS, not RTS directly). When the cluster ratio drops below 0.80 for the clustering index, data is significantly out of physical order. 3. **Space fragmentation**: Compare NACTIVE (total pages) to NPAGES (pages with data) from SYSTABLESPACESTATS. A large gap indicates empty pages that REORG can reclaim. A smart schedule queries these metrics daily and generates REORG jobs only for objects that exceed thresholds, rather than reorganizing every tablespace every Sunday regardless of need. This reduces maintenance windows, I/O load, and log volume.Question 24
A security auditor asks you to produce a report showing every user who has DELETE authority on any table containing customer personally identifiable information (PII). How would you approach this using only catalog queries?
Answer
Step 1: Identify PII tables by querying SYSCOLUMNS for columns with names suggesting PII content (e.g., SSN, SOCIAL_SECURITY, TAX_ID, DATE_OF_BIRTH, EMAIL, PHONE). Collect the distinct table names. Step 2: Join the PII table list with SYSTABAUTH, filtering for GRANTEE values where DELETEAUTH IN ('Y', 'G'). Step 3: Present the results showing GRANTEE, table name, and whether GRANT OPTION was given. This approach relies on column naming conventions. A more robust approach would use REMARKS or a separate data classification catalog to tag PII tables, rather than inferring from column names.Question 25
Describe a scenario where the optimizer makes a poor access path choice because catalog statistics are stale, and explain how a catalog query could have detected the problem before it caused a production incident.
Answer
**Scenario**: The MERIDIAN.TRANSACTION table had 5 million rows when RUNSTATS was last executed (CARD = 5,000,000, STATSTIME shows 6 months ago). Since then, a year-end batch process loaded 45 million historical records. The catalog still shows CARD = 5,000,000. A query with a range predicate on TRANSACTION_DATE uses a nested loop join (appropriate for 5M rows) instead of a merge join (appropriate for 50M rows), causing a 45-minute batch job to run for 12 hours. **Detection query**: A simple check for tables where STATSTIME is older than a threshold, combined with RTS data showing STATSINSERTS significantly larger than the cataloged CARD, would have flagged this table as needing urgent RUNSTATS before the batch job ran:SELECT T.NAME, T.CARD, T.STATSTIME,
SS.TOTALROWS, SS.STATSINSERTS
FROM SYSIBM.SYSTABLES T
JOIN SYSIBM.SYSTABLESPACESTATS SS
ON SS.DBNAME = T.DBNAME AND SS.NAME = T.TSNAME
WHERE SS.STATSINSERTS > T.CARD * 0.5;
Return to Chapter 21 | Continue to Case Study 1