ODP Protocol Deep Dive

This comprehensive guide covers the ODP (Operational Data Provisioning) protocol in ERPL. Learn how to extract and replicate SAP data with delta/full modes, manage subscriptions, and track changes.

What is ODP?

ODP (Operational Data Provisioning) is SAP's standard for data extraction and replication. It provides automatic subscription management, change tracking (Insert/Update/Delete), and supports multiple contexts including BW DataSources, SAPI, ABAP CDS, and SLT extractors.

ODP Architecture

Like BICS, ODP in ERPL runs entirely over the SAP NetWeaver RFC SDK — it calls the RODPS_REPL_* RFC function modules and reuses the RFC extension's sap_rfc secret. There is no separate ODP network endpoint to configure.

Understanding ODP Contexts

ODP supports multiple contexts for different data sources:

• BW - SAP BW DataSources and InfoProviders
• SAPI - Extraction APIs (SAP Application Interface)
• ABAP_CDS - CDS Views (Core Data Services)
• SLT - SAP Landscape Transformation
• HANA - SAP HANA views and tables

Basic Extraction

List Available Contexts

-- List all available ODP contexts
SELECT * FROM sap_odp_show_contexts();

-- Returns columns technical_name, text, release. Example rows:
-- technical_name | text                                  | release
-- ABAP_CDS       | ABAP Core Data Services               | SBC758
-- BW             | SAP NetWeaver Business Warehouse       | SBW758
-- HANA           | HANA Information Views                 |
-- SAPI           | DataSources/Extractors                | SDE758

List Data Sources

-- List data sources in BW context
SELECT * FROM sap_odp_show('BW');

-- List data sources in SAPI context
SELECT * FROM sap_odp_show('SAPI');

-- List data sources in ABAP_CDS context
SELECT * FROM sap_odp_show('ABAP_CDS');

Describe Data Source Structure

-- Describe BW DataSource structure
SELECT * FROM sap_odp_describe('BW', 'VBAK$F');

-- Describe SAPI extractor structure
SELECT * FROM sap_odp_describe('SAPI', '2LIS_11_VAHDR');

-- Describe CDS view structure
SELECT * FROM sap_odp_describe('ABAP_CDS', 'I_SALESORDER');

Basic Full Load

-- Full load from BW DataSource
SELECT * FROM sap_odp_read_full('BW', 'VBAK$F');

-- Full load from SAPI extractor
SELECT * FROM sap_odp_read_full('SAPI', '2LIS_11_VAHDR');

-- Full load from CDS view
SELECT * FROM sap_odp_read_full('ABAP_CDS', 'I_SALESORDER');

Delta Replication

Full vs. delta in ERPL

ERPL exposes two distinct extraction functions:

• sap_odp_read_full(context, data_source) is one-shot: it opens a FULL cursor on the SAP side, streams the snapshot, then auto-closes the cursor when the scan ends. No state survives.
• sap_odp_read_delta(context, data_source, subscriber_process) keeps a server-side delta pointer keyed by subscriber_process. The first call performs SAP's auto-DELTAINIT — it returns the full current snapshot AND registers the pointer. Subsequent calls with the same subscriber_process resume from the pointer and return only the changes since then.

Pick a stable subscriber_process per pipeline (e.g. 'NIGHTLY_ETL_BUPA'). It is the key that ties successive calls together. Close cursors with PRAGMA sap_odp_close_delta_cursor (graceful) or PRAGMA sap_odp_drop (hard reset). Inspect them with sap_odp_show_cursors().

Initial Delta Load (DELTAINIT)

-- First call: auto-DELTAINIT. Returns the full current snapshot AND registers
-- a server-side delta pointer under subscriber_process 'NIGHTLY_ETL'.
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL');

Subsequent Delta Loads

-- Every subsequent call with the same subscriber_process returns only the
-- changes since the previous call.
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL');

-- Categorize changes by ODQ_CHANGEMODE — ODP exposes the operation kind.
SELECT
  ODQ_CHANGEMODE,             -- C = Create/Insert, U = Update, D = Delete
  COUNT(*) AS record_count
FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL')
GROUP BY ODQ_CHANGEMODE;

Concurrency

Do not run two sap_odp_read_delta calls with the same subscriber_process in parallel — they will race the server-side pointer.

Probe before extract

Use sap_odp_get_last_modified to skip pipelines when nothing has changed since the last run — it returns a single timestamp without opening a cursor.

-- Last-modified probe (returns 0.0 for unknown ODP names)
SELECT * FROM sap_odp_get_last_modified('BW', 'VBAK$F');

-- Skip extract if untouched
WITH probe AS (
  SELECT last_modified FROM sap_odp_get_last_modified('BW', 'VBAK$F')
)
SELECT *
FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL')
WHERE (SELECT last_modified FROM probe) > 20260516000000.0;

Recovering an interrupted fetch

-- recover => true re-streams the last unconfirmed packet without advancing
-- the pointer. Use after a network blip or process crash mid-fetch.
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL', recover => true);

Releasing the cursor

-- Graceful: keep the subscription registered, just close its current pointer.
-- The next sap_odp_read_delta call with the same subscriber_process resumes.
PRAGMA sap_odp_close_delta_cursor('BW', 'NIGHTLY_ETL', 'VBAK$F');

-- Hard reset: wipe the subscription. The next call performs DELTAINIT again.
PRAGMA sap_odp_drop('BW', 'ERPL', 'NIGHTLY_ETL', 'VBAK$F');

Advanced Topics

Preview Data Without Subscription

-- Preview data without creating subscription
SELECT * FROM sap_odp_preview('BW', 'VBAK$F');

-- Preview with limit
SELECT * FROM sap_odp_preview('BW', 'VBAK$F', max_rows => 100);

Selection and Filtering

ERPL pushes most DuckDB SQL clauses down to RFC automatically:

-- Column projection: the SELECT list determines which columns RFC returns.
SELECT VBELN, ERDAT, KUNNR, NETWR
FROM sap_odp_read_full('BW', 'VBAK$F');

-- Tune parallelism and project columns explicitly.
SELECT * FROM sap_odp_read_full(
  'BW',
  'VBAK$F',
  threads => 4,
  columns => ['VBELN', 'ERDAT', 'KUNNR', 'NETWR']
);

For server-side row filtering, ODP uses a structured filters parameter built from the ODP_SELECT_SIGN / ODP_SELECT_OP enums. See the function reference for the exact shape.

Advanced: Subscription Management (For SAP Experts)

For SAP Administrators

This section covers advanced subscription management, cursor handling, and recovery strategies.

View Active Subscriptions

-- ERPL-owned subscriptions (default)
SELECT * FROM sap_odp_show_subscriptions();

-- All subscribers on the SAP system (cross-team visibility)
SELECT * FROM sap_odp_show_subscriptions(ERPL_ONLY => FALSE);

-- Cross-subscriber visibility on a single source (RODPS_REPL_ODP_GET_SUBSCR)
SELECT * FROM sap_odp_get_subscriptions('BW', 'VBAK$F');

sap_odp_show_subscriptions returns queue_name, subscriber_type, subscriber_name, subscriber_proc. sap_odp_get_subscriptions additionally exposes model_name and the numeric subscription_id.

Check Extraction Cursors

-- View extraction cursors
SELECT * FROM sap_odp_show_cursors();

-- Narrow to delta cursors created by a specific subscriber
SELECT * FROM sap_odp_show_cursors(
  subscriber_name => 'ERPL',
  replication_mode => 'DELTA'
);

Cursors created by sap_odp_read_delta appear here with is_delta_extension=true; the subscriber_proc column matches the subscriber_process argument you passed.

Closing a delta cursor

PRAGMA sap_odp_close_delta_cursor releases the cursor on the SAP side while keeping the subscription registered. The next sap_odp_read_delta call with the same subscriber_process resumes from the same pointer. Idempotent: returns 'CLOSED' or 'NOT_FOUND'.

PRAGMA sap_odp_close_delta_cursor('BW', 'NIGHTLY_ETL', 'VBAK$F');

Resetting a subscription

PRAGMA sap_odp_drop invokes RODPS_REPL_ODP_RESET to wipe the subscription entirely. The next sap_odp_read_delta call with the same subscriber tuple performs DELTAINIT again.

-- PRAGMA sap_odp_drop(odp_context, subscriber_name, subscriber_process, odp_name)
PRAGMA sap_odp_drop('BW', 'ERPL', 'NIGHTLY_ETL', 'VBAK$F');

Prefer sap_odp_close_delta_cursor for normal pipeline shutdown; reach for sap_odp_drop when a cursor is stuck or you intentionally want to restart from a fresh snapshot.

Subscription Lifecycle Example

-- 1. Probe — does this source have changes worth pulling?
SELECT last_modified FROM sap_odp_get_last_modified('BW', 'VBAK$F');

-- 2. First call: auto-DELTAINIT.  Registers a delta pointer under 'NIGHTLY_ETL'.
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL');

-- 3. Inspect the cursor that was created
SELECT * FROM sap_odp_show_cursors(subscriber_name => 'ERPL')
WHERE subscriber_proc = 'NIGHTLY_ETL';

-- 4. Subsequent runs: only changes since the previous call
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL');

-- 5. Pipeline finished — close the cursor gracefully
PRAGMA sap_odp_close_delta_cursor('BW', 'NIGHTLY_ETL', 'VBAK$F');

Real-World Examples

Daily Delta Replication Pipeline

-- Daily delta replication workflow
WITH delta_data AS (
  SELECT
    *,
    ODQ_CHANGEMODE,
    CASE ODQ_CHANGEMODE
      WHEN 'C' THEN 'INSERT'
      WHEN 'U' THEN 'UPDATE'
      WHEN 'D' THEN 'DELETE'
      ELSE 'UNKNOWN'
    END AS change_type
  FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL')
)
SELECT
  change_type,
  COUNT(*) AS record_count,
  MIN(ERDAT) AS earliest_date,
  MAX(ERDAT) AS latest_date
FROM delta_data
GROUP BY change_type
ORDER BY change_type;

Multi-Context Data Integration

-- Combine data from multiple ODP contexts
WITH bw_sales AS (
  SELECT 
    VBELN AS sales_doc,
    ERDAT AS doc_date,
    KUNNR AS customer,
    NETWR AS net_value,
    'BW' AS source_context
  FROM sap_odp_read_full('BW', 'VBAK$F')
),
cds_sales AS (
  SELECT 
    SalesOrder AS sales_doc,
    DocumentDate AS doc_date,
    SoldToParty AS customer,
    NetAmount AS net_value,
    'CDS' AS source_context
  FROM sap_odp_read_full('ABAP_CDS', 'I_SALESORDER')
)
SELECT 
  sales_doc,
  doc_date,
  customer,
  net_value,
  source_context
FROM bw_sales
UNION ALL
SELECT 
  sales_doc,
  doc_date,
  customer,
  net_value,
  source_context
FROM cds_sales
ORDER BY doc_date DESC, net_value DESC;

Change Data Capture Analysis

-- Analyze change patterns
WITH change_analysis AS (
  SELECT
    ODQ_CHANGEMODE,
    ERDAT AS change_date,
    COUNT(*) AS change_count,
    SUM(NETWR) AS total_value
  FROM sap_odp_read_delta('BW', 'VBAK$F', 'CDC_ANALYSIS')
  GROUP BY ODQ_CHANGEMODE, ERDAT
)
SELECT
  ODQ_CHANGEMODE,
  change_date,
  change_count,
  total_value,
  change_count * 100.0 / SUM(change_count) OVER() AS percentage_of_changes
FROM change_analysis
ORDER BY change_date DESC, change_count DESC;

Error Recovery Pattern

If a delta fetch was interrupted, try recover => true first to re-stream the last unconfirmed packet without advancing the pointer. If the cursor itself is wedged, fall back to PRAGMA sap_odp_drop to wipe the subscription so the next call performs DELTAINIT.

-- 1. Inspect what's there
SELECT * FROM sap_odp_show_cursors(subscriber_name => 'ERPL');

-- 2a. Replay the last packet (no pointer advance)
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL', recover => true);

-- 2b. Or hard-reset if the cursor is stuck
PRAGMA sap_odp_drop('BW', 'ERPL', 'NIGHTLY_ETL', 'VBAK$F');

-- 3. Re-run — DELTAINIT after a drop returns the full snapshot again
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL');

Performance Optimization

Batch Processing

-- Process multiple data sources in parallel
SELECT 'VBAK$F' AS data_source, COUNT(*) AS record_count
FROM sap_odp_read_full('BW', 'VBAK$F')

UNION ALL

SELECT 'VBAP$F' AS data_source, COUNT(*) AS record_count
FROM sap_odp_read_full('BW', 'VBAP$F')

UNION ALL

SELECT 'KNA1$F' AS data_source, COUNT(*) AS record_count
FROM sap_odp_read_full('BW', 'KNA1$F');

Selective Field Extraction

-- Extract only specific fields to reduce data volume
SELECT 
  VBELN,
  ERDAT,
  KUNNR,
  NETWR
FROM sap_odp_read_full(
  'BW', 
  'VBAK$F'
);

Connection Pooling

-- Reuse connections for multiple extractions
-- (Connection pooling is handled automatically by ERPL)
SELECT * FROM sap_odp_read_full('BW', 'VBAK$F');
SELECT * FROM sap_odp_read_full('BW', 'VBAP$F');
SELECT * FROM sap_odp_read_full('BW', 'KNA1$F');

Troubleshooting

Common Issues

Data Source Not Found

-- Check available data sources
SELECT * FROM sap_odp_show('BW');

-- Verify data source name (case-sensitive)
SELECT * FROM sap_odp_describe('BW', 'VBAK$F');

Subscription Errors

-- Check subscription status (ERPL-owned)
SELECT * FROM sap_odp_show_subscriptions();

-- Or look at *all* subscribers on a specific source
SELECT * FROM sap_odp_get_subscriptions('BW', 'VBAK$F');

-- Check cursor status
SELECT * FROM sap_odp_show_cursors();

-- Reset the cursor and re-DELTAINIT
PRAGMA sap_odp_drop('BW', 'ERPL', 'NIGHTLY_ETL', 'VBAK$F');
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL');

Permission Denied

-- Check ODP access permissions
SELECT * FROM sap_odp_show_contexts();

-- Verify user permissions in SAP
SELECT * FROM sap_odp_show('BW');

No Delta Data

-- Check if a subscription exists for this pipeline
SELECT * FROM sap_odp_show_subscriptions()
WHERE subscriber_proc = 'NIGHTLY_ETL';

-- Probe whether anything changed since the last call
SELECT last_modified FROM sap_odp_get_last_modified('BW', 'VBAK$F');

-- If no subscription, the next call auto-DELTAINITs
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'NIGHTLY_ETL');

Debugging Tips

-- Enable ERPL tracing to see the underlying RFC calls
SET erpl_trace_enabled = TRUE;
SET erpl_trace_level   = 'DEBUG';   -- TRACE | DEBUG | INFO | WARN | ERROR
SET erpl_trace_output  = 'console'; -- console | file | both

-- Preview data structure
SELECT * FROM sap_odp_preview('BW', 'VBAK$F', max_rows => 10);

-- Check subscription details
SELECT * FROM sap_odp_show_subscriptions();

-- Monitor extraction performance
EXPLAIN SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F'
);

For SAP Experts

The wire-level ODP mechanics for SAP Basis administrators and developers — the RODPS_REPL_* modules, the fetch loop, the BXML payload, and the delta-queue (ODQ) semantics behind the SQL functions. Expand the deep dive below.

ODP protocol deep dive — RODPS_REPL_* modules, fetch loop, BXML payload, delta/pointer mechanics, ODQ columns

Protocol details

ODP in ERPL is an RFC interface, not a separate web protocol. Everything runs over the SAP NetWeaver RFC SDK using your sap_rfc secret. The relevant RFC function modules are:

ERPL function	RFC function module(s)	Key parameters
sap_odp_show_contexts	RODPS_REPL_CONTEXT_GET_LIST	—
sap_odp_show	RODPS_REPL_ODP_GET_LIST	I_CONTEXT, I_SEARCH_PATTERN
sap_odp_describe / read_* (bind)	RODPS_REPL_ODP_GET_DETAIL	I_CONTEXT, I_ODPNAME → ET_FIELDS, E_SUPPORTS_FULL/DELTA
sap_odp_read_full / read_delta	RODPS_REPL_ODP_OPEN → RODPS_REPL_ODP_FETCH_XML → RODPS_REPL_ODP_CLOSE	I_EXTRACTION_MODE, E_POINTER
sap_odp_preview	RODPS_REPL_ODP_READ_DIRECT_XML	I_MAXIMUM_ROWS
sap_odp_get_last_modified	RODPS_REPL_ODP_GET_LAST_MODIF	IT_ODP → E_LAST_MODIFIED
sap_odp_get_subscriptions	RODPS_REPL_ODP_GET_SUBSCR	I_CONTEXT, I_ODPNAME
sap_odp_show_cursors	RODPS_REPL_CURSOR_GET_LIST	I_SUBSCRIBER_*, I_EXTRACTION_MODE
PRAGMA sap_odp_close_delta_cursor	RODPS_REPL_ODP_CLOSE	I_POINTER
PRAGMA sap_odp_drop	RODPS_REPL_ODP_RESET	subscriber tuple

Authentication is the RFC sap_rfc secret (user/password or SNC), and ODP authorizations (RODPS) govern which contexts and DataSources a user may read.

The OPEN → FETCH → CLOSE loop

Every extraction is an OPEN, a loop of FETCH_XML calls, and a CLOSE:

1. OPEN — RODPS_REPL_ODP_OPEN is called with I_EXTRACTION_MODE = 'F' (full), 'D' (delta), or 'R' (recover), the subscriber tuple, I_MAXPACKAGESIZE (≈2 MiB), and the selection/projection tables. It returns E_POINTER (the cursor handle / TSN) and E_DELTA_EXTENSION.
2. FETCH_XML — RODPS_REPL_ODP_FETCH_XML is called repeatedly with I_POINTER and a 6-digit I_PACKAGE counter (000001, 000002, …). Each call returns one data package as E_XML plus E_NO_MORE_DATA. The loop ends when E_NO_MORE_DATA = 'X'.
3. CLOSE — RODPS_REPL_ODP_CLOSE releases the cursor. Full reads close automatically when the scan ends; delta reads do not — you must call PRAGMA sap_odp_close_delta_cursor (or let SAP auto-close on E_NO_MORE_DATA).

ERPL can fetch packages with up to threads => N workers (default 5); each worker decodes its own package while the session serialises the shared cursor pointer and package counter.

The BXML payload

The _XML in FETCH_XML / READ_DIRECT_XML is not HTTP and not plain text. SAP returns a DEFLATE-compressed BXML (binary XML, format ODQ_G) stream inside the RFC response. ERPL inflates it (miniz) and parses the token stream client-side:

• The stream starts with the 4-byte magic BXML; a missing magic raises "Input has no valid BXML magic number" (the error you get when a source doesn't return a usable payload — e.g. some BW extractors under sap_odp_preview).
• Row records appear as repeated item elements; each field tag maps to a result column.
• SAP "initial" sentinels in date/time fields (00000000, 0000-00-00, 000000, 00:00:00, 0000-00-00 00:00:00) are surfaced as SQL NULL so DuckDB's date/time casts don't reject them. Fields whose name ends in uuid are decoded from binary to a UUID string.

Delta, subscriptions and the pointer

A subscription is identified by a subscriber tuple: I_SUBSCRIBER_TYPE (ERPL uses SAP_BW), I_SUBSCRIBER_NAME (ERPL), and I_SUBSCRIBER_PROCESS (the subscriber_process you pass). The server-side pointer is a DECIMAL(23,9) whose leading 14 digits are a UTC timestamp (YYYYMMDDhhmmss) — that is what sap_odp_show_cursors.request_date is parsed from.

• Auto-DELTAINIT — the first OPEN in mode 'D' for a new subscriber tuple has no stored pointer, so SAP returns the full snapshot and registers a pointer (E_DELTA_EXTENSION = 'X'). Subsequent 'D' opens with the same tuple resume from that pointer and return only changes.
• Recover (recover => true) — opens in mode 'R', which re-streams the last unconfirmed package without advancing the pointer. Use it after a crash/blip mid-fetch.
• Reset vs close — PRAGMA sap_odp_drop calls RODPS_REPL_ODP_RESET, which deletes the subscription and clears the pointer (next read re-DELTAINITs from scratch). PRAGMA sap_odp_close_delta_cursor calls RODPS_REPL_ODP_CLOSE, which releases the cursor but keeps the subscription so the next read resumes.

⚠️ One consumer per subscriber_process — two sap_odp_read_delta calls racing the same subscriber_process both OPEN the same server-side pointer; SAP's locking there is undefined and one may get a stale pointer or error. Give each parallel pipeline its own subscriber_process.

The ODQ change columns

Delta result sets carry SAP's operational delta-queue (ODQ) bookkeeping columns alongside your data fields:

Column	Type	Meaning
ODQ_CHANGEMODE	VARCHAR(1)	Operation: C = create/insert, U = update, D = delete (blank = unspecified)
ODQ_ENTITYCNTR	BIGINT	Entity counter — distinguishes before/after images within an update
ODQ_TSN	DECIMAL	Transaction sequence number of the delta package (matches the pointer batch)
ODQ_UNITNO	INTEGER	Logical unit number grouping related changes
ODQ_RECORDNO	INTEGER	Record number within the unit

A full read returns these too (with ODQ_CHANGEMODE = 'C' for the snapshot rows).

Selection pushdown

The structured filters parameter is pushed to SAP as the IT_SELECT table (RODPS_REPL_S_SELECTION rows: FIELDNAME, SIGN 'I'/'E', OPT 'EQ'/'BT'/…, LOW, HIGH) so the server restricts rows before they are packaged — the ODP equivalent of an ABAP SELECT-OPTIONS range. Column projection (columns => [...] or the SELECT list) becomes the projection table so unused fields are never serialised.

Cursor and subscription inspection

• sap_odp_show_cursors (RODPS_REPL_CURSOR_GET_LIST) returns queue_name, subscriber_proc, subscriber_id, pointer, is_closed, is_delta_extension, and the derived request_date. is_delta_extension is true for delta/recover cursors; is_closed is set once a cursor has drained or been closed.
• sap_odp_show_subscriptions reads the subscription registry; sap_odp_get_subscriptions (RODPS_REPL_ODP_GET_SUBSCR) lists all subscribers on one source, adding model_name and the numeric subscription_id.

Security

Because ODP rides on RFC, its security model is the RFC model:

1. Transport security — use SNC (or an SSH tunnel) to encrypt the RFC connection
2. Authentication — the sap_rfc secret: user/password or SNC certificates
3. Authorization — standard ODP/RODPS authorizations govern context and DataSource access
4. Audit logging — the SAP security audit log (SM19/SM20) records the RFC logons

Next Steps

🚀 Ready for More?

• RFC Protocol Guide - Read SAP tables and call functions
• BICS Protocol Guide - Execute SAP BW queries
• Function Reference - Complete API docs

🔧 Advanced Topics

• ODP Subscription Management - Complete subscription lifecycle guide
• Performance Tuning - Optimize ODP extractions
• Real-World Use Cases - Complete scenarios

💡 Examples

• ERPL Examples - More real-world ODP examples
• Integration with Python - Use ODP data with Pandas
• Real-World Use Cases - Complete scenarios

---

Need help? Check our troubleshooting guide or browse more examples.