Chapter 9 Exercises: DB2 Utilities for the COBOL Developer

Foundational Exercises (Apply)

Exercise 1: REORG JCL — Basic Offline

Write the complete JCL for an offline REORG of tablespace DBPROD.TSORDER01 with: - Sort data and keys - Inline RUNSTATS for all tables and indexes - Inline image copy to two datasets (DASD and tape) - Utility ID: RGORDR01

Include all DD statements with appropriate space allocations for a 20 GB tablespace.

Exercise 2: Online REORG Setup

For the tablespace DBPROD.TSORDER01, write: a) The DDL to create the mapping table for SHRLEVEL CHANGE REORG b) The REORG utility statement with SHRLEVEL CHANGE, DRAIN_WAIT 90, RETRY 5, TIMEOUT TERM c) Explain what happens if DRAIN_WAIT expires and all retries are exhausted with TIMEOUT TERM

Exercise 3: RUNSTATS with Column Groups

The ORDER_DETAIL table has these frequently queried column combinations: - ORDER_ID + LINE_NUM (primary key lookup) - PRODUCT_ID + WAREHOUSE_ID (inventory correlation) - ORDER_DATE + STATUS (date range with status filter) - CUSTOMER_REGION + PRODUCT_CATEGORY (geographic analysis)

Write the RUNSTATS utility statement that collects: - All table and index statistics - COLGROUP statistics for each column combination above - FREQVAL COUNT 15 for STATUS and PRODUCT_CATEGORY - HISTOGRAM NUMQUANTILES 40 for ORDER_DATE

Exercise 4: RUNSTATS Profile

Convert the RUNSTATS specification from Exercise 3 into a stored profile using ALTER TABLESPACE. Then write the RUNSTATS invocation that uses USEPROFILE.

Exercise 5: Image Copy — Full and Incremental

Write two JCL jobs: a) A full image copy of tablespace DBPROD.TSORDER01 with dual copies (DASD and virtual tape), SHRLEVEL REFERENCE, PARALLEL 4 b) An incremental image copy of the same tablespace with a single copy to DASD

Exercise 6: LOAD from Sequential File

Write the LOAD utility statement and JCL to load data into the DAILY_TRANSACTIONS table with these columns: - TRAN_ID CHAR(15) - TRAN_DATE DATE - ACCT_FROM CHAR(12) - ACCT_TO CHAR(12) - AMOUNT DECIMAL(15,2) - TRAN_CODE CHAR(4) - BRANCH_ID CHAR(6)

Requirements: RESUME YES, LOG NO, inline COPY, inline RUNSTATS, SORTKEYS 250000. Input file is a fixed-length sequential dataset.

Exercise 7: RECOVER Scenarios

For each scenario, write the correct RECOVER utility statement:

a) Tablespace DBPROD.TSORDER01 has a media failure. Recover to current using the most recent copies. b) A bad batch job corrupted data at RBA X'0000B2C4D6E80000'. Recover to the point just before that RBA. c) You need to restore to exactly the state captured in image copy dataset PROD.ORDER01.FCOPY.D260310. d) In a data sharing environment, recover to LRSN X'B2C4D6E8F0A2'.

Exercise 8: CHECK DATA with Exception Table

Write: a) The DDL to create an exception table for the ORDER_DETAIL table (which has columns ORDER_ID, LINE_NUM, PRODUCT_ID, QTY, PRICE, STATUS) b) The CHECK DATA utility statement that checks ORDER_DETAIL, copies violations to the exception table, but does NOT delete them c) The SQL query you'd run against the exception table to analyze the violations

Exercise 9: REBUILD INDEX

Write the utility statements to: a) Rebuild all indexes on tablespace DBPROD.TSORDER01 with inline RUNSTATS b) Rebuild only the specific index PRODADM.IX_ORDER_DATE with SHRLEVEL NONE

Exercise 10: DISPLAY UTILITY

Write the DB2 commands to: a) Display all active utilities b) Display a specific utility with UTILID RGORDR01 c) Terminate a stuck utility with UTILID LDTRAN01

Intermediate Exercises (Analyze)

Exercise 11: REORG Decision Analysis

Given these catalog statistics for four tablespaces, determine which ones need REORG and why:

For each, state: REORG needed (yes/no), justification, recommended SHRLEVEL, and any special considerations.

Exercise 12: RUNSTATS Impact Analysis

A query's EXPLAIN output shows:

ACCESSTYPE = R (tablespace scan)
MATCHCOLS  = 0
TABLE_CARD = 5,000,000

The query is: SELECT * FROM ORDERS WHERE STATUS = 'OPEN' AND REGION = 'NORTHEAST'

An index exists on (STATUS, REGION).

a) What catalog statistics would you check to understand why the optimizer chose a tablespace scan? b) Write the RUNSTATS statement that would collect the statistics needed for the optimizer to evaluate the index. c) After RUNSTATS, what additional step is required before the query uses the new statistics? d) If STATUS has only 4 distinct values and REGION has 8, but the combination (STATUS='OPEN', REGION='NORTHEAST') is very rare, which RUNSTATS feature captures this?

Exercise 13: Recovery Time Estimation

A tablespace has these characteristics: - Size: 50 GB across 12 partitions - Full image copy: taken Sunday at 02:00 (FlashCopy) - Incremental copies: taken daily at 01:00 - Daily log volume for this tablespace: approximately 2 GB - It's now Thursday at 14:00

Estimate the recovery time for: a) RECOVER to current after a media failure b) RECOVER TORBA to Monday at 22:00 c) RECOVER TOCOPY to Sunday's full copy

Assume: FlashCopy restore = 15 seconds, incremental apply = 1 minute per GB, log apply = 2 minutes per GB.

Exercise 14: LOAD vs. INSERT Analysis

You need to insert 2 million rows into a partitioned table (8 partitions). Compare these approaches:

a) COBOL program with INSERT statements, COMMIT every 5,000 rows b) LOAD RESUME YES, LOG NO, with inline COPY and RUNSTATS c) LOAD RESUME YES, LOG YES

For each approach, analyze: approximate elapsed time (relative), log volume, recovery implications, impact on concurrent applications, and post-operation utility requirements.

Exercise 15: Partition-Level Utility Strategy

A transaction table has 24 partitions organized by month (January through December of the current and prior year). Analyze the activity pattern and design a partition-level utility strategy:

• Current month: 100,000 inserts/day, 50,000 updates/day
• Prior 3 months: 5,000 updates/day (corrections only)
• 4-12 months ago: 100 updates/day (rare corrections)
• Over 12 months: Read-only (regulatory queries)

For each partition age group, specify: REORG frequency, RUNSTATS frequency, COPY frequency, and justification.

Exercise 16: COPY Pending Resolution

Your batch job gets SQLCODE -904 with reason code 00C900B0 (COPY pending) on tablespace DBPROD.TSTRAN01.

a) What caused the COPY-pending status? b) Write the utility statement to resolve it. c) How could this situation have been prevented? d) Can you override COPY-pending to let your batch run? Should you?

Exercise 17: Point-in-Time Recovery Dependencies

The HA Banking system has these RI relationships:

CUSTOMER_MASTER (parent)
  └── ACCT_MASTER (dependent, FK: CUST_ID)
        └── ACCT_TRANSACTION (dependent, FK: ACCT_ID)
SYSTEM_PARAMS (independent)
AUDIT_TRAIL (independent)
ACCT_XREF (dependent on ACCT_MASTER, FK: ACCT_ID)

A bad batch run at RBA X'0000C5D6E7F80000' corrupted ACCT_MASTER.

a) Which tablespaces must you recover to the same point in time? b) In what order must you run the RECOVER utilities? c) What utilities must you run after recovery? d) What's the risk if you recover only ACCT_MASTER without recovering the dependent tablespaces?

Exercise 18: Utility Conflict Analysis

These utility jobs are scheduled to run at 01:00:

a) Which jobs can run concurrently? b) Which jobs conflict and must be serialized? c) Design the optimal execution sequence to minimize total elapsed time. d) Which jobs become unnecessary if you use inline options?

Exercise 19: Threshold Monitoring SQL

Write a comprehensive monitoring query that identifies tablespaces needing utility attention. The query should check for: - CLUSTERRATIOF below 0.80 - PERCDROP above 0.10 - STATSTIME more than 7 days old - COPY-pending status - REORG-pending status

Output should include: database name, tablespace name, partition number, the specific metrics, and a recommendation column (REORG, RUNSTATS, COPY, or multiple).

Exercise 20: UNLOAD/LOAD Table Restructure

You need to add two columns to the ORDER_DETAIL table and change the partitioning scheme from 12 to 24 partitions. Design the complete utility sequence:

a) UNLOAD the existing data b) ALTER the table structure c) REORG to materialize the ALTER d) Or: UNLOAD, DROP/CREATE with new structure, LOAD

Compare both approaches. Which is better for a 100 GB table? Why?

Advanced Exercises (Analyze/Evaluate)

Exercise 21: CNB's High-Volume Table Strategy

CNB's ACCT_TRANSACTION table receives 2.5 million inserts per day across 24 partitions. The current REORG strategy runs weekly on all partitions (SHRLEVEL CHANGE). Kwame suspects this is overkill for some partitions and insufficient for others.

Design an optimized strategy that: a) Uses catalog metrics to determine which partitions actually need REORG b) Runs the minimum number of REORGs to maintain performance c) Accounts for the varying activity levels across partitions (most inserts hit the current month) d) Integrates RUNSTATS and COPY into the REORG schedule e) Includes the monitoring SQL to drive the schedule

Exercise 22: Federal Benefits Recovery Drill

Sandra Chen wants to validate her team's recovery procedures. Design a recovery drill that tests:

a) Recovery of ACCT_MASTER (parent tablespace) to current after simulated media failure b) Point-in-time recovery of ACCT_TRANSACTION to a specific RBA c) Recovery of the CUSTOMER_MASTER → ACCT_MASTER → ACCT_TRANSACTION chain d) Verification using CHECK DATA and CHECK INDEX after each recovery

Write the complete sequence of utility statements for each drill scenario, including pre-drill preparation (taking image copies) and post-drill verification.

Exercise 23: LOAD Performance Optimization

Pinnacle Health needs to load 50 million patient encounter records (150 GB) into a 48-partition tablespace. The current LOAD job takes 4 hours. Ahmad Patel wants to get it under 1 hour.

Design an optimized LOAD strategy that: a) Leverages parallel partition-level LOAD b) Uses appropriate SORTKEYS values (justify your choice) c) Minimizes log volume d) Includes inline COPY and RUNSTATS e) Accounts for restart capability if a partition LOAD fails f) Includes the JCL framework for the parallel LOAD jobs

Exercise 24: Utility Impact on Batch Window

The HA Banking system has a batch window from 22:00 to 06:00 (8 hours). The following must complete within that window:

• Nightly batch processing: 4 hours (22:00-02:00)
• DB2 utilities for 6 tablespaces
• Batch job restart capability

Design a utility schedule that fits within the remaining 4 hours (02:00-06:00). Include: a) Job sequencing with dependencies b) Parallelism opportunities c) Estimated elapsed times for each utility d) Contingency plan if any utility overruns e) The scheduling tool directives (e.g., CA-7 or TWS style)

Exercise 25: Comprehensive Utility Audit

You've inherited a DB2 system with no utility schedule. The previous DBA "ran things when he remembered." Perform a utility audit:

a) Write the SQL queries to assess the current state (last REORG time, last RUNSTATS time, last COPY time, pending statuses) b) Identify the most critical gaps (tablespaces that haven't been copied in 30 days, never had RUNSTATS, etc.) c) Design a remediation plan to bring the system to a healthy state d) Design the ongoing utility schedule to maintain that state e) Estimate the resource requirements (CPU, DASD, tape, batch window time) for the steady-state schedule

Challenge Exercises

Exercise 26: Online REORG Failure Analysis

An online REORG with SHRLEVEL CHANGE has been running for 3 hours and is stuck in the SWITCH phase. The DRAIN_WAIT has expired and all retries are exhausted.

a) What are the possible causes? b) How do you identify the blocking application? c) What are your options for resolving the situation without losing the REORG work? d) How do you prevent this from happening again? e) Write the DB2 commands you'd issue to diagnose and resolve this.

Exercise 27: Cross-System Utility Coordination

In a data sharing environment with three DB2 members (DB2A, DB2B, DB2C), you need to coordinate utilities across members:

a) Can you run REORG on DB2A for a tablespace that DB2B and DB2C are using? b) How does SHRLEVEL CHANGE work across data sharing members? c) What utility ID naming convention prevents conflicts? d) How do you handle COPY when different members have different active log streams? e) Design the utility coordination strategy for the HA Banking system assuming 3 data sharing members.

Exercise 28: Disaster Recovery with Utilities

Design the DB2 utility strategy for a disaster recovery site that maintains a 4-hour Recovery Time Objective (RTO) and 15-minute Recovery Point Objective (RPO):

a) What copy frequency is needed to meet the RPO? b) How do you get image copies and logs to the DR site? c) Write the recovery procedure for bringing up all 6 HA Banking tablespaces at the DR site d) How do you verify data consistency at the DR site after recovery? e) What ongoing utility maintenance is needed at the DR site?