Database Schema Documentation

Overview

The INSMASTR insurance management system utilizes a DB2 relational database named INSPROD with five primary tables that manage the complete lifecycle of insurance policies, customers, claims, and providers. The database is designed to support high-volume batch processing with a focus on data integrity, audit trails, and fraud detection.

Key Characteristics

• Database Platform: IBM DB2 for z/OS
• Database Name: INSPROD (Insurance Production)
• Isolation Level: CS (Cursor Stability)
• Lock Timeout: 30 seconds
• Transaction Model: Batch processing with periodic commits (default: 500 records)
• Total Tables: 5 (4 fully implemented, 1 audit table referenced)
• Sequences: 2 (POLICY_SEQ, CLAIM_SEQ)

Design Philosophy

The schema follows these principles:

1. Normalization: Customer information is separated from policy data to avoid redundancy
2. Audit Trail: Comprehensive tracking of creation and update timestamps
3. Referential Integrity: Foreign key relationships maintain data consistency
4. Self-Documenting: Clear naming conventions and status codes
5. Fraud Prevention: Scoring mechanisms integrated at the database level
6. Performance: Strategic denormalization (e.g., customer name in policy table) for reporting efficiency

Entity Relationship Diagram

Relationship Summary:

• One customer can have multiple policies (1:N)
• One policy can have multiple claims (1:N)
• One customer can file multiple claims (1:N)
• One provider can service multiple claims (1:N)
• Policies can renew to create new policies (self-referencing)

Database Configuration

Connection Parameters

Transaction Management

Commit Strategy:

• Commits occur after every 500 successfully processed records (configurable via WS-COMMIT-FREQUENCY)
• Final commit occurs at program termination
• Rollback on critical errors (handled by 9999-ABORT-PROGRAM)
• Retry logic: Maximum 3 retry attempts on transient failures

SQL Operations:

• INSERT: New policies, claims, and customer records
• UPDATE: Policy status changes, usage tracking
• MERGE: Customer records (INSERT or UPDATE based on existence)
• SELECT: Duplicate checking, policy/customer lookups

Table Schemas

POLICY_TABLE

Purpose: Central table storing all insurance policy records including new policies and renewals.

Primary Key: POLICY_NUMBER Row Estimate: High volume (millions of records) Access Pattern: Primarily INSERT for new policies, UPDATE for usage tracking, SELECT for lookups

Column Definitions

Indexes

Constraints

• Primary Key: POLICY_NUMBER must be unique
• Foreign Key: CUSTOMER_NUMBER references CUSTOMER_TABLE(CUSTOMER_NUMBER)
• Foreign Key: RENEWAL_FROM_POLICY references POLICY_TABLE(POLICY_NUMBER)
• Foreign Key: RENEWAL_TO_POLICY references POLICY_TABLE(POLICY_NUMBER)
• Check: COVERAGE_AMOUNT ≤ 999,999,999.99
• Check: POLICY_STATUS IN ('ACTIVE', 'RENEWED', 'EXPIRED', 'CANCELLED')
• Check: INSURANCE_TYPE IN ('HEALTH', 'LIFE', 'AUTO', 'PROPERTY', 'DENTAL', 'VISION')
• Check: RISK_SCORE BETWEEN 0 AND 100
• Check: FRAUD_SCORE BETWEEN 0 AND 100

Business Rules

1. Policy Number Generation: Automatically assigned from POLICY_SEQ sequence
2. Status Management:
   • New policies created with status 'ACTIVE'
   • Renewed policies change status to 'RENEWED' and populate RENEWAL_TO_POLICY
   • Policies expire when POLICY_END_DATE is reached (batch job updates status)
3. Usage Tracking: DEDUCTIBLE_MET and OUT_OF_POCKET_MET updated with each processed claim
4. Renewal Linking: When policy is renewed, old policy's RENEWAL_TO_POLICY points to new policy's POLICY_NUMBER
5. Audit Trail: CREATED_DATE and CREATED_BY are immutable; LAST_UPDATE_DATE and UPDATED_BY track modifications

CUSTOMER_TABLE

Purpose: Master table for customer demographic and contact information.

Primary Key: CUSTOMER_NUMBER Unique Key: SSN Row Estimate: Medium to high volume Access Pattern: MERGE operations (INSERT new, UPDATE existing), SELECT for validation

Column Definitions

Indexes

Constraints

• Primary Key: CUSTOMER_NUMBER must be unique
• Unique Key: SSN must be unique (cannot have duplicate SSNs)
• Check: GENDER IN ('M', 'F', 'O')
• Check: MARITAL_STATUS IN ('S', 'M', 'D', 'W') OR NULL
• Check: STATUS IN ('ACTIVE', 'INACTIVE', 'SUSPENDED')
• Check: RISK_PROFILE IN ('LOW', 'MEDIUM', 'HIGH')
• Check: FRAUD_ALERT IN ('Y', 'N')
• Check: SSN must be 9 digits

Business Rules

1. Customer Identification: SSN is the natural key but CUSTOMER_NUMBER is used as surrogate key
2. MERGE Operations: Program uses SQL MERGE to INSERT new customers or UPDATE existing ones
3. Address Format: Address is stored as a single concatenated string (Street1|Street2|City|State|ZIP|Country)
4. Risk Profile: Derived from policy risk scores; updated when policies are created
5. Fraud Alert: Set to 'Y' if any policy or claim has fraud score >= 70
6. Age Validation: Age calculated from DOB must be between 18-85 (enforced by application, not DB)
7. Email Validation: Must contain '@' symbol (enforced by application)

CLAIM_TABLE

Purpose: Records all insurance claim submissions with financial details and fraud indicators.

Primary Key: CLAIM_ID Unique Key: CLAIM_NUMBER Row Estimate: Very high volume Access Pattern: Primarily INSERT, SELECT for duplicate checking and fraud analysis

Column Definitions

Indexes

Constraints

• Primary Key: CLAIM_ID must be unique
• Unique Key: CLAIM_NUMBER must be unique (prevents duplicate submissions)
• Foreign Key: POLICY_NUMBER references POLICY_TABLE(POLICY_NUMBER)
• Foreign Key: CUSTOMER_NUMBER references CUSTOMER_TABLE(CUSTOMER_NUMBER)
• Foreign Key: PROVIDER_CODE references PROVIDER_TABLE(PROVIDER_CODE)
• Check: CLAIM_TYPE IN ('MEDICAL', 'HOSPITAL', 'EMERGENCY', 'DENTAL', 'VISION', 'AUTO', 'PROPERTY')
• Check: CLAIM_STATUS IN ('PROCESSING', 'UNDER REVIEW', 'APPROVED', 'DENIED')
• Check: APPROVAL_STATUS IN ('AUTO-APPROVED', 'PENDING APPROVAL', 'MANUAL REVIEW', 'FRAUD INVESTIGATION')
• Check: PAYMENT_STATUS IN ('APPROVED', 'PATIENT RESPONSIBILITY', 'DENIED')
• Check: FRAUD_FLAG IN ('Y', 'N')
• Check: INVESTIGATION_FLAG IN ('Y', 'N')
• Check: FRAUD_SCORE BETWEEN 0 AND 100
• Check: CLAIM_AMOUNT > 0
• Check: CLAIM_DATE >= INCIDENT_DATE (claim cannot be filed before incident)

Business Rules

1. Claim ID Generation: Automatically assigned from CLAIM_SEQ sequence
2. Duplicate Prevention: CLAIM_NUMBER must be unique to prevent duplicate submissions
3. Date Validation:
   • INCIDENT_DATE cannot be in the future
   • CLAIM_DATE must be >= INCIDENT_DATE
   • INCIDENT_DATE must fall within POLICY_START_DATE and POLICY_END_DATE
4. Fraud Detection Workflow:
   • FRAUD_SCORE >= 70: Set FRAUD_FLAG='Y', INVESTIGATION_FLAG='Y', APPROVAL_STATUS='FRAUD INVESTIGATION'
   • FRAUD_SCORE 50-69: Set INVESTIGATION_FLAG='Y', APPROVAL_STATUS='MANUAL REVIEW'
   • FRAUD_SCORE < 50 AND CLAIM_AMOUNT ≤ $5,000: APPROVAL_STATUS='AUTO-APPROVED'
   • FRAUD_SCORE < 50 AND CLAIM_AMOUNT > $5,000: APPROVAL_STATUS='PENDING APPROVAL'
5. Payment Calculation:
   • APPROVED_AMOUNT = CLAIM_AMOUNT (or adjusted by reviewer)
   • INSURANCE_PAID = APPROVED_AMOUNT - DEDUCTIBLE_APPLIED - COPAY_APPLIED - COINSURANCE_APPLIED
   • PATIENT_RESPONSIBILITY = DEDUCTIBLE_APPLIED + COPAY_APPLIED + COINSURANCE_APPLIED
6. Policy Update: After claim is processed, POLICY_TABLE is updated with new DEDUCTIBLE_MET, OUT_OF_POCKET_MET, CLAIMS_COUNT, and LAST_CLAIM_DATE

PROVIDER_TABLE

Purpose: Reference table for healthcare providers and service providers.

Primary Key: PROVIDER_CODE Row Estimate: Medium volume (thousands of records) Access Pattern: Primarily SELECT for validation and fraud scoring

Column Definitions

Indexes

Constraints

• Primary Key: PROVIDER_CODE must be unique
• Check: PROVIDER_TYPE IN ('HOSPITAL', 'CLINIC', 'DOCTOR', 'LAB')
• Check: NETWORK_FLAG IN ('Y', 'N')
• Check: ACTIVE_FLAG IN ('Y', 'N')
• Check: FRAUD_SCORE BETWEEN 0 AND 100
• Check: RATING BETWEEN 0.00 AND 9.99 OR NULL

Business Rules

1. Provider Validation: Claims must reference a valid PROVIDER_CODE
2. Fraud Impact:
   • Provider FRAUD_SCORE > 50 adds 20 points to claim fraud score
   • Provider ACTIVE_FLAG = 'N' adds 30 points to claim fraud score
3. Network Status: Affects claim payment calculations (out-of-network may have different copay/coinsurance)
4. Rating System: Quality rating maintained for provider selection and network management

AUDIT_TABLE

Purpose: Audit trail for all database changes (referenced but not fully implemented in current version).

Primary Key: AUDIT_ID Row Estimate: Very high volume Access Pattern: INSERT only (append-only log)

Column Definitions

Indexes

Constraints

• Primary Key: AUDIT_ID must be unique
• Check: OPERATION IN ('INSERT', 'UPDATE', 'DELETE')

Business Rules

1. Audit Triggers: Database triggers (if implemented) would automatically insert audit records on any DML operation
2. Retention: Audit records are never deleted (append-only for compliance)
3. Compliance: Supports SOX, HIPAA, and other regulatory requirements for audit trails

Note: The AUDIT_TABLE is referenced in the code but not fully implemented in the current version (3.0). It is designed for future implementation to provide comprehensive audit trail functionality.

Relationships and Foreign Keys

Primary Relationships

POLICY_TABLE Foreign Keys

CLAIM_TABLE Foreign Keys

Referential Integrity Rules

1. CASCADE Updates: Changes to primary keys (rare) cascade to dependent records
2. RESTRICT Deletes: Cannot delete parent records if child records exist
3. SET NULL: For optional relationships (e.g., RENEWAL_FROM_POLICY), deletion sets child to NULL
4. Application-Level Enforcement: COBOL program enforces additional business rules beyond DB constraints

Relationship Cardinality

Indexes

Indexing Strategy

The database uses a combination of primary keys, unique keys, and non-unique indexes to support:

• Fast lookups: Primary key access for individual records
• Duplicate prevention: Unique keys on SSN and CLAIM_NUMBER
• Query performance: Non-unique indexes on frequently filtered columns
• Fraud analysis: Indexes on fraud flags and scores

Complete Index Inventory

POLICY_TABLE Indexes

CUSTOMER_TABLE Indexes

CLAIM_TABLE Indexes

PROVIDER_TABLE Indexes

Index Maintenance

• Rebuild Schedule: Weekly during maintenance window
• Statistics Update: After each batch run (auto-update)
• Fragmentation Monitoring: Monthly checks, rebuild if fragmentation > 30%

Sequences

POLICY_SEQ

Purpose: Generates unique policy numbers

Configuration:

• Start Value: 100000000000000 (15-digit number starting at 100 trillion)
• Increment: 1
• Min Value: 100000000000000
• Max Value: 999999999999999
• Cycle: NO (error when max reached)
• Cache: 50 (pre-allocates 50 numbers for performance)
• Order: YES (guarantees sequential order)

Usage:

SELECT NEXT VALUE FOR POLICY_SEQ INTO :HV-POLICY-NUMBER

Capacity: 900 trillion unique policy numbers available

CLAIM_SEQ

Purpose: Generates unique claim IDs

Configuration:

• Start Value: 1
• Increment: 1
• Min Value: 1
• Max Value: 9999999999 (10-digit number)
• Cycle: NO (error when max reached)
• Cache: 100 (pre-allocates 100 numbers for performance)
• Order: YES (guarantees sequential order)

Usage:

SELECT NEXT VALUE FOR CLAIM_SEQ INTO :HV-CLAIM-ID

Capacity: 10 billion unique claim IDs available

Sequence Management

• Monitoring: Alert when sequence reaches 80% capacity
• Reset Policy: Sequences never reset (to maintain referential integrity)
• Failover: Sequences are cluster-aware for high availability

Business Rules and Constraints

Policy Business Rules

1. Policy Creation Rules:

   • Customer age must be 18-85 years
   • Coverage amount cannot exceed $999,999,999.99
   • Policy term must be valid for insurance type
   • SSN must be unique across all customers

2. Policy Renewal Rules:

   • Only ACTIVE policies can be renewed
   • Renewal creates new policy with new POLICY_NUMBER
   • Old policy status changes to RENEWED
   • RENEWAL_FROM_POLICY and RENEWAL_TO_POLICY establish link
   • Renewal term must be 12, 24, or 36 months
   • Loyalty discounts apply: 1% per year as customer (max 15%)
   • No-claims discounts apply: 2% per year without claims (max 20%)

3. Policy Status Transitions:

   • NEW → ACTIVE (upon creation)
   • ACTIVE → RENEWED (when renewed)
   • ACTIVE → EXPIRED (when term ends)
   • ACTIVE → CANCELLED (customer request)
   • ACTIVE → SUSPENDED (payment failure)

Customer Business Rules

1. Customer Validation:

   • Name is required (cannot be blank)
   • Age must be 18-85 (calculated from DOB)
   • SSN must be 9 digits and unique
   • Email must contain '@' symbol
   • One customer can have multiple policies

2. Risk Profile Classification:

   • LOW: Risk score < 30
   • MEDIUM: Risk score 30-69
   • HIGH: Risk score >= 70

3. Fraud Alert:

   • Set to 'Y' if any associated policy or claim has fraud score >= 70
   • Triggers manual review for all future transactions

Claim Business Rules

1. Claim Validation:

   • Policy must be ACTIVE at time of incident
   • INCIDENT_DATE must be between POLICY_START_DATE and POLICY_END_DATE
   • INCIDENT_DATE cannot be in the future
   • CLAIM_DATE must be >= INCIDENT_DATE
   • CLAIM_AMOUNT must be > 0
   • Duplicate CLAIM_NUMBER is rejected

2. Fraud Detection Scoring:

Fraud Decision Logic:

• Score >= 70: FRAUD_FLAG='Y', INVESTIGATION_FLAG='Y', Status='UNDER REVIEW'
• Score 50-69: INVESTIGATION_FLAG='Y', Status='PENDING', Manual review required
• Score < 50: Auto-process, auto-approve if amount ≤ $5,000

3. Payment Calculation:

Step 1: Apply Deductible

DEDUCTIBLE_REMAINING = DEDUCTIBLE_AMOUNT - DEDUCTIBLE_MET
DEDUCTIBLE_APPLIED = MIN(CLAIM_AMOUNT, DEDUCTIBLE_REMAINING)
REMAINING_AMOUNT = CLAIM_AMOUNT - DEDUCTIBLE_APPLIED

Step 2: Apply Copay/Coinsurance

COPAY_AMOUNT = REMAINING_AMOUNT * COPAY_PERCENTAGE
REMAINING_AMOUNT = REMAINING_AMOUNT - COPAY_AMOUNT

Step 3: Check Out-of-Pocket Maximum

OOP_REMAINING = OUT_OF_POCKET_MAX - OUT_OF_POCKET_MET
PATIENT_TOTAL = DEDUCTIBLE_APPLIED + COPAY_AMOUNT
If PATIENT_TOTAL > OOP_REMAINING:
    PATIENT_RESPONSIBILITY = OOP_REMAINING
    INSURANCE_PAID = CLAIM_AMOUNT - OOP_REMAINING
Else:
    PATIENT_RESPONSIBILITY = PATIENT_TOTAL
    INSURANCE_PAID = REMAINING_AMOUNT

4. Auto-Approval Rules:
   • Claims ≤ $5,000 with fraud score < 50 are auto-approved
   • Claims > $5,000 require manual approval regardless of fraud score
   • Claims with fraud score >= 50 always require review

Provider Business Rules

1. Provider Validation:

   • Claims must reference existing PROVIDER_CODE
   • Inactive providers (ACTIVE_FLAG='N') increase claim fraud score
   • Out-of-network providers may have different payment rules

2. Fraud Score Impact:

   • Provider with FRAUD_SCORE > 50 adds 20 points to claim fraud score
   • Provider history tracked across all claims for pattern detection

Data Types and Conventions

Naming Conventions

Table Names:

• All uppercase
• Descriptive business names
• Suffix _TABLE for all tables
• Examples: POLICY_TABLE, CUSTOMER_TABLE, CLAIM_TABLE

Column Names:

• All uppercase
• Underscore-separated words
• Descriptive of content
• Primary keys typically match table name (e.g., POLICY_NUMBER for POLICY_TABLE)
• Foreign keys include referenced table hint (e.g., CUSTOMER_NUMBER in POLICY_TABLE)

Index Names:

• Prefix indicates type: PK_ (primary key), UK_ (unique key), IX_ (index)
• Suffix indicates table: e.g., PK_POLICY, IX_CLAIM_FRAUD
• Composite indexes indicate columns: IX_POLICY_DATES

Sequence Names:

• Suffix _SEQ for sequences
• Match the table/purpose: POLICY_SEQ, CLAIM_SEQ

Data Type Standards

Numeric Types

Character Types

Date/Time Types

Note: Dates are stored as VARCHAR(10) in some columns for flexibility, but DATE type is recommended for new implementations.

Status Code Standards

Policy Status Codes

Claim Status Codes

Approval Status Codes

Payment Status Codes

Insurance Type Codes

Claim Type Codes

Provider Type Codes

Flag Conventions

All boolean flags use:

• Y = Yes/True/Active
• N = No/False/Inactive

Examples: FRAUD_FLAG, INVESTIGATION_FLAG, NETWORK_FLAG, ACTIVE_FLAG, FRAUD_ALERT

Null vs. Zero

Use NULL for:

• Optional fields that may not have a value (e.g., DENIAL_REASON)
• Fields that will be populated later (e.g., LAST_UPDATE_DATE on INSERT)
• Optional foreign keys (e.g., RENEWAL_FROM_POLICY for new policies)

Use ZERO for:

• Counters that start at zero (e.g., CLAIMS_COUNT)
• Accumulated amounts (e.g., DEDUCTIBLE_MET starts at 0.00)
• Scores that default to zero (e.g., RISK_SCORE, FRAUD_SCORE)

Access Patterns

Common Query Patterns

1. Duplicate Policy Check (Before INSERT)

SELECT COUNT(*)
  INTO :WS-DUPLICATE-COUNT
  FROM POLICY_TABLE
 WHERE CUSTOMER_NUMBER = :HV-CUSTOMER-NUMBER
   AND INSURANCE_TYPE = :HV-INSURANCE-TYPE
   AND POLICY_STATUS = 'ACTIVE'

Index Used: IX_POLICY_CUSTOMER, IX_POLICY_STATUS Frequency: Every new policy creation Performance: Sub-millisecond with proper indexes

2. Customer Lookup (For Validation)

SELECT CUSTOMER_NUMBER, CUSTOMER_NAME, RISK_PROFILE
  INTO :HV-CUST-NUMBER, :HV-CUST-NAME, :HV-CUST-RISK-PROFILE
  FROM CUSTOMER_TABLE
 WHERE SSN = :POL-SSN

Index Used: UK_CUSTOMER_SSN (unique index) Frequency: Every new policy creation Performance: Direct index lookup

3. Policy Retrieval (For Renewal)

SELECT *
  INTO :HV-POLICY-TABLE
  FROM POLICY_TABLE
 WHERE POLICY_NUMBER = :REN-POLICY-NUMBER
   AND POLICY_STATUS = 'ACTIVE'

Index Used: PK_POLICY (primary key) Frequency: Every renewal request Performance: Single-row fetch via PK

4. Duplicate Claim Check

SELECT COUNT(*)
  INTO :WS-DUPLICATE-COUNT
  FROM CLAIM_TABLE
 WHERE CLAIM_NUMBER = :CLM-CLAIM-NUMBER

Index Used: UK_CLAIM_NUMBER (unique index) Frequency: Every claim submission Performance: Direct index lookup

5. Claim Frequency Analysis (Fraud Detection)

SELECT COUNT(*)
  INTO :WS-FREQUENCY-COUNT
  FROM CLAIM_TABLE
 WHERE CUSTOMER_NUMBER = :HV-CLM-CUSTOMER-NUMBER
   AND CLAIM_DATE >= :WS-30-DAYS-AGO
   AND CLAIM_STATUS <> 'DENIED'

Index Used: IX_CLAIM_CUSTOMER, IX_CLAIM_DATES Frequency: Every claim for fraud scoring Performance: Index range scan

6. Pattern Detection (Similar Claims)

SELECT COUNT(*)
  INTO :WS-PATTERN-COUNT
  FROM CLAIM_TABLE
 WHERE CUSTOMER_NUMBER = :HV-CLM-CUSTOMER-NUMBER
   AND CLAIM_TYPE = :HV-CLAIM-TYPE
   AND CLAIM_AMOUNT = :HV-CLAIM-AMOUNT
   AND PROVIDER_CODE = :HV-PROVIDER-CODE
   AND CLAIM_DATE >= :WS-90-DAYS-AGO
   AND CLAIM_ID <> :HV-CLAIM-ID

Index Used: IX_CLAIM_CUSTOMER, IX_CLAIM_DATES Frequency: Every claim for fraud scoring Performance: Index range scan with filters

7. Provider Lookup (For Fraud Score)

SELECT PROVIDER_CODE, PROVIDER_NAME, PROVIDER_TYPE,
       FRAUD_SCORE, ACTIVE_FLAG
  INTO :HV-PROV-CODE, :HV-PROV-NAME, :HV-PROV-TYPE,
       :HV-PROV-FRAUD-SCORE, :HV-PROV-ACTIVE-FLAG
  FROM PROVIDER_TABLE
 WHERE PROVIDER_CODE = :CLM-PROVIDER-CODE

Index Used: PK_PROVIDER (primary key) Frequency: Every claim submission Performance: Single-row fetch via PK

8. Customer MERGE (Insert or Update)

MERGE INTO CUSTOMER_TABLE AS C
USING (VALUES (:HV-CUST-NUMBER, :HV-CUST-NAME, ...))
   AS S(CUSTOMER_NUMBER, CUSTOMER_NAME, ...)
   ON C.CUSTOMER_NUMBER = S.CUSTOMER_NUMBER
WHEN MATCHED THEN
  UPDATE SET C.CUSTOMER_NAME = S.CUSTOMER_NAME, ...
WHEN NOT MATCHED THEN
  INSERT (CUSTOMER_NUMBER, CUSTOMER_NAME, ...)
  VALUES (S.CUSTOMER_NUMBER, S.CUSTOMER_NAME, ...)

Index Used: PK_CUSTOMER (primary key for match) Frequency: Every new policy creation Performance: Single-row operation

9. Policy Usage Update (After Claim)

UPDATE POLICY_TABLE
   SET DEDUCTIBLE_MET = DEDUCTIBLE_MET + :WS-DEDUCTIBLE-APPLIED,
       OUT_OF_POCKET_MET = OUT_OF_POCKET_MET + :WS-PATIENT-PAYS,
       CLAIMS_COUNT = CLAIMS_COUNT + 1,
       LAST_CLAIM_DATE = :WS-CURRENT-DATE,
       LAST_UPDATE_DATE = CURRENT TIMESTAMP,
       UPDATED_BY = :WS-PROGRAM-NAME
 WHERE POLICY_NUMBER = :HV-POLICY-NUMBER

Index Used: PK_POLICY (primary key) Frequency: Every approved claim Performance: Single-row update via PK

10. Old Policy Status Update (After Renewal)

UPDATE POLICY_TABLE
   SET POLICY_STATUS = 'RENEWED',
       RENEWAL_COUNT = RENEWAL_COUNT + 1,
       RENEWAL_TO_POLICY = :HV-NEW-POLICY-NUMBER,
       LAST_UPDATE_DATE = CURRENT TIMESTAMP,
       UPDATED_BY = :WS-PROGRAM-NAME
 WHERE POLICY_NUMBER = :REN-POLICY-NUMBER

Index Used: PK_POLICY (primary key) Frequency: Every successful renewal Performance: Single-row update via PK

Batch Processing Patterns

Transaction Boundaries:

• Commits occur every 500 records (configurable via WS-COMMIT-FREQUENCY)
• Each transaction includes:
  • 1 input record read
  • 0-2 SELECT statements (validation/duplicate check)
  • 1-3 INSERT/UPDATE statements
  • 1 output record write

Example Transaction for Policy Creation:

1. Read policy input record
2. SELECT from CUSTOMER_TABLE (lookup by SSN)
3. SELECT from POLICY_TABLE (duplicate check)
4. MERGE into CUSTOMER_TABLE (create/update customer)
5. INSERT into POLICY_TABLE (new policy)
6. Write policy output record
7. Commit (if threshold reached)

Performance Characteristics:

• Policy Processing: ~1,000 records/minute
• Renewal Processing: ~1,500 records/minute
• Claims Processing: ~800 records/minute (includes fraud detection)

Locking and Concurrency

Isolation Level CS (Cursor Stability):

• Allows uncommitted reads for better concurrency
• Locks released as soon as cursor moves to next row
• Reduces lock contention in batch processing

Lock Timeout: 30 seconds

• Prevents indefinite waits
• Application retries up to 3 times on timeout

Lock Granularity:

• Row-level locks for UPDATE and INSERT
• Page locks may escalate to table locks under high contention
• Index locks acquired during INSERT operations

Performance Considerations

Index Maintenance:

• All indexes updated with each INSERT/UPDATE
• Index maintenance overhead balanced against query performance
• Recommend index rebuild during weekly maintenance window

Statistics:

• DB2 RUNSTATS executed after each batch run
• Ensures query optimizer has current statistics
• Critical for optimal access path selection

Buffer Pool Tuning:

• Large buffer pool recommended for CLAIM_TABLE (very high volume)
• Separate buffer pools for indexes vs. data
• Monitor buffer pool hit ratio (target > 95%)

Commit Frequency:

• 500 records balances transaction size and restart recovery
• Too frequent: Performance overhead
• Too infrequent: Long rollback time on error, lock contention