Chapter 35: Exercises — RESTful APIs and Modern Access Patterns

Exercise 35.1: Db2 REST Service Registration

Objective: Create and consume built-in Db2 REST services (LUW).

1. Register a REST service that returns all active accounts for a given customer: sql -- Write the ADMIN_CMD call to create this service -- The query should accept a customer_id parameter and return -- account_id, account_type, balance, and status
2. Register a REST service that wraps the PROC_TRANSFER_FUNDS stored procedure.
3. Write curl commands to invoke both services, including proper authentication headers.
4. Discuss the limitations you encounter compared to a full REST API framework.

Exercise 35.2: Spring Boot REST API

Objective: Build a REST API endpoint that calls a DB2 stored procedure.

Create a Spring Boot controller with the following endpoints:

Requirements: - Use HikariCP for connection pooling (configure in application.yml). - Implement input validation using javax.validation annotations. - Return consistent error responses (never expose SQLSTATE or SQLCODE to clients). - Add pagination to the statement endpoint. - Write integration tests using MockMvc.

Exercise 35.3: API Error Handling

Objective: Design a comprehensive error mapping strategy.

Given that PROC_TRANSFER_FUNDS returns these status codes: - 0: Success - -1: Invalid input - -2: Source account error - -3: Destination account error - -4: Insufficient funds - -5: Daily limit exceeded - Positive values: DB2 SQLCODEs

Create: 1. An ErrorMapper class that maps each status code to an HTTP status code and client-facing error code. 2. A global exception handler that catches DB2 exceptions and returns safe error responses. 3. A test class that verifies every error mapping. 4. A JSON error response format that includes a unique error reference ID for support troubleshooting.

Exercise 35.4: GraphQL Schema Design

Objective: Design a GraphQL schema for a banking domain.

Extend the GraphQL schema from Section 35.5 to include:

1. A creditCardAccount type with fields for credit limit, available credit, minimum payment due, payment due date, and rewards points.
2. A budgetCategory type that aggregates transactions by category (food, transportation, utilities, etc.) for a given month.
3. A query field spendingAnalysis that accepts a customer ID, start date, and end date, and returns spending by category with month-over-month change percentages.
4. A mutation for setting up automatic loan payments (autopay).
5. Pagination using cursor-based pagination (not offset-based) for transaction lists.

Write the complete schema additions and explain how each type maps to DB2 queries.

Exercise 35.5: Kafka Connect Configuration

Objective: Configure a Debezium connector for DB2 CDC.

1. Enable CDC on the following tables: sql ALTER TABLE MERIDIAN.ACCOUNTS DATA CAPTURE CHANGES; ALTER TABLE MERIDIAN.TRANSACTIONS DATA CAPTURE CHANGES; -- What other tables should have CDC enabled for Meridian Bank?

2. Write a complete Debezium connector configuration (JSON) that: - Captures changes from ACCOUNTS and TRANSACTIONS only - Routes events to topics named meridian.events.{table_name} - Excludes sensitive columns (SSN_HASH, SSN_LAST_FOUR) - Configures a dead-letter queue for failed events - Sets appropriate poll intervals for near-real-time processing

3. Design a Kafka consumer (pseudocode or Java) that: - Reads from meridian.events.TRANSACTIONS - Detects large transactions (> $10,000) - Publishes an alert to meridian.alerts.large-transactions - Implements idempotent processing using a processed-events table

Exercise 35.6: Event-Driven Architecture

Objective: Implement the saga pattern for a multi-step banking operation.

Design a saga for "Open Business Account" that involves these steps: 1. KYC (Know Your Customer) verification — external service call 2. Create customer record in DB2 3. Create business checking account with $10,000 minimum 4. Create linked savings account 5. Set up overdraft protection between the two accounts 6. Send welcome notification

For each step, define: - The action to perform - The compensating action (to undo if a later step fails) - The DB2 table updates involved - The Kafka events published

Write the saga state table DDL and the state transition logic as a stored procedure.

Exercise 35.7: API Security Implementation

Objective: Implement JWT-based authentication for a DB2-backed API.

1. Write a Spring Security filter that: - Extracts the JWT from the Authorization header - Validates the token signature and expiration - Extracts the customer ID claim - Sets the security context with the customer's roles

2. Create an authorization interceptor that ensures: - A customer can only access their own accounts - A teller can access any customer's accounts but cannot modify loan terms - An admin can perform all operations

3. Map JWT roles to DB2 procedure execution: describe how each role's API permissions translate to DB2 EXECUTE privileges.

4. Write a SQL injection test suite that attempts common injection patterns against each endpoint and verifies they are all rejected.

Exercise 35.8: Performance Optimization

Objective: Optimize an API endpoint for high throughput.

You have a /api/v1/accounts/{id}/balance endpoint that is called 10,000 times per minute. Currently it executes:

SELECT BALANCE FROM MERIDIAN.ACCOUNTS WHERE ACCOUNT_ID = ?

Implement three optimization strategies:

1. Response caching with a 5-second TTL using Spring Cache + Redis. Calculate the cache hit ratio if balances change on average every 30 seconds.

2. Connection pool tuning. Given 10,000 requests/minute and an average query execution time of 2ms, calculate the minimum pool size needed. What happens if the pool is too small?

3. Keyset pagination for the transaction history endpoint. Convert this offset-based query to keyset pagination: sql SELECT * FROM MERIDIAN.TRANSACTIONS WHERE ACCOUNT_ID = ? ORDER BY TRANSACTION_DATE DESC OFFSET 1000 ROWS FETCH NEXT 20 ROWS ONLY;

Exercise 35.9: z/OS Connect Service Design

Objective: Design a z/OS Connect API mapping for existing DB2 procedures.

Given the stored procedure PROC_CUSTOMER_DASHBOARD from Chapter 34 (which returns 3 result sets), design:

1. The service archive (.sar) configuration that maps the procedure's parameters.
2. The API archive (.aar) configuration that exposes it as GET /api/v1/customers/{id}/dashboard.
3. The request mapping (how the URL path parameter maps to the procedure's IN parameter).
4. The response mapping (how the three result sets are combined into a single JSON response).
5. Security configuration using SAF/RACF integration.

Discuss the challenge of mapping multiple result sets to a JSON response in z/OS Connect.

Exercise 35.10: Full Stack Integration

Objective: Build a complete API-to-database path for a new feature.

Design and implement the "Account Statement Download" feature:

1. DB2 layer: Create a stored procedure or table function that generates a monthly statement with opening balance, all transactions, fees, interest, and closing balance.

2. API layer: Create a REST endpoint GET /api/v1/accounts/{id}/statements/{year}/{month} that returns the statement as JSON. Add a query parameter ?format=pdf that triggers asynchronous PDF generation.

3. Async processing: For PDF generation, return a 202 Accepted with a status URL. Use a Kafka event to trigger the PDF generation worker. Store the result and return it when the client polls the status URL.

4. Caching: Cache generated statements (they are immutable once the month is complete). Use ETags for cache validation.

5. Security: Ensure a customer can only download their own statements. A teller can download any customer's statement but only for auditing purposes (logged).

Document the complete flow from HTTP request to DB2 query and back.