ODP Subscription Management

This guide covers operational management of ODP subscriptions and cursors from ERPL: how to inspect them, recover from stuck states, coordinate across teams, and keep the SAP side clean.

For SAP Administrators and Data Engineers

You'll get the most out of this guide if you've already worked through ODP Protocol Deep Dive. It covers the sap_odp_read_full vs. sap_odp_read_delta distinction that the rest of this page assumes.

The Two ODP Cursor Lifecycles

sap_odp_read_full cursors auto-close at end of scan — there is no lifecycle to manage. Everything below applies to delta cursors registered by sap_odp_read_delta.

Inspecting What's There

Listing Subscriptions

-- ERPL-owned subscriptions (default ERPL_ONLY => TRUE)
SELECT queue_name, subscriber_type, subscriber_name, subscriber_proc
FROM sap_odp_show_subscriptions();

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

sap_odp_show_subscriptions returns only queue_name, subscriber_type, subscriber_name, subscriber_proc. There is no status, created_date, or last_used_date — pair the result with sap_odp_show_cursors (below) for activity timestamps.

Per-Source Subscriber Visibility

sap_odp_get_subscriptions(odp_context, odp_name [, …]) exposes RODPS_REPL_ODP_GET_SUBSCR, which lists every subscription registered against a single source — handy when you want to know who else is reading from a CDS view or BW DataSource.

-- All subscribers on a source, with model and queue context
SELECT * FROM sap_odp_get_subscriptions('ABAP_CDS', 'SEPM_IBUPA$P');

-- Filter to my pipeline's subscriber
SELECT * FROM sap_odp_get_subscriptions(
    'BW', '0D_FC_C01$F',
    subscriber_name => 'ERPL'
);

Returns subscriber_type, subscriber_name, subscriber_process, model_name, queue_name, subscription_id (DECIMAL(23,9)).

Inspecting Cursors

-- All ERPL cursors with their current state
SELECT subscriber_proc, pointer, is_closed, is_delta_extension, request_date
FROM sap_odp_show_cursors(subscriber_name => 'ERPL');

-- Narrow to delta cursors on a specific context
SELECT * FROM sap_odp_show_cursors(
    subscriber_name  => 'ERPL',
    context          => 'ABAP_CDS',
    replication_mode => 'DELTA'
);

Returned columns: queue_name, subscriber_proc, subscriber_id, pointer, is_closed, is_delta_extension, request_date. request_date is the activity timestamp — use it for staleness checks.

Probing Source Change Time

sap_odp_get_last_modified returns when the source itself was last updated server-side — independent of any subscription:

-- Returns (odp_name VARCHAR, last_modified DECIMAL(21,7))
-- Format YYYYMMDDhhmmss.fffffff (UTC). Returns 0.0 for unknown ODP names.
SELECT * FROM sap_odp_get_last_modified('ABAP_CDS', 'SEPM_IBUPA$P');

Combine with run-log state to skip pipelines when nothing has changed.

Operational Patterns

Reconcile subscriptions to cursors

sap_odp_show_subscriptions lists what is registered; sap_odp_show_cursors lists what currently has activity. Joining them surfaces stale or orphaned state:

WITH subs AS (
  SELECT queue_name, subscriber_proc
  FROM sap_odp_show_subscriptions()
),
cursors AS (
  SELECT subscriber_proc, pointer, is_closed, is_delta_extension, request_date
  FROM sap_odp_show_cursors(subscriber_name => 'ERPL')
)
SELECT
  s.queue_name,
  s.subscriber_proc,
  c.is_delta_extension,
  c.is_closed,
  c.request_date,
  CURRENT_DATE - CAST(c.request_date AS DATE) AS days_idle,
  CASE
    WHEN c.subscriber_proc IS NULL                              THEN 'NO_CURSOR'
    WHEN c.is_closed                                            THEN 'CLOSED'
    WHEN CURRENT_DATE - CAST(c.request_date AS DATE) > 7        THEN 'STALE'
    ELSE                                                              'ACTIVE'
  END AS state
FROM subs s
LEFT JOIN cursors c USING (subscriber_proc)
ORDER BY days_idle DESC NULLS LAST;

Graceful close vs. hard reset

ERPL exposes two cleanup pragmas with different semantics:

Pragma	Effect	When to use
PRAGMA sap_odp_close_delta_cursor	Closes the cursor's pointer on SAP. Subscription remains registered and resumable. Idempotent — returns 'CLOSED' or 'NOT_FOUND'.	End of every healthy pipeline run. The default.
PRAGMA sap_odp_drop	Invokes RODPS_REPL_ODP_RESET — wipes the subscription entirely. Next sap_odp_read_delta call performs DELTAINIT.	Cursor is stuck and the close pragma can't reach it, or you intentionally want a fresh snapshot.

-- Standard end-of-pipeline cleanup
PRAGMA sap_odp_close_delta_cursor('BW', 'NIGHTLY_ETL', '0D_FC_C01$F');

-- Hard reset (rare)
PRAGMA sap_odp_drop('BW', 'ERPL', 'NIGHTLY_ETL', '0D_FC_C01$F');

Recovering a delta pipeline

-- 1. Look at the cursor — closed? open? when did it last move?
SELECT * FROM sap_odp_show_cursors(subscriber_name => 'ERPL')
WHERE subscriber_proc = 'NIGHTLY_ETL';

-- 2. Try replaying the last unconfirmed packet (no pointer advance)
SELECT * FROM sap_odp_read_delta(
    'BW', '0D_FC_C01$F', 'NIGHTLY_ETL',
    recover => true
);

-- 3. If still wedged, hard-reset and let the next call DELTAINIT
PRAGMA sap_odp_drop('BW', 'ERPL', 'NIGHTLY_ETL', '0D_FC_C01$F');
SELECT * FROM sap_odp_read_delta('BW', '0D_FC_C01$F', 'NIGHTLY_ETL');

Multiple subscribers on one source

Each subscriber_process is independent — different pipelines can register their own delta pointers against the same source without coordinating:

-- Three independent pipelines on VBAK$F
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'ANALYTICS_DAILY');
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'REPORTING_HOURLY');
SELECT * FROM sap_odp_read_delta('BW', 'VBAK$F', 'CDC_REALTIME');

-- See them all (ERPL-owned)
SELECT * FROM sap_odp_show_subscriptions()
WHERE queue_name = 'VBAK$F';

-- Or, including subscribers other tools created on the same source
SELECT * FROM sap_odp_get_subscriptions('BW', 'VBAK$F');

Pick descriptive, stable names per pipeline (<environment>_<purpose>_<cadence>); they appear verbatim in SAP's queue UI.

Cleanup Hygiene

Leaked open cursors accumulate server-side and consume queue slots. Two rules:

1. Always close a pipeline's cursor before exiting with PRAGMA sap_odp_close_delta_cursor. The pragma is idempotent — call it even if you aren't sure whether the cursor is open.
2. Run a periodic stale-cursor sweep against the reconciliation query above. Anything with state = 'STALE' and a days_idle past your retention policy is a candidate for PRAGMA sap_odp_drop.

-- Generate drop commands for ERPL cursors idle for more than 30 days
SELECT
  'PRAGMA sap_odp_drop(''' ||
    -- context: derive from queue_name or join sap_odp_show / sap_odp_get_subscriptions
    'BW' || ''', ''ERPL'', ''' ||
    subscriber_proc || ''', ''' ||
    queue_name || ''');' AS drop_sql
FROM sap_odp_show_cursors(subscriber_name => 'ERPL')
WHERE CURRENT_DATE - CAST(request_date AS DATE) > 30;

Inspect the generated SQL before executing — sap_odp_drop is destructive.

Troubleshooting

The next call returned a fresh full snapshot — I expected just deltas

The subscription was dropped (explicit sap_odp_drop, an admin reset on the SAP side, or the queue was purged). Auto-DELTAINIT runs whenever a subscriber_process is new.

sap_odp_close_delta_cursor returned 'NOT_FOUND'

No cursor of that name exists. Either it was already closed, the subscriber_process argument doesn't match what sap_odp_read_delta was called with, or the subscription was dropped. Confirm with sap_odp_show_cursors(subscriber_name => 'ERPL').

sap_odp_read_delta errors with "delta extraction not supported"

The source doesn't have a delta capability. Check sap_odp_describe(context, name) → supports_delta and delta_modes. CDS views need @Analytics.dataExtraction.delta.byElement; BW fact tables (*$F) are normally delta-capable.

Cursor is wedged — recover => true doesn't help

Hard-reset with PRAGMA sap_odp_drop; the next call re-DELTAINITs. If even drop fails, the queue may be locked at the SAP layer — escalate to Basis.

Next Steps

Ready for More?

• ODP Protocol Deep Dive — full context, examples, and the FULL vs. DELTA model
• ERPL Function Reference — every ODP function and pragma with signatures

Examples

• ERPL Examples — runnable ODP snippets
• Delta Replicate Stock Data from SAP ERP to Parquet — end-to-end pipeline walkthrough

---

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