Chapter 28 Exercises: Data Sharing and Parallel Sysplex

Exercise 28.1: Understanding Data Sharing Architecture

Objective: Reinforce your understanding of data sharing fundamentals by diagramming the architecture.

Tasks:

1. Draw a diagram of a two-member data sharing group that includes: - Two CPCs, each with one LPAR running a DB2 member - Two coupling facilities (for redundancy) - Shared DASD connected to both CPCs - The following CF structures: lock structure, SCA, GBP0, GBP1 - CF links between each CPC and each CF

2. For each of the following, identify whether the resource is per-member or shared across the group: - Active log datasets - Archive log datasets - BSDS (bootstrap dataset) - DB2 catalog (DSNDB06) - User tablespaces - Buffer pools (in-memory) - EDM pool - Lock structure - Group buffer pools

3. Explain why the sysplex timer is essential for data sharing. What would happen if two members had clocks that were several seconds apart?

Expected Deliverable: A labeled architecture diagram and a table classifying each resource.

Exercise 28.2: Coupling Facility Sizing

Objective: Practice sizing coupling facility structures for a data sharing group.

Scenario: You are designing a 3-member data sharing group for an insurance company with the following characteristics:

• Peak transaction rate: 8,000 TPS across all members
• Average locks per transaction: 6
• Average lock hold time: 50 ms
• Number of frequently updated tablespaces: 25
• Average pages updated per transaction: 4
• Buffer pool configuration: BP0 (4K, primary pool), BP1 (4K, index pool), BP8K0 (8K, LOB pool)

Tasks:

1. Calculate the approximate number of concurrent locks at peak: Concurrent locks = TPS * locks_per_txn * avg_hold_time

2. Using IBM's recommendation that the lock table should have at least 2x the maximum concurrent locks as entries, calculate the minimum lock table size.

3. For GBP0, estimate the required size given: - 4K page size - 5:1 directory-to-data ratio - Target: cache 500,000 pages in the GBP

4. Document your sizing recommendations in a CFRM policy format: STRUCTURE NAME(...) SIZE(...) INITSIZE(...) PREFLIST(CF01, CF02) REBUILDPERCENT(5) DUPLEX(ENABLED)

Exercise 28.3: GBP State Transitions

Objective: Trace the GBP-dependent/GBP-independent state transitions for a tablespace.

Scenario: Tablespace ACCOUNTS_TS is defined in a 2-member data sharing group (M1 and M2). Trace the GBP state through the following sequence:

1. Time T1: M1 opens ACCOUNTS_TS for read/write access. No other member has it open.
2. Time T2: M1 updates 100 pages in ACCOUNTS_TS.
3. Time T3: M2 opens ACCOUNTS_TS for read/write access.
4. Time T4: M2 reads 50 pages from ACCOUNTS_TS, including some updated by M1.
5. Time T5: M1 closes ACCOUNTS_TS.
6. Time T6: M2 is the only accessor. M2 updates 200 pages.

Questions for each time step:

• Is ACCOUNTS_TS GBP-dependent or GBP-independent?
• What P-lock negotiations occur?
• Are updated pages written to the GBP, or only to the local buffer pool?
• What cross-invalidation (XI) signals are sent?

Exercise 28.4: Failure Recovery Analysis

Objective: Analyze recovery scenarios and estimate recovery times.

Scenario: A 3-member data sharing group (M1, M2, M3) is running at Meridian Bank. At 2:15 PM on a Tuesday:

• M1 is processing 5,000 TPS (online banking via CICS)
• M2 is processing 3,000 TPS (distributed DRDA connections)
• M3 is running a batch settlement job with a long-running transaction (updating 2 million rows, 45 minutes in)

At 2:15 PM, the LPAR running M2 crashes due to a hardware fault.

Tasks:

1. Describe the immediate impact on: - In-flight transactions on M2 - CICS sessions connected to M2 (trick question: are there any?) - DRDA connections to M2 - M1 and M3 operations

2. Describe the peer recovery process: - Which member initiates peer recovery? - What logs must be read? - How long might recovery take (estimate based on the transaction rate)?

3. What happens to M2's retained locks during recovery? How do they affect M1 and M3?

4. After recovery completes, what must the operations team do to bring M2 back into the group?

5. If M3's batch job was accessing the same tables as M2's DRDA workload, how might retained locks from M2 affect M3's batch job?

Exercise 28.5: Rolling Maintenance Planning

Objective: Plan a rolling maintenance procedure for applying DB2 PTFs.

Scenario: Meridian Bank needs to apply a critical DB2 PTF (APAR fix) to all three members of the MBNKGRP data sharing group. The maintenance window is Saturday 10 PM to Sunday 6 AM, but the bank requires 24/7 availability for online banking.

Tasks:

1. Write a step-by-step maintenance plan that: - Applies the PTF to each member in sequence - Ensures at least two members are running at all times - Includes verification steps after each member restart - Accounts for workload redistribution during member outages

2. For each step, estimate the duration and identify the risk.

3. Create a rollback plan in case the PTF causes problems on the first member.

4. What monitoring commands would you run after each member restart to verify data sharing health?

Exercise 28.6: Workload Affinity Design

Objective: Design a workload distribution strategy that minimizes data sharing overhead.

Scenario: Meridian Bank has the following workloads accessing the MBNKGRP data sharing group:

Tasks:

1. Identify which tables are "hot" (accessed by multiple workloads).

2. Design a workload-to-member mapping that minimizes cross-member access to hot tables. Use a 3-member group.

3. For the ACCOUNTS table, estimate the GBP activity if all three members are updating it simultaneously. What if only two members update it while the third only reads?

4. Propose a tablespace design (separate tablespaces for different tables) that supports your workload distribution strategy.

5. How would you handle the nightly batch workload to minimize interference with online processing?

Exercise 28.7: Performance Troubleshooting

Objective: Diagnose data sharing performance problems from monitoring data.

Scenario: The following metrics are observed during peak processing on the MBNKGRP data sharing group:

CF Service Time (Lock Structure):     45 microseconds
CF Service Time (GBP0):               28 microseconds
GBP0 Hit Ratio:                       42%
GBP0 XI Ratio:                        18% of getpages
False Contention Rate:                 8% of lock requests
Castout Class Threshold Hits:          150/hour for ACCOUNTS_TS
Lock Structure Utilization:            85%

Meridian's baselines are: - CF service time: <15 microseconds - GBP0 hit ratio: >85% - XI ratio: <5% - False contention: <1% - Castout threshold hits: <10/hour - Lock structure utilization: <70%

Tasks:

1. Identify the metrics that are outside acceptable ranges.
2. For each problematic metric, explain the likely root cause.
3. Propose corrective actions ranked by impact and urgency.
4. Write the monitoring SQL or commands you would use to drill deeper into each problem.