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

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();

-- Example output:
-- context_name | description
-- BW          | SAP BW DataSources
-- SAPI        | SAP Application Interface
-- ABAP_CDS    | ABAP CDS Views
-- SLT         | SAP Landscape Transformation

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

How replication mode is decided

Replication mode is not a per-call parameter. It's a property of the cursor that SAP maintains for your subscriber:

• The first call to sap_odp_read_full(context, data_source) for a given source opens a delta cursor and returns the FULL snapshot.
• Subsequent calls automatically return only the DELTA since the last cursor position.
• To force another FULL, reset the cursor with PRAGMA sap_odp_drop(...) first.

You can inspect the current cursor state with sap_odp_show_cursors() at any time.

Initial Full Load

-- First call to this (context, data_source) returns the full snapshot
-- and opens the delta cursor server-side.
SELECT * FROM sap_odp_read_full('BW', 'VBAK$F');

Subsequent Delta Loads

-- Every subsequent call returns only the changes since the last cursor position.
SELECT * FROM sap_odp_read_full('BW', 'VBAK$F');

-- Categorize changes by RECORD_MODE — ODP exposes the operation kind.
SELECT
  RECORD_MODE,                -- I = Insert, U = Update, D = Delete
  COUNT(*) AS record_count
FROM sap_odp_read_full('BW', 'VBAK$F')
GROUP BY RECORD_MODE;

Forcing a full re-extract

-- Drop the existing cursor so the next read returns a fresh FULL.
PRAGMA sap_odp_drop('BW', 'ERPL', 'erpl_subs_proc', 'VBAK$F');

-- Next call is FULL again.
SELECT * FROM sap_odp_read_full('BW', '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

-- List all active subscriptions
SELECT * FROM sap_odp_show_subscriptions();

-- Check subscription details
SELECT 
  subscriber_name,
  subscriber_type,
  data_source,
  context,
  status,
  created_date
FROM sap_odp_show_subscriptions();

Check Extraction Cursors

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

-- Check cursor status for specific data source
SELECT * FROM sap_odp_show_cursors(
  replication_mode => 'DELTA'
);

Reset a subscription

PRAGMA sap_odp_drop resets the cursor server-side. The next call to sap_odp_read_full for the same source re-creates the subscription and returns a fresh FULL.

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

Drop subscriptions individually with the pragma above; list active ones first with sap_odp_show_subscriptions().

Subscription Lifecycle Management

-- Complete subscription lifecycle example
-- 1. Check if subscription exists
SELECT * FROM sap_odp_show_subscriptions()
WHERE data_source = 'VBAK$F' AND subscriber_name = 'ERPL';

-- 2. If not exists, create with full load
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F'
);

-- 3. Subsequent delta loads
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F'
);

-- 4. Monitor subscription status
SELECT * FROM sap_odp_show_subscriptions()
WHERE subscriber_name = 'ERPL';

Real-World Examples

Daily Delta Replication Pipeline

-- Daily delta replication workflow
WITH delta_data AS (
  SELECT 
    *,
    RECORD_MODE,
    CASE RECORD_MODE
      WHEN 'I' THEN 'INSERT'
      WHEN 'U' THEN 'UPDATE'
      WHEN 'D' THEN 'DELETE'
      ELSE 'UNKNOWN'
    END AS change_type
  FROM sap_odp_read_full(
    'BW', 
    'VBAK$F'
  )
)
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 
    RECORD_MODE,
    ERDAT AS change_date,
    COUNT(*) AS change_count,
    SUM(NETWR) AS total_value
  FROM sap_odp_read_full(
    'BW', 
    'VBAK$F'
  )
  GROUP BY RECORD_MODE, ERDAT
)
SELECT 
  RECORD_MODE,
  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 subscription is stuck or the cursor is in an inconsistent state, reset it server-side with PRAGMA sap_odp_drop and let the next read recreate it.

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

-- 2. Reset the cursor for the affected data source
PRAGMA sap_odp_drop('BW', 'ERPL', 'erpl_subs_proc', 'VBAK$F');

-- 3. Re-read — this call is FULL again because the cursor was reset
SELECT * FROM sap_odp_read_full('BW', 'VBAK$F');

-- 4. Confirm the new cursor is in place
SELECT * FROM sap_odp_show_cursors() WHERE subscriber_name = 'ERPL';

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
SELECT * FROM sap_odp_show_subscriptions();

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

-- Reset the cursor and re-read (next read returns a fresh FULL)
PRAGMA sap_odp_drop('BW', 'ERPL', 'erpl_subs_proc', 'VBAK$F');
SELECT * FROM sap_odp_read_full('BW', 'VBAK$F');

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 subscription exists
SELECT * FROM sap_odp_show_subscriptions()
WHERE data_source = 'VBAK$F';

-- If no subscription, create with full load
SELECT * FROM sap_odp_read_full(
  'BW', 
  'VBAK$F'
);

Debugging Tips

-- Enable debug mode
SET debug_mode = true;

-- 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

Advanced ODP Internals

This section covers ODP protocol internals for SAP Basis administrators and developers.

ODP Protocol Details

ODP uses HTTP/HTTPS with XML payloads for communication:

• Port: Usually 8080 or 8443 (HTTPS)
• Protocol: HTTP/HTTPS with XML
• Authentication: Basic Auth or SAML
• Compression: Optional gzip compression
• Unicode: Full Unicode support

Subscription Management

1. Automatic creation: Subscriptions and cursors are created on the first call to sap_odp_read_full
2. Cursor tracking: ODP maintains cursors server-side; subsequent reads return only the delta since the last cursor position
3. Recovery: Reset cursors with PRAGMA sap_odp_drop(...) — the next read returns a fresh FULL
4. Cleanup: Use PRAGMA sap_odp_drop per subscription; there is no drop_all pragma

Performance Considerations

1. Parallelism: Tune threads => N on sap_odp_read_full for large extractions
2. Column projection: Use columns => [...] or rely on SELECT-list pushdown to reduce payload
3. Batch processing: Run multiple data sources in parallel queries
4. Connection reuse: ERPL handles connection pooling automatically

Security Best Practices

1. Network security: Use HTTPS for all ODP connections
2. Authentication: Use strong passwords or certificates
3. Authorization: Implement proper ODP user roles
4. Audit logging: Enable ODP audit logs in SAP

Change Data Capture Patterns

1. Initial load: First call to a (context, data_source) pair is FULL — automatic
2. Delta processing: Subsequent calls return only changes — automatic
3. Error handling: Reset the cursor with PRAGMA sap_odp_drop to recover
4. Monitoring: Use sap_odp_show_cursors to inspect current mode and position

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.