Chapter 21 Exercises: Catalog and Directory

These exercises reinforce and extend the concepts from Chapter 21. They range from foundational catalog queries to advanced automation scripts and impact analysis workflows. Do not skip the ones that feel easy — fluency with the catalog comes from repetition, and the queries you practice here will become the foundation of your DBA toolkit.

Difficulty Ratings: - Beginner — Recall and comprehension. If you read the chapter, you can answer these. - Intermediate — Application and analysis. Requires combining multiple catalog tables or thinking beyond what was explicitly stated. - Advanced — Synthesis and evaluation. Requires building complete automation scripts, handling edge cases, or integrating concepts across chapters.

Part A: Catalog Fundamentals

Exercise 21.1 — Catalog vs. Directory (Beginner)

In your own words, explain the difference between the DB2 catalog and the DB2 directory on z/OS. Your explanation should cover: 1. What each contains 2. Whether each is queryable with SQL 3. Which is more critical for DB2 startup 4. What happens if each is lost

Then explain why DB2 for LUW does not have a separate directory structure.

Exercise 21.2 — Catalog Table Mapping (Beginner)

Complete the following table from memory, matching each z/OS SYSIBM table to its LUW SYSCAT equivalent:

After completing the table from memory, compare against Sections 21.2 and 21.4.

Exercise 21.3 — SYSTABLES Key Columns (Beginner)

For SYSIBM.SYSTABLES (z/OS), list at least 10 columns and explain what each one stores. Pay particular attention to the columns that hold statistics updated by RUNSTATS versus columns that hold structural information set at CREATE TABLE time.

Exercise 21.4 — Column Ordinal Position Difference (Beginner)

On z/OS, SYSCOLUMNS.COLNO is 1-based (the first column is numbered 1). On LUW, SYSCAT.COLUMNS.COLNO is 0-based (the first column is numbered 0). Write a query for each platform that returns columns for a given table in order, and explain why this difference matters when you are writing cross-platform scripts.

Exercise 21.5 — TYPE Column Values (Beginner)

The TYPE column in SYSTABLES / SYSCAT.TABLES distinguishes between different object types. List all the TYPE values you can recall and what each represents. Why is it important to filter on TYPE = 'T' when counting "real" tables?

Part B: Querying the Catalog

Exercise 21.6 — Find Tables by Creation Date (Intermediate)

Write a catalog query (for the platform of your choice) that lists all tables in the MERIDIAN schema created within the last 90 days. Include the table name, column count, and creation timestamp. Sort the results with the most recently created table first.

Exercise 21.7 — Column Data Type Distribution (Intermediate)

Write a query that produces a summary showing how many columns of each data type exist across all tables in the MERIDIAN schema. For example, the output might show that there are 45 VARCHAR columns, 32 INTEGER columns, 18 DECIMAL columns, and so on. This type of query is useful for understanding the overall data profile of a schema.

Exercise 21.8 — Tables with Most Columns (Intermediate)

Write a query that finds the 10 tables in the MERIDIAN schema with the most columns. Include the table name, column count, and row count (CARD). Discuss what a very high column count might indicate about table design.

Exercise 21.9 — Nullable Column Audit (Intermediate)

Write a query that, for each table in the MERIDIAN schema, shows the total number of columns and the number of nullable columns. Calculate the percentage of nullable columns. Which tables have the highest nullable percentage, and why might this be a concern?

Exercise 21.10 — Unused REMARKS Columns (Intermediate)

The REMARKS column in SYSTABLES and SYSCOLUMNS allows DBAs to add documentation directly to the catalog. Write a query that identifies how many tables and columns in the MERIDIAN schema have NULL or empty REMARKS. Discuss the value of maintaining catalog remarks in a production environment.

Part C: Index Analysis

Exercise 21.11 — Complete Index Inventory (Intermediate)

Write a query that produces a complete index inventory for the MERIDIAN schema, showing: - Index name - Table name - Whether the index is unique, primary key, or allows duplicates - Whether it is the clustering index - All key columns in order - Number of leaf pages - Number of B-tree levels

This requires joining at least two catalog tables. Format the output so it could serve as documentation for a schema review.

Exercise 21.12 — Index-to-Table Ratio (Intermediate)

Write a query that calculates the index-to-table ratio for each table in the MERIDIAN schema — that is, the number of indexes per table. Also calculate the ratio of total index leaf pages to table data pages. Which tables have the highest ratios, and what are the performance implications of having many large indexes on a single table?

Exercise 21.13 — Single-Column vs. Composite Indexes (Intermediate)

Write a query that categorizes all indexes in the MERIDIAN schema as either single-column (COLCOUNT = 1) or composite (COLCOUNT > 1). Count the number of each type. Then, for composite indexes, show the distribution by number of key columns (2, 3, 4, etc.). Discuss when composite indexes are preferable to multiple single-column indexes.

Exercise 21.14 — Clustering Index Coverage (Intermediate)

Write a query that identifies all tables in the MERIDIAN schema that do NOT have a clustering index defined. For tables with more than 10,000 rows, this may indicate a physical design gap. Discuss why a table might legitimately not have a clustering index.

Exercise 21.15 — Redundant Index Detection (Advanced)

Expand the redundant index detection query from Section 21.6 to check not just the first key column but the first two key columns. An index on (A, B, C) is likely redundant if another index on the same table starts with (A, B). Write the query and explain the risks of dropping a seemingly redundant index without further investigation.

Part D: Relationship and Dependency Analysis

Exercise 21.16 — Full Foreign Key Map (Intermediate)

Write a query that produces a complete map of all foreign key relationships in the MERIDIAN schema. The output should show the child table, parent table, foreign key column(s), parent key column(s), and delete rule. Format this as a reference document that a developer could use to understand the data model.

Exercise 21.17 — Orphan Table Detection (Intermediate)

Write a query that identifies "orphan" tables — tables in the MERIDIAN schema that are neither a parent nor a child in any foreign key relationship. These tables are isolated from the relational graph. Discuss when this is acceptable (e.g., reference/lookup tables with no enforced foreign keys) and when it indicates a design problem.

Exercise 21.18 — Cascade Delete Impact Analysis (Advanced)

Write a query that identifies all foreign key relationships in the MERIDIAN schema with a DELETE RULE of CASCADE. For each such relationship, use a recursive CTE to trace the full cascade chain: if deleting from table A cascades to table B, and table B also has cascade deletes to table C, show the complete chain. Discuss why cascade deletes in a financial system like Meridian National Bank require extreme caution.

Exercise 21.19 — View Dependency Tree (Advanced)

Write a query that builds a complete view dependency tree for the MERIDIAN schema. For each view, show: - The view name - All base tables the view depends on - Whether the view depends on other views (nested views)

Then answer: if you need to ALTER the CUSTOMER table, which views will be affected? How would you use this information in a change management process?

Part E: Space and Storage

Exercise 21.20 — Schema Space Summary (Intermediate)

Write a query (for z/OS) that produces a space usage summary for the MERIDIAN schema, showing: - Total number of tables - Total data pages across all tables - Total estimated size in megabytes - Average table size in megabytes - Largest table (by pages) and its size

Exercise 21.21 — Partition Balance Analysis (Advanced)

For a partitioned tablespace in the Meridian schema (e.g., TSTRXN for the TRANSACTION table), write a query against SYSTABLEPART that shows the row count and space usage for each partition. Calculate the standard deviation of row counts across partitions. A high standard deviation indicates partition skew, which can cause performance problems. Discuss strategies for rebalancing.

Part F: Security and Privileges

Exercise 21.22 — Privilege Summary Matrix (Intermediate)

Write a query that produces a privilege summary matrix for the MERIDIAN schema. For each user (GRANTEE), show a count of tables on which they hold SELECT, INSERT, UPDATE, DELETE, ALTER, and INDEX privileges. Identify any users who have ALTER authority on more than 5 tables and discuss whether this is appropriate.

Exercise 21.23 — GRANT OPTION Audit (Intermediate)

Write a query that identifies all privilege grants in the MERIDIAN schema where the privilege was granted WITH GRANT OPTION (indicated by 'G' rather than 'Y'). Explain why WITH GRANT OPTION requires careful management and what risks it introduces.

Exercise 21.24 — Public Privilege Audit (Advanced)

Write a query that identifies all tables in the MERIDIAN schema where PUBLIC has been granted any privilege. For a banking application like Meridian, discuss why PUBLIC grants on application tables are almost always a security violation and how to remediate them.

Part G: Automation and Advanced Techniques

Exercise 21.25 — DDL Generation Script (Advanced)

Write a catalog query that generates a complete CREATE TABLE statement for any given table in the MERIDIAN schema. Your generated DDL should include: - All columns with correct data types, lengths, scales, and nullability - NOT NULL constraints - Default values - Primary key constraint

Test your query against at least two Meridian tables. Discuss the limitations of catalog-based DDL generation compared to tools like db2look (LUW) or DB2 Admin Tool (z/OS).

Exercise 21.26 — CREATE INDEX Generator (Advanced)

Write a catalog query that generates CREATE INDEX statements for all indexes on a given table. The generated DDL should include: - Index name - Column list in correct order with ASC/DESC - UNIQUE clause if applicable - CLUSTER clause if applicable

Exercise 21.27 — Comprehensive Impact Analysis Script (Advanced)

Write a complete impact analysis script that, given a table name, identifies ALL objects that would be affected by dropping that table. Your script should check: 1. Foreign key relationships (child tables) 2. Views that reference the table 3. Stored procedures / packages that reference the table 4. Triggers defined on the table 5. Indexes on the table 6. Check constraints 7. MQTs (materialized query tables) that reference the table

Present the results categorized by dependency type.

Exercise 21.28 — RUNSTATS Priority Generator (Advanced)

Using Real-Time Statistics (z/OS), write a query that generates a prioritized RUNSTATS schedule for the MERIDIAN schema. Tables should be categorized as: - Critical: More than 20% change since last RUNSTATS and CARD > 1,000,000 - High: More than 20% change or CARD > 1,000,000 and STATS older than 14 days - Medium: More than 10% change or STATS older than 30 days - Low: All others

Generate actual RUNSTATS utility control statements for the Critical and High categories.

Exercise 21.29 — Change Detection Report (Advanced)

Write a query that produces a weekly change detection report showing: - Tables created in the last 7 days - Tables altered in the last 7 days - Indexes created in the last 7 days - Indexes dropped in the last 7 days (hint: this is harder — what catalog evidence would indicate a recently dropped index?)

For the "indexes dropped" case, discuss the challenges of detecting deletions from the catalog (since dropped objects are removed from catalog tables).

Exercise 21.30 — Cross-Schema Impact Analysis (Advanced)

In a real enterprise environment, foreign keys and views may cross schema boundaries. Write a query that identifies all cross-schema dependencies involving the MERIDIAN schema — objects in other schemas that depend on MERIDIAN objects, and MERIDIAN objects that depend on objects in other schemas. Discuss the change management implications of cross-schema dependencies.

Part H: Integration and Capstone

Exercise 21.31 — Build Your Personal Catalog Toolkit (Advanced)

Based on the Meridian Bank Catalog Toolkit in Section 21.12, design and implement your own catalog toolkit with at least 8 queries tailored to a specific use case: - Daily health monitoring - Weekly capacity planning - Monthly security audit - Quarterly schema review

For each query, document its purpose, expected output, and the action you would take based on the results.

Exercise 21.32 — Catalog-Driven REORG Scheduler (Advanced)

Write a complete script (using RTS on z/OS or ADMIN_GET_TAB_INFO on LUW) that: 1. Queries the catalog and RTS for tablespace health metrics 2. Identifies tablespaces that need REORG based on multiple criteria (cluster ratio, space usage, change volume) 3. Prioritizes them by urgency 4. Generates the appropriate REORG utility control statements 5. Estimates the time window needed based on tablespace size

Exercise 21.33 — Catalog vs. Application Data Model (Advanced)

Extract the complete Meridian schema definition from the catalog (tables, columns, data types, primary keys, foreign keys). Compare this catalog-derived model to the logical data model from Chapter 13. Identify any discrepancies — tables in the model that are not in the catalog, columns that differ in type or nullability, relationships that are documented but not enforced. Discuss how catalog queries can be used as a governance tool to ensure the physical implementation matches the logical design.

Exercise 21.34 — Simulating Statistics for Optimizer Testing (Advanced)

On z/OS, certain SYSTABLES and SYSCOLUMNS statistics columns can be updated directly to test optimizer behavior. On LUW, the SYSSTAT views serve this purpose. Write a set of UPDATE statements that would simulate the following scenario on the MERIDIAN.TRANSACTION table: - Double the row count (CARD) - Halve the column cardinality for TRANSACTION_TYPE - Set HIGH2KEY and LOW2KEY for TRANSACTION_DATE to span only the last 30 days

Discuss the risks of manually updating catalog statistics in a production environment and the safeguards you should put in place.

Exercise 21.35 — Documentation Generation (Advanced)

Write a comprehensive catalog query (or set of queries) that generates complete schema documentation for the MERIDIAN schema in a format suitable for a data dictionary. For each table, include: - Table name and description (REMARKS) - All columns with types, lengths, nullability, and descriptions - Primary key definition - Foreign key definitions - Index definitions - Granted privileges

Discuss how this approach to documentation differs from maintaining a separate data dictionary tool, and what the advantages and disadvantages of each approach are.

Return to Chapter 21 | Continue to Quiz