What is the RMS Batch?

During business hours, RMS processes individual transactions — a buyer creates a purchase order, a price analyst submits a price change, a store receives a shipment. But many critical operations cannot happen in real-time because they affect millions of rows or require complex calculations across the entire dataset.

These operations run as batch programs during the overnight batch window (typically 10 PM – 6 AM):

A typical large retailer has 200-400 batch programs running in sequence and parallel during the nightly window.

Pro*C: The Batch Language

Most RMS batch programs are written in Pro*C — a C programming language with embedded SQL. Oracle's Pro*C precompiler converts the embedded SQL into Oracle Call Interface (OCI) function calls, which are then compiled with a standard C compiler.

Why Pro*C?

• Performance: C programs execute 2-5x faster than equivalent PL/SQL for heavy data processing
• Memory Control: C gives direct control over memory allocation, buffer sizes, and data structures
• OS Integration: C programs can interact with the file system (read/write flat files), environment variables, and OS utilities
• Array Processing: Pro*C supports array fetch and array insert, processing thousands of rows per database round trip

Pro*C Compilation Flow

┌─────────────┐     ┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│  Source File │────▶│  Pro*C      │────▶│  C Compiler │────▶│  Executable │
│  (.pc)       │     │  Precompiler│     │  (gcc/cc)   │     │  (binary)   │
│              │     │  (proc)     │     │             │     │             │
│  C code +    │     │  Converts   │     │  Compiles   │     │  Ready to   │
│  embedded    │     │  SQL to     │     │  pure C     │     │  run        │
│  SQL         │     │  OCI calls  │     │  to binary  │     │             │
└─────────────┘     └─────────────┘     └─────────────┘     └─────────────┘

Pro*C vs. PL/SQL Batch

The Nightly Batch Flow

The nightly batch runs in a defined sequence, with some programs running serially and others in parallel:

10:00 PM ─── FOUNDATION BATCHES ──────────────────────────────
             │
             ├── vdate_job (advance business date)
             ├── currrate_load (load currency rates)
             └── fiscal_date_update (calendar updates)
             
11:00 PM ─── PRICING BATCHES ──────────────────────────────────
             │
             ├── price_change_execute (execute price changes)
             ├── clearance_execute (execute clearance markdowns)
             └── promotion_execute (activate promotions)
             
12:00 AM ─── SALES PROCESSING ──────────────────────────────────
             │
             ├── saposupld (post sales from ReSA to RMS)
             └── stock_ledger_post (update stock ledger)
             
 1:00 AM ─── INVENTORY BATCHES (PARALLEL) ──────────────────────
             │
             ├── Thread 1: wac_calc (recalculate WAC)
             ├── Thread 2: inv_adj_post (post adjustments)
             └── Thread 3: stock_count_post (post count results)
             
 2:00 AM ─── REPLENISHMENT (PARALLEL) ──────────────────────────
             │
             ├── Thread 1-8: replenish (calculate reorder qtys)
             └── auto_po_create (generate automatic POs)
             
 4:00 AM ─── DATA EXTRACTION ──────────────────────────────────
             │
             ├── fdl_item_extract (items for downstream)
             ├── fdl_itemloc_extract (item-loc for downstream)
             └── fdl_inv_extract (inventory for downstream)
             
 6:00 AM ─── BATCH COMPLETE ────────────────────────────────────

Threading & Parallel Execution

Many RMS batch programs support multi-threading — splitting the workload across multiple parallel processes:

How Threading Works

1. The scheduler launches the batch program with a thread count parameter (e.g., replenish -threads 8)
2. The program reads the RESTART_CONTROL table to determine its assigned data range
3. Each thread processes a non-overlapping subset of the data (typically partitioned by department, location, or item range)
4. Threads run independently — if Thread 3 fails, Threads 1, 2, 4-8 continue
5. After all threads complete, a final "consolidation" step merges results

Thread Assignment Example

For the replenishment batch with 8 threads across 80 departments:

RESTART_CONTROL Table

The RESTART_CONTROL table is a critical infrastructure table that manages batch program threading and restart capability:

-- Check status of tonight's batch programs
SELECT 
    program_name,
    thread_no,
    status,
    TO_CHAR(start_datetime, 'HH24:MI:SS') AS started,
    TO_CHAR(end_datetime, 'HH24:MI:SS')   AS ended,
    ROUND((end_datetime - start_datetime) * 24 * 60, 1) 
                                           AS duration_min,
    restart_bookmark
FROM 
    restart_control
WHERE 
    start_datetime > TRUNC(SYSDATE)
ORDER BY 
    start_datetime;

Error Handling & Recovery

When a batch program encounters an error:

1. The program writes the error to its log file (.log) and optionally to a bad file (.bad)
2. The program updates RESTART_CONTROL with status = 'F' (Failed) and sets the restart_bookmark
3. The scheduler detects the failure and holds all dependent downstream programs
4. Operations team investigates the log file to determine the root cause
5. After fixing the issue, operations restarts the program — it picks up from the restart_bookmark

Common Error Types

Batch Scheduling (POM & External)

On-Premise: External Schedulers

In on-premise deployments, batch programs are orchestrated by external schedulers:

• Control-M — the most common enterprise scheduler for Oracle Retail
• Autosys — popular in financial services
• cron — used in smaller deployments

Cloud: POM

In Oracle Retail Cloud, POM (Process Orchestration and Monitoring) is the mandatory scheduler. It provides a web-based interface for defining batch flows, dependencies, and monitoring execution.

Monitoring & Troubleshooting

Log File Analysis

Every batch program produces a log file. Key things to search for:

• "ORA-" — Oracle database errors
• "ERROR" — Application-level errors
• "ROWS PROCESSED" — Data volume indicators
• "ELAPSED TIME" — Performance benchmarks
• "RESTART" — Restart bookmark information

Batch Duration Trending

Track batch duration over time. A batch program that normally runs in 30 minutes but suddenly takes 2 hours indicates:

• Stale statistics on the tables it queries
• Data volume growth (more rows to process)
• Lock contention from a concurrent process
• Infrastructure issue (slow I/O, reduced CPU)