Chapter 10 Exercises

Chapter 10 Exercises

Section 10.1: When Logic Belongs in the Database

Exercise 1: Logic Placement Analysis

You maintain a COBOL batch program that calculates shipping costs for an e-commerce fulfillment system. The calculation reads from three tables (product_weight, shipping_zones, carrier_rates), applies twelve business rules, and writes the result to the order_detail table. The same calculation also exists in a CICS transaction, a web service, and an IMS MPP. Each implementation was written by a different developer over a period of six years.

a) Using the five-dimension scoring matrix from Section 10.7, score this logic for stored procedure candidacy. Show your score for each dimension with justification.

b) Identify three specific risks of keeping this logic in four separate programs.

c) Identify two risks of moving this logic to a stored procedure.

Exercise 2: The False Centralization Trap

A junior developer proposes moving the following logic into a stored procedure: a CICS BMS map formatting routine that reads customer data from DB2 and formats it for display on a 3270 screen, including cursor positioning and attribute bytes. Explain why this is a poor candidate for a stored procedure, referencing the "Red Lines" from Section 10.7.

Exercise 3: Evaluating Trade-offs

Federal Benefits Administration runs a nightly batch job that reads 14 million rows from the BENEFICIARY table and calculates eligibility for each beneficiary. The calculation involves reading from 7 additional tables. Currently, the batch program makes 14 million sets of SQL calls (approximately 100 million individual SQL roundtrips). Sandra proposes replacing this with a stored procedure called once per beneficiary.

a) Estimate the network round-trip savings if the stored procedure replaces 7 SQL calls per beneficiary with 1 CALL per beneficiary.

b) Sandra's colleague Marcus suggests an alternative: instead of a stored procedure, rewrite the batch to use a single large SQL join that fetches all needed data in one pass, then apply the business rules in COBOL. Compare this approach to the stored procedure approach on three dimensions: maintainability, performance, and code reuse.

c) Which approach would you recommend? Justify your answer.

Exercise 4: Access Path Inventory

Before making any stored procedure decisions, you need to inventory all access paths for a given business rule. For the "account balance check" rule at your bank, list all possible access paths (programs, transactions, services) that might execute this rule. Consider: CICS online, batch reporting, web services, mobile API, IVR (Interactive Voice Response), ATM interface, and internal audit.

Section 10.2: COBOL External Stored Procedures

Exercise 5: CREATE PROCEDURE DDL Analysis

Given the following CREATE PROCEDURE statement, identify three errors or suboptimal choices and explain the impact of each:

CREATE PROCEDURE BANK.UPDATE_BALANCE
    (IN  P_ACCOUNT_NUM     CHAR(12),
     IN  P_AMOUNT           DECIMAL(15,2),
     IN  P_TRANSACTION_TYPE CHAR(1),
     OUT P_NEW_BALANCE      DECIMAL(15,2),
     OUT P_STATUS           INTEGER)
    LANGUAGE COBOL
    EXTERNAL NAME UPDATE_BALANCE_PROC
    PARAMETER STYLE SQL
    DETERMINISTIC
    READS SQL DATA
    WLM ENVIRONMENT WLMENV1
    COMMIT ON RETURN YES;

Exercise 6: WLM Address Space Configuration

You are deploying a new stored procedure that will be called by 200 concurrent CICS users during peak hours. Each call takes approximately 50ms to execute.

a) Calculate the minimum number of TCBs needed to avoid queuing at peak (200 concurrent users, 50ms per call, assuming uniform arrival).

b) Explain why you would configure more TCBs than the calculated minimum.

c) What happens if all TCBs are busy and a new CALL arrives?

d) What is the impact of setting NUMTCB too high?

Exercise 7: STEPLIB Configuration

A stored procedure works correctly in the test environment but fails in production with an S806 ABEND (module not found). The procedure calls two subroutines: DATEUTIL (a date conversion utility in a shared utility library) and ACCTCHK (an account validation module in the application library). Write the STEPLIB DD concatenation for the WLM address space JCL procedure, in the correct order, explaining why order matters.

Exercise 8: Deployment Sequence

Put the following deployment steps in the correct order. Explain what happens if you perform step E before step D.

A. BIND PACKAGE for the stored procedure DBRM B. Compile and link-edit the COBOL program C. DB2 precompile the COBOL source D. Copy the load module to the WLM address space STEPLIB library E. REFRESH the WLM application environment F. CREATE PROCEDURE (or ALTER PROCEDURE) DDL G. Run regression tests

Exercise 9: Stored Procedure Error Handling

Write the complete error handling paragraph for a stored procedure that updates an account balance. The paragraph should handle: - SQLCODE 0 (success) - SQLCODE +100 (account not found) - SQLCODE -803 (duplicate key — should not happen but guard against it) - SQLCODE -911 (timeout/deadlock — set a retry indicator) - Any other negative SQLCODE (unexpected error)

For each case, set appropriate values in the OUT parameters LS-STATUS-CODE (integer) and LS-STATUS-MSG (PIC X(200)).

Exercise 10: GOBACK vs. STOP RUN

Explain why a COBOL external stored procedure must use GOBACK instead of STOP RUN. What happens to the WLM address space if a stored procedure executes STOP RUN?

Section 10.3: Parameter Passing and Result Sets

Exercise 11: Parameter Mode Selection

For each of the following parameters in a "process payment" stored procedure, choose the appropriate mode (IN, OUT, or INOUT) and justify your choice:

a) Payment amount b) Account number c) Payment status (caller passes initial status; procedure may upgrade it) d) Error message e) Transaction ID (generated by the procedure) f) Payment date (caller provides requested date; procedure returns actual processing date)

Exercise 12: PARAMETER STYLE GENERAL WITH NULLS

Rewrite the LINKAGE SECTION and PROCEDURE DIVISION USING clause for the account validation stored procedure from Section 10.2, using PARAMETER STYLE GENERAL WITH NULLS. The account number parameter should be nullable (if NULL, the procedure should return status code 12 with message "NULL ACCOUNT NUMBER").

Exercise 13: Result Set Implementation

Write a stored procedure that returns two result sets:

Result Set 1: Account summary (account number, account type, current balance, status) for a given customer ID.

Result Set 2: The 10 most recent transactions across all of that customer's accounts.

Provide both the COBOL code (WORKING-STORAGE cursor declarations and PROCEDURE DIVISION to open them) and the CREATE PROCEDURE DDL.

Exercise 14: Caller-Side Result Set Processing

Write the COBOL code for a calling program that: 1. Calls the two-result-set procedure from Exercise 13. 2. Uses ASSOCIATE LOCATORS and ALLOCATE CURSOR to receive both result sets. 3. Processes the account summary result set, printing each account. 4. Processes the transaction result set, accumulating a total. 5. Properly closes both allocated cursors.

Exercise 15: Result Set Size Concerns

A developer writes a stored procedure that opens a cursor on a 50-million-row table without any WHERE clause and returns it as a result set. The calling CICS transaction fetches rows one at a time.

a) Will this cause the entire 50-million-row result set to be materialized in memory? Why or why not?

b) What locking implications does this have (reference Chapter 8)?

c) Propose a better design for this scenario.

Section 10.4: User-Defined Functions in COBOL

Exercise 16: Scalar UDF Design

Design a scalar UDF called CALC_LATE_FEE that takes three inputs: the payment due date (DATE), the actual payment date (DATE), and the outstanding balance (DECIMAL(13,2)). It returns the late fee (DECIMAL(11,2)) based on these rules:

0 days late: $0.00
1-15 days late: 2% of outstanding balance, minimum $25.00
16-30 days late: 5% of outstanding balance, minimum $50.00
31+ days late: 10% of outstanding balance, minimum $100.00, maximum $500.00

Write the CREATE FUNCTION DDL and the COBOL PROCEDURE DIVISION logic (you may omit IDENTIFICATION/ENVIRONMENT divisions).

Exercise 17: UDF Null Handling

In the CALC_LATE_FEE UDF from Exercise 16, if the actual payment date is NULL (payment hasn't been received yet), the function should calculate the fee based on CURRENT DATE. Modify the COBOL code to handle this using the null indicator for the payment date parameter.

Exercise 18: Table Function Design

Design a table function called GET_ACCOUNT_HISTORY that takes an account number and a date range, and returns a table of monthly summaries: (MONTH_YEAR CHAR(7), OPENING_BALANCE DECIMAL(13,2), TOTAL_CREDITS DECIMAL(13,2), TOTAL_DEBITS DECIMAL(13,2), CLOSING_BALANCE DECIMAL(13,2), TRANSACTION_COUNT INTEGER).

Write the CREATE FUNCTION DDL. Then write the COBOL logic for the OPEN, FETCH, and CLOSE call types, using the scratchpad to track the current month being processed.

Exercise 19: UDF vs. Stored Procedure

Diane at Pinnacle Health needs to implement a claims adjudication calculation. The calculation: - Takes a claim ID as input - Reads from 4 tables - Returns 6 values: allowed amount, copay, deductible applied, coinsurance, plan payment, and patient responsibility

Should this be implemented as a scalar UDF, a table function, or a stored procedure? Justify your answer, considering how each approach would be called from SQL.

Exercise 20: UDF Performance Trap

Explain why the following query is likely to cause severe performance problems, even though the UDF itself is efficient (executes in 0.5ms per call):

SELECT C.*
FROM CLAIMS C
WHERE C.CLAIM_STATUS = 'PENDING'
  AND PINNACLE.CALC_ALLOWED_AMT(C.PROC_CODE,
                                 C.PROVIDER_TIER,
                                 C.PLAN_TYPE) > 10000.00
ORDER BY CLAIM_DATE;

Assume the CLAIMS table has 80 million rows and 2 million are in 'PENDING' status. Propose two alternative approaches that avoid the performance problem.

Section 10.5: Performance Implications

Exercise 21: Round-Trip Analysis

A stored procedure replaces application logic that made 8 SQL calls per invocation. The procedure is called 500,000 times per day by a batch program. Each DB2 cross-memory round-trip costs 0.15ms.

a) Calculate the total time saved per day in network round-trips.

b) If the stored procedure itself adds 0.03ms of WLM scheduling overhead per call, what is the net time savings?

c) At what invocation volume does the WLM scheduling overhead outweigh the round-trip savings (i.e., what is the break-even number of SQL calls replaced)?

Exercise 22: CPU Accounting Scenario

SecureFirst runs a stored procedure that performs fraud scoring. The procedure consumes 0.5 CPU-ms per call. It is called by three departments:

Online Banking: 100,000 calls/day
Card Processing: 250,000 calls/day
Risk Management: 50,000 calls/day

a) Calculate the total daily CPU consumption in seconds attributed to the stored procedure.

b) The Online Banking team argues they shouldn't pay for the stored procedure CPU because "it's a shared service." The Card Processing team argues the stored procedure saves them money because it replaced application logic that consumed 0.8 CPU-ms per call. Design a fair CPU chargeback model.

Exercise 23: WLM Tuning Scenario

You monitor a stored procedure and observe the following over a one-hour peak period:

Total CALL requests: 15,000
Average service time per CALL: 20ms
95th percentile queue wait time: 150ms
Current NUMTCB setting: 10
WLM address space CPU utilization: 85%

a) Is the NUMTCB setting sufficient? Calculate the theoretical throughput with 10 TCBs and 20ms service time.

b) What NUMTCB setting would you recommend?

c) What other WLM adjustments should you investigate?

Section 10.6: Stored Procedure Governance

Exercise 24: Versioning Scenario

You need to add a new OUT parameter to an existing stored procedure that is called by 12 different programs across 4 application teams. The new parameter returns a risk score that only 2 of the 12 callers need.

a) Design a versioning strategy that doesn't break the existing 12 callers.

b) How would you handle the transition so that callers can migrate incrementally?

c) After all callers have migrated, what cleanup steps are needed?

Exercise 25: Testing Strategy

Write a test specification for the VALIDATE_ACCOUNT stored procedure from Section 10.2. Your specification should include:

a) At least 8 test cases covering normal and error paths.

b) The test data setup (INSERT statements) needed for each test case.

c) The expected output (parameter values) for each test case.

d) The test harness CALL statement and validation logic for one test case (written in COBOL).

Exercise 26: Security Model Design

Federal Benefits has three user roles: CASE_WORKER, SUPERVISOR, and AUDITOR. Design the security model for the following stored procedures:

CALC_ELIGIBILITY: Determines benefit eligibility. Case workers and supervisors can call it.
OVERRIDE_ELIGIBILITY: Overrides the calculated eligibility. Only supervisors.
AUDIT_ELIGIBILITY_LOG: Returns the audit trail. Only auditors.

For each procedure, specify the GRANT statements and explain why direct table access should not be granted to any role.

Exercise 27: Deployment Failure Analysis

A production deployment of a stored procedure update fails. The new version was deployed at 10:00 PM. At 10:15 PM, callers start receiving SQLCODE -471 (the procedure is not available). The DBA discovers that the WLM address space was recycled but the new load module was deployed to the wrong STEPLIB library.

a) What is the immediate remediation?

b) What process changes would prevent this in the future?

c) Design a deployment checklist with verification steps.

Section 10.7: Decision Framework

Exercise 28: Scoring Exercise

Score the following five business rules using the matrix from Section 10.7. For each rule, provide the score for each dimension and your recommendation.

a) Currency conversion rate lookup: Called by 8 programs, reads 1 table, rates change daily, minimal computation.

b) Mortgage payment calculation: Called by 3 programs, reads 4 tables, complex amortization math, changes with regulation updates (annually).

c) Customer name/address formatting: Called by 15 programs, reads 2 tables, simple string manipulation, changes rarely.

d) Fraud detection scoring model: Called by 1 program, reads 12 tables, heavy statistical computation, model updated quarterly.

e) End-of-day balance reconciliation: Called by 2 programs, reads 20 tables, moderate computation, logic is stable.

Exercise 29: Architecture Review

You are reviewing a proposal to move all 200 business rules in a banking application into stored procedures. The architect argues that "centralization is always better." Write a one-page response identifying:

a) Three categories of rules that are good stored procedure candidates.

b) Three categories of rules that should remain in application COBOL.

c) The operational risks of having 200 stored procedures in a single WLM environment.

d) A phased migration strategy.

Exercise 30: HA Banking System Design

For the HA Banking Transaction Processing System progressive project, design the complete stored procedure layer:

a) List all stored procedures and UDFs needed, with their names, parameter lists, and return types.

b) Design the WLM environment topology: how many application environments, which procedures go in each, and why.

c) Write the CREATE PROCEDURE DDL for the fund transfer procedure.

d) Write the CREATE FUNCTION DDL for the monthly fee calculation UDF.

e) Design the regression test suite: list 15 test cases covering the fund transfer procedure.

f) Design the deployment pipeline including all promotion stages and verification steps.